‘Unique fucntion’ in excel (97)

Topic

New Reply

I have an excel worksheet with about 10,000 rows of data and 10 or so columns. i would like to grab a list of unique vendor names from column C, many of the names reappear in each row. is there a function that can do this for me. i just want a list of unique values in a row. i’ve tried using access for this but my query seems to freeze everytime. any ideas.

Reply | Quote

Replies

You can do this several ways without having a function:

1. Run an advanced filter and select unique values.

2. Say you wanted to get unique data for Column A data with data starting in A1
a. Sort the data in column a in ascending order
b. In B1 type in the following formula =A1=A2 (Returns False if data is different, True if the same)
c. copy the formula in B1 down to the end of the dataset.
d. copy and paste column b as a paste special, values. (Replace the formula results true and false with text)
e. Sort on Column B
f. All of the false values are unique.

You could create a function on example 2 to do this.

Reply | Quote

I’m a little confused. “Many of the names reappear in EACH ROW” ??? You want a list of unique values in a ROW? With 10,000 records that seems like a big ol’ row.

My gut feeling was that a Pivot Table would solve your problem most easily.

Make a Pivot Table (Data…Pivot Wizard) that uses Vendor Name (Column C) as the Range and as the Row title and you can even use “Count of Vendor Name” as the Data. You really don’t have to select or use any of the other columns in your data table if you don’t want to. Pivot table will give you a column listing all the unique Vendor names. And Pivots update easily.

Reply | Quote