Compare two columns and find duplicate and unique entries

[s8ntandrew]

I have a spreadsheet with two columns. Each is full of duplicated data points. I'd like to search Column A for duplicates and create an array based on the results that I can search the associated data in Column B for the first unique data points. Make sense?


So, for example, Column A includes state names (e.g., Arizona, Oregon, California, etc...) and Column B includes an associated automobile types (e.g., Chevy, Ford, Honda). I want to search all of the Oregon entries and produce the first mention of each automobile type. So the cell where the formula is should produce Chevy, Ford, Honda, etc....


Is this possible?

 

[s8ntandrew]

Perhaps an example from my spreadsheet would help.


Column A Column B

1040 700

1040 700

1040 800

1040 800

2000 700

2000 700

2000 850

2000 900

2100 800

2100 900

2100 900


In column C, I'd like to produce 700,800 for the 1040 entries, 700,850,900 for the 2000 entries and 800,900 for the 2100 entries.


Any thoughts? Anyone?

 

[Hui]

s8ntandrew

Have you tried a pivot table of the data

That's is as close to what you want that I can think of


Have Column A as the Row labels, Column B as Column labels and use a custom formula Sum(Column Field)/Count(Column Field), which will leave the Column values in the table area.

You can then not display the Column Labels

 

[hiwiller]

Hi s8ntandrew.


You can identify the unique values compared across 2 columns with an array formula.

Given your data above, you can add a third column (C) and, presuming your data start on row 2, enter the following formula in C2 (use CTRL+SHIFT+ENTER to enter an array formula):

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,"",B2)

Then copy the formula down.

This will give you a the value from column B only if it is the first occurrence of that value in the subset of matching column A values. If you wanted to do something like total the unique values in a pivot table, change the last 2 arguments to ...0,1) and then you can do your math functions.

 

[hiwiller]

Ah, just re-read your question and see you want the concatenated values (comma separated):


You can try this in addition to my previous post. In a fourth column (D), enter this formula in D2 (again presuming your data start on row 2 - headings on 1)

=IF(C2>"",C2 &IF(AND(A3=A2,D3>""),", "&D3,""),IF(AND(A3=A2,D3>""),D3,""))

Copy and paste the formula again to the end. This is not an array formula so just enter it normally. This should give you the topmost row in each group with your comma separated list of unique values. (forgive me for any superfluous code... testing it quickly)