MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to extract unique entries from a particular column in a range

  Asked By: Adalicia    Date: Aug 28    Category: MS Office    Views: 2012

1) Suppose I have a range of a1:b20
2) Column A contains Names of the Items (A1 can be labelled as Item Name)
3) Column B contains Quantity (B1 can be labelled as Quantity)
4)Range A2:A20 contains 19 item name entries. But there only (say) 8 unique
5) Range B2:B20 contains corresponding quantities
6) If I use autofilter, from the dropdown list I can select any unique item
and get its corresponding entries displayed. So far, so good.
7)But I want the list of unique item names to be extracted so that I can use
that extracted list as validation list in some other area of the worksheet

How to extract the list of unique item names (without resorting to any VBA

Can somebody help me !



2 Answers Found

Answer #1    Answered By: Luisa Fischer     Answered On: Aug 28

Use a pivot table.

Pivot columns A and B then include a count of either column, this should give
you a unique  list. Finally, copy and paste special the pivot table

Answer #2    Answered By: Eshe Chalthoum     Answered On: Aug 28

You can use Advanced Filter option of Excel.

I think you can use following steps.

Go to Data
Select Adavanced Filter
Select Copy to another Location
In List Range Select Column A
in Copy to Select G1
Check Unique Record
Press OK