CODEWITHSUNDEEP
excelformattingexcel-formulacompare
bl4ckb0l7
bl4ckb0l7

Reputation: 3999

Excel: Find duplicates in column with differences in another column

I want to highlight cells in column A, which have duplicates in column A but a difference in column B.

A   B  
1   2   -
2   3   +
3   2   -
2   4   +
1   2   -
3   2   -
4   5   -

The rows (or a cell within the row) with the - shall not be highlighted, but the rows (or a cell within the row) with the + shall be highlighted.

How can I accomplish this in an Excel formula?

Please pay attention to the fact, that not all unique combinations shall be highlighted (last row!).

In SQL the corresponding query would be something like this:

SELECT *
FROM table
GROUP BY A
HAVING COUNT(B) > 1

Upvotes: 0

Views: 2757

Answers (4)

pps
pps

Reputation: 58

Mark column A > Home > Style > Conditional Formatting> New Rule... > Use a formula to determine which cells to format > enter the formula

=SUMPRODUCT(--(A:A=A1),--(B:B<>B1))

> Format... > choose some formatting of your liking > OK > OK

Upvotes: 1

Mitch
Mitch

Reputation: 544

Here is another way to do it assuming your above data is in cells A2:B7:

1) Copy and paste your column A values to a blank section of your workbook(Lets say A11) and perform the following function Data->Remove Duplicates with the section selected.

2) Highlight cells B10:B13(all cells where a value is in column A) and type in the following formula:

=FREQUENCY(A2:A8,A10:A13)

Hit Ctrl + Shift + Enter to make this an array.

3) Similar to step two highlight all cells in column C where there is data in columns A and B. In this case C2:C7 and use the following formula:

=IF(VLOOKUP(A2,$A$10:$B$13,2,FALSE)>1,IF(FREQUENCY(VALUE(CONCATENATE($A$2:$A$7,$B$2:$B$7)),VALUE(CONCATENATE($A$2:$A$7,$B$2:$B$7)))<>1,"","Highlight"),"")

Hit Ctrl + Shift + Enter to make this an array.

Your cells that need to be highlighted will now say "Highlight"

enter image description here

Upvotes: 0

Mitch
Mitch

Reputation: 544

A simpler solution might be to use Concatenate to join A and B together and use a conditional formating to highlight the unique values. This would leave your desired list highlighted:

enter image description here

For the Conditional Formatting highlight column C then navigate:

Home-> Conditional Formatting -> New Rule-> Format only unique or duplicate values

Then change selection from "duplicate" to "unique" and select the desired format. Apply the setting and have identified the appropriate rows.

Upvotes: 1

Ditto
Ditto

Reputation: 3344

Assuming your data is in A1:B7, (with "A" and "B" as headers on row 1):

I used the following formulas to get the matches .. I just did a simple search after, and before .. if it finds a record above or below, it "flags" it in column F as TRUE. Not sure it works for 3 or more duplicates, though you didn't seem to indicate how you wanted a 3 of a kind to work ;)

D2=MATCH(A2,A3:$A$1000,0)
E2=IF(ISERROR(D2),IF(ISERROR(G2),"",OFFSET($A$1,G2,0,1,1)),OFFSET(B2,D2,0,1,1))
F2=AND(NOT(AND(ISERROR(D2),ISERROR(G2))),B2<>E2)
G2=MATCH(A2,$A$1:A1,0)`

D col locates the first matching A column after the current row.

G col locates the first matching A Column prior to current row.

E col pulls that remote B column value to current row to more easily check.

F col puts the logic together: If we found something, and B cols are not equal.

Upvotes: 0

Related Questions