Dealing with Duplicate Data from Lists with Unique Values

[KingCoder]

Hi,

This question will probably be easy to some of you.

Sheet A

Column 1 - Unique Values
Column 2 - Mixed Values some Duplicates some Unique

I have a list of values I want to lookup in Column 2, my list has duplicate values, for each duplicate value in the List it relates to an entry in Column 2 which relates to the Unique Value in Column 1.

I want to be able to extract all the unique values in a Lookup/Match scenario, I guess it will be using some form of COUNTIFS and MATCH INDEX but I haven't been able to work it out.

Your help is greatly appreciated.

 

[oldbrewer]

				COLORS
				red				Count of COLORS
				green				COLORS	Total
				red				black	1
				yellow				blue	2
				blue				green	1
				black				purple	1
				blue				red	2
				purple				yellow	1
								Grand Total	8
				red and blue are duplicated
				a very simple pivot table lists the unique colors
				is this helpful ?

<colgroup><col span="8"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[KingCoder]

Hi,

Thanks for your response.

However I am not looking for a sum of duplicate entries and a pivot table is no good to me.

I need to be able to extract all the unique values from Column 1 using a list of entries which reference duplicate values in Column 2.

So there may be three instances of 'Apples' in Column 2 but instance in the column may have unique values of 'Black', 'Orange' and 'Green' in Column 1.

Hope that makes sense.

 

[Marcelo Branco]

Hi,

Thanks for your response.

However I am not looking for a sum of duplicate entries and a pivot table is no good to me.

I need to be able to extract all the unique values from Column 1 using a list of entries which reference duplicate values in Column 2.

So there may be three instances of 'Apples' in Column 2 but instance in the column may have unique values of 'Black', 'Orange' and 'Green' in Column 1.

Hope that makes sense.

A data sample along with desired results would be helpful.

M.

 

[KingCoder]

No problem could you point me in the direction of a tutorial on how to post sample cells into the forum? I'll post some sample data straight away many thanks.

 

[Marcelo Branco]

See section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

The easiest way is: select a relevant area of your spreadsheet (~10 rows); Copy (Ctrl+C); Paste (Ctrl+V) in the forum reply page.
And also tell us the expected results related to such data sample

M.

 

[oldbrewer]

not clear to me what you want -

So there may be three instances of 'Apples' in Column 2 but instance in the column may have unique values of 'Black', 'Orange' and 'Green' in Column 1.

Hope that makes sense. is there a typo in it ?