Excel Data Validation Get Unique Values of Record from a Named Table

Question

I have an Excel Table named Table5 which consist of two columns CompanyCode and EmployeeLevel. Now my question is i want to get the unique values of EmployeeLevel columns. Take note that the length of the record is unknown. I am using the Table5[EmployeeLevel] as the range to include all the records in the Table5. Is there a formula that you can provide ? I cannot use ActiveX object to loop through each record. I must do it via Excel formula. The solutions I got are only usable when it is fixed how long the records are. but this record I am working on is a dynamic table.

Thanks in advance :)

jmac jmac · Accepted Answer · 2013-08-23T04:15:24

Assuming your data looks like this:

Table5

The easiest way to do this is to create a Pivot Table based on Table5. In the Rows, add EmployeeLevel. In Data, add EmployeeLevel.

enter image description here

Change the data aggregation to Count rather than sum. You will get something like this.

enter image description here

When you add a new item to Table5, all you need to do is update the PivotTable (right-click on the PivotTable and click update).

If you need to use formulas, the following is a workaround.

In Column C, add the following formula (given the below data, adjust to fit):

=--(COUNTIF($B$1:$B2,$B2)=1)

This will return a 1 for every unique value in the table.

Somewhere on the sheet (in my case F2), I added the following formula to count unique values:

=SUM(Table5[Unique Value])

In my sample data, there are 14.

I then copied the following formula from F3 down 20 rows (I used 20 because I don't have that many unique values -- you say your table is variable, so pick a number of rows higher than the amount of unique values you expect in the future):

{=IF(ROW(A1)<=F$2,SMALL(IF(Table5[Unique Value]=1,Table5[EmployeeLevel]),ROW(A1)),"")}

_{Note -- the curly braces are an array formula -- do not add them by hand, enter the formula without them and confirm with ctrl+shift+enter}

This will result in the unique values in ascending order:

enter image description here

Excel Data Validation Get Unique Values of Record from a Named Table

2 Answers