i have excel table named table5 consist of 2 columns companycode , employeelevel. question want unique values of employeelevel columns. take note length of record unknown. using table5[employeelevel] range include records in table5. there formula can provide ? cannot use activex object loop through each record. must via excel formula. solutions got usable when fixed how long records are. record working on dynamic table.
thanks in advance :)
assuming data looks this:
the easiest way create pivot table based on table5. in rows, add employeelevel. in data, add employeelevel.
change data aggregation count rather sum. this.
when add new item table5, need update pivottable (right-click on pivottable , click update).
if need use formulas, following workaround.
in column c, add following formula (given below data, adjust fit):
=--(countif($b$1:$b2,$b2)=1) this return 1 every unique value in table.
somewhere on sheet (in case f2), added following formula count unique values:
=sum(table5[unique value]) in sample data, there 14.
i copied following formula f3 down 20 rows (i used 20 because don't have many unique values -- table variable, pick number of rows higher amount of unique values expect in future):
{=if(row(a1)<=f$2,small(if(table5[unique value]=1,table5[employeelevel]),row(a1)),"")} note -- curly braces array formula -- not add them hand, enter formula without them , confirm ctrl+shift+enter
this result in unique values in ascending order:
