CODEWITHSUNDEEP
excelrelational-database
syrkull
syrkull

Reputation: 2344

matching and marking ones that are duplicates in excel

I have two tables in excel

first table has product list with an id

product_id   |  package_size   | package_name
1            |  40             | product 1
2            |  100            | product 2
2            |  100            | product 2
2            |  50             | product 2
3            |  100            | product 3
3            |  100            | product 3

second table has two columns:

product_id   |  multiple_package_size
1            |  multiple_package_size
2            |  multiple_package_size
3            |  multiple_package_size

What I am expecting to return on the second table is this:

product_id   |  multiple_package_size
1            | 
2            |           x
3            |

right now my multiple_package_size column is empty, I am trying to put an x on every product id that has multiple package size. I have been using INDEX MATCH but it is not flagging every product that has multiple package size. Here is the formula that I have in the multiple_package_size column now: 

=if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,0)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,1)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,1)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,2)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,2)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,3)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,3)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,4)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,4)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,5)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,5)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,6)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,6)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,7)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,7)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,8)),if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,8)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,9)),,"x"),"x"),"x"),"x"),"x"),"x"),"x"),"x"),"x")

for easy read:

=if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,0)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,1)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,1)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,2)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,2)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,3)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,3)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,4)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,4)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,5)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,5)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,6)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,6)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,7)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,7)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,8)),
if(INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,8)) = INDEX(Sheet3!$E$1:$E$89999,MATCH(A29,Sheet3!$A$1:$A$89999,9)),
,"x")
,"x")
,"x")
,"x")
,"x")
,"x")
,"x")
,"x")
,"x")

What am I doing wrong? is there a better way to do this?

Upvotes: 1

Views: 30

Answers (1)

A.S.H
A.S.H

Reputation: 29352

What you need is to check with COUNTIF

=IF(COUNTIF(Sheet3!$A$1:$A$89999,A29) > 1, "x", "")

What am I doing wrong?

Probably misunderstand the third parameter of the MATCH function.

If the first table has duplicate rows, which wasn't visible in your first question, it's a little more complicated:

=IF(AGGREGATE(14,6,Sheet3!$I$1:$I$89999/(Sheet3!$A$1:$A$89999=A29),1)<>
    AGGREGATE(15,6,Sheet3!$I$1:$I$89999/(Sheet3!$A$1:$A$89999=A29),1),"x", "")

Basically it compares the maximum and minimum package sizes of the matched product id, and if they're different it marks the "x".

p.s. I'm not completely sure that the columns correspond exactly to your data I only tried to guess them from your formula. I assumed that product ID is sheet3 column A, the package size is sheet3 column I and the value searched for is A29. Adjust them if needed.

p.s.2 I think you should consider simply removing the duplicates from your data if that's an option.

Upvotes: 4

Related Questions