Distinct values

**kushibobby** · 02-23-2012, 07:20 AM

Am trying to list distinct values in a column from a column of another sheet.
ColA Col B
A 3
B 4
B 4
C 5
A 3
C 5
B 4

Always A, b and C are having same values for Col B

am trying to get the below output:

A 3
B 4
C 5

I used If and COuntif, but not possible. Can somebody help me

**NBVC** · 02-23-2012, 10:50 AM

Did you try using Advanced Filter for Unique Values?

Go to Sheet2, select Data|Filter|Advanced filter, select Copy to another Location and check Unique Records only... select the List range from Sheet1, then select the cell you want to copy to in Sheet2 in the Copy To field.

**kushibobby** · 02-24-2012, 02:12 AM

Hi NBVC,

THanks for your reply.

But am trying to have a formula.can u pls help

Regards

**NBVC** · 02-24-2012, 09:15 AM

Assuming your data is in Sheet1, A2:B8 (not including headers)

Then in Sheet2, A2 enter:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Sheet1!A$2:A$8,MATCH(0,INDEX(COUNTIF($A$1:$A1,Sheet1!$A$2:$A$8),0),0)),""))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down as far as needed.

In B2 enter:

=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$B$8,2,0))

copied down same distance.