Consolidating to unique data

[Adison]

I have a solution for the challenge however I am curious if there is a better way. Thanks beforehand for your attention.

Example -

A column is populated with 3 or more possible entries. There are numerous rows. What is the most efficient way to discover the three possible entries?

Currently I use the following technique.

sampval.jpg

Attachment 354140

samp.xls



Is this the most efficient method to consolidate?

Thanks again!

[mcmahobt]

There's a consolidation as well as remove duplicates tool for Excel data. It's under the Data tab in the ribbon, "Consolidate" and "Remove Duplicates" respectively.

Hope this helps.

[mcmahobt]

As I see you've edited your post a few times, I'd say for your data, use the "Remove Duplicates" under the Data tab in the ribon. This is a useful option to achieve what you are looking for in terms of unique data.

[Adison]

Yes many edits because of difficulty with jpg showing of formulas involved.

Thanks for response, however I am looking for a formula solution that will work dynamically and not menu command solution. Thanks.

And as mentioned, I have a solution as shown above. However is there a better way (using formulas).

[mcmahobt]

See attached. Basically you will drag down the formula in cell B2 until values start repeating...that's when you know you have all the unique values.

[Adison]

Thanks Mc. Semi solves the challenge. As long as formula is copied down 1 more then as many unique values

[mcmahobt]

Correct. Someone else on the foum may be able to automate it down. Also, make sure to note the named range that is used in the formula.

[Adison]

Actually,it's not working

See attached.

When yellow is added and the formula is copied down...

ExampleError.xlsx

[mcmahobt]

See attached. Note that column B and C can be hidden for this to work. Formatting the A column as a table will allow you to dynamically update the Color List in column D as well.

Hope this helps.