Match a value but return a different column value without duplicates

[aheffy]

I'm working with a large amount of data in two separate tabs:

Tab X: Column A (Product #) contains a list of product numbers (e.g. 700, 701, etc). Column B (Load ID) contains a unique ID for one specific unit of that product (e.g. 12345, 12346, etc).

Tab Y: Column A (Product #) contains a list of product numbers again (e.g. 700, 701, etc). Column B (Order #) contains a list of customer orders (e.g. ST567, ST568).

I am trying to match the product numbers in tab X with the product numbers in tab Y and return a unique customer order number (column B) that relates to that product number. However, I can only use each order number once, I can't have duplicates. Put differently, I am attempting to pair unique ID's of various product numbers to unique order numbers of the same product without duplicates.

I've attempted various index and match formulae as well as various vlookup formulae with nested ifs. I don't believe I have gotten close with either of these.

Thank you in advance for any help you can provide!!!

 

[strooman]

Can you provide a sample or screenshot with the desired result? We have to see how the layout of the data is.

 

[aheffy]

Sure! Sorry, I should I have done that from the beginning.

Can you provide a sample or screenshot with the desired result? We have to see how the layout of the data is.

 

[aheffy]

[/IMG]

 

[strooman]

OK, I presume that the image is from Tab X and that the yellow area is where the formula is inserted. Is this a correct assumption?

When this is a Yes, where do we lookup the Order # in column C? I mean, are the Order # in a table on Tab Y? If Yes then please send screenshot from Tab Y.

 

[aheffy]

OK, I presume that the image is from Tab X and that the yellow area is where the formula is inserted. Is this a correct assumption?

When this is a Yes, where do we lookup the Order # in column C? I mean, are the Order # in a table on Tab Y? If Yes then please send screenshot from Tab Y.

Yes, your assumption is correct. The yellow area is where the formula will be inserted on Tab X.

Here is a screenshot of Tab Y:

Thank you for your help!

 

[strooman]

Well, it's a difficult lookup because you got nothing to go on. You only have the Product# and there are duplicates as you mentioned.
The only thing I can think of is to insert a helper column in COLUMN E.

Formula's:

In C2:

=IFERROR(VLOOKUP($A2&"-"&COUNTIF($A$2:$A2;$A2);$E$2:$G$25;3;FALSE);"")

And copy down.

In E2 (the helper column):

=$F2&"-"&COUNTIF($F$2:F2;F2)

And copy down.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#0070C0"]Product_Number[/td][td="bgcolor:#0070C0"]Load_ID[/td][td="bgcolor:#0070C0"]Order_Number[/td][td][/td][td="bgcolor:#BFBFBF"]Helper[/td][td="bgcolor:#0070C0"]Product_Number[/td][td="bgcolor:#0070C0"]Order_Number[/td][/tr]
[tr][td]

2​

[/td][td]

703​

[/td][td]

12345​

[/td][td="bgcolor:#FFFF00"]ST361[/td][td][/td][td]709-1[/td][td]

709​

[/td][td]ST567[/td][/tr]
[tr][td]

3​

[/td][td]

708​

[/td][td]

12346​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td]705-1[/td][td]

705​

[/td][td]ST568[/td][/tr]
[tr][td]

4​

[/td][td]

709​

[/td][td]

12347​

[/td][td="bgcolor:#FFFF00"]ST567[/td][td][/td][td]701-1[/td][td]

701​

[/td][td]ST329[/td][/tr]
[tr][td]

5​

[/td][td]

701​

[/td][td]

12348​

[/td][td="bgcolor:#FFFF00"]ST329[/td][td][/td][td]710-1[/td][td]

710​

[/td][td]ST210[/td][/tr]
[tr][td]

6​

[/td][td]

701​

[/td][td]

12349​

[/td][td="bgcolor:#FFFF00"]ST853[/td][td][/td][td]701-2[/td][td]

701​

[/td][td]ST853[/td][/tr]
[tr][td]

7​

[/td][td]

702​

[/td][td]

12350​

[/td][td="bgcolor:#FFFF00"]ST490[/td][td][/td][td]701-3[/td][td]

701​

[/td][td]ST580[/td][/tr]
[tr][td]

8​

[/td][td]

708​

[/td][td]

12351​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td]707-1[/td][td]

707​

[/td][td]ST385[/td][/tr]
[tr][td]

9​

[/td][td]

700​

[/td][td]

12352​

[/td][td="bgcolor:#FFFF00"]ST456[/td][td][/td][td]702-1[/td][td]

702​

[/td][td]ST490[/td][/tr]
[tr][td]

10​

[/td][td]

702​

[/td][td]

12353​

[/td][td="bgcolor:#FFFF00"]ST381[/td][td][/td][td]704-1[/td][td]

704​

[/td][td]ST399[/td][/tr]
[tr][td]

11​

[/td][td]

704​

[/td][td]

12354​

[/td][td="bgcolor:#FFFF00"]ST399[/td][td][/td][td]704-2[/td][td]

704​

[/td][td]ST443[/td][/tr]
[tr][td]

12​

[/td][td]

700​

[/td][td]

12355​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td]705-2[/td][td]

705​

[/td][td]ST274[/td][/tr]
[tr][td]

13​

[/td][td]

705​

[/td][td]

12356​

[/td][td="bgcolor:#FFFF00"]ST568[/td][td][/td][td]707-2[/td][td]

707​

[/td][td]ST494[/td][/tr]
[tr][td]

14​

[/td][td]

710​

[/td][td]

12357​

[/td][td="bgcolor:#FFFF00"]ST210[/td][td][/td][td]707-3[/td][td]

707​

[/td][td]ST482[/td][/tr]
[tr][td]

15​

[/td][td]

705​

[/td][td]

12358​

[/td][td="bgcolor:#FFFF00"]ST274[/td][td][/td][td]710-2[/td][td]

710​

[/td][td]ST242[/td][/tr]
[tr][td]

16​

[/td][td]

703​

[/td][td]

12359​

[/td][td="bgcolor:#FFFF00"]ST407[/td][td][/td][td]710-3[/td][td]

710​

[/td][td]ST416[/td][/tr]
[tr][td]

17​

[/td][td]

700​

[/td][td]

12360​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td]709-2[/td][td]

709​

[/td][td]ST275[/td][/tr]
[tr][td]

18​

[/td][td]

710​

[/td][td]

12361​

[/td][td="bgcolor:#FFFF00"]ST242[/td][td][/td][td]704-3[/td][td]

704​

[/td][td]ST317[/td][/tr]
[tr][td]

19​

[/td][td]

703​

[/td][td]

12362​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td]709-3[/td][td]

709​

[/td][td]ST240[/td][/tr]
[tr][td]

20​

[/td][td]

703​

[/td][td]

12363​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td]703-1[/td][td]

703​

[/td][td]ST361[/td][/tr]
[tr][td]

21​

[/td][td]

707​

[/td][td]

12364​

[/td][td="bgcolor:#FFFF00"]ST385[/td][td][/td][td]710-4[/td][td]

710​

[/td][td]ST413[/td][/tr]
[tr][td]

22​

[/td][td]

710​

[/td][td]

12365​

[/td][td="bgcolor:#FFFF00"]ST416[/td][td][/td][td]707-4[/td][td]

707​

[/td][td]ST486[/td][/tr]
[tr][td]

23​

[/td][td]

700​

[/td][td]

12366​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td]702-2[/td][td]

702​

[/td][td]ST381[/td][/tr]
[tr][td]

24​

[/td][td]

707​

[/td][td]

12367​

[/td][td="bgcolor:#FFFF00"]ST494[/td][td][/td][td]703-2[/td][td]

703​

[/td][td]ST407[/td][/tr]
[tr][td]

25​

[/td][td]

709​

[/td][td]

12368​

[/td][td="bgcolor:#FFFF00"]ST275[/td][td][/td][td]700-1[/td][td]

700​

[/td][td]ST456[/td][/tr]
[tr][td]

26​

[/td][td="bgcolor:#FFD1D1"][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#FFD1D1"][/td][td][/td][/tr]
[tr][td]

27​

[/td][td="bgcolor:#FFD1D1"]Tab X[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#FFD1D1"]Tab Y[/td][td][/td][/tr]
[/table]

You have to adjust the formula's so that they refere to your sheetnames.

 

[aheffy]

Well, it's a difficult lookup because you got nothing to go on. You only have the Product# and there are duplicates as you mentioned.
The only thing I can think of is to insert a helper column in COLUMN E.

Formula's:

In C2:

=IFERROR(VLOOKUP($A2&"-"&COUNTIF($A$2:$A2;$A2);$E$2:$G$25;3;FALSE);"")

And copy down.

In E2 (the helper column):

=$F2&"-"&COUNTIF($F$2:F2;F2)

And copy down.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD="bgcolor: #0070C0"]Product_Number[/TD]
[TD="bgcolor: #0070C0"]Load_ID[/TD]
[TD="bgcolor: #0070C0"]Order_Number[/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"]Helper[/TD]
[TD="bgcolor: #0070C0"]Product_Number[/TD]
[TD="bgcolor: #0070C0"]Order_Number[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

703​

[/TD]
[TD]

12345​

[/TD]
[TD="bgcolor: #FFFF00"]ST361[/TD]
[TD][/TD]
[TD]709-1[/TD]
[TD]

709​

[/TD]
[TD]ST567[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

708​

[/TD]
[TD]

12346​

[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD]705-1[/TD]
[TD]

705​

[/TD]
[TD]ST568[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

709​

[/TD]
[TD]

12347​

[/TD]
[TD="bgcolor: #FFFF00"]ST567[/TD]
[TD][/TD]
[TD]701-1[/TD]
[TD]

701​

[/TD]
[TD]ST329[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

701​

[/TD]
[TD]

12348​

[/TD]
[TD="bgcolor: #FFFF00"]ST329[/TD]
[TD][/TD]
[TD]710-1[/TD]
[TD]

710​

[/TD]
[TD]ST210[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

701​

[/TD]
[TD]

12349​

[/TD]
[TD="bgcolor: #FFFF00"]ST853[/TD]
[TD][/TD]
[TD]701-2[/TD]
[TD]

701​

[/TD]
[TD]ST853[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

702​

[/TD]
[TD]

12350​

[/TD]
[TD="bgcolor: #FFFF00"]ST490[/TD]
[TD][/TD]
[TD]701-3[/TD]
[TD]

701​

[/TD]
[TD]ST580[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

708​

[/TD]
[TD]

12351​

[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD]707-1[/TD]
[TD]

707​

[/TD]
[TD]ST385[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

700​

[/TD]
[TD]

12352​

[/TD]
[TD="bgcolor: #FFFF00"]ST456[/TD]
[TD][/TD]
[TD]702-1[/TD]
[TD]

702​

[/TD]
[TD]ST490[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

702​

[/TD]
[TD]

12353​

[/TD]
[TD="bgcolor: #FFFF00"]ST381[/TD]
[TD][/TD]
[TD]704-1[/TD]
[TD]

704​

[/TD]
[TD]ST399[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

704​

[/TD]
[TD]

12354​

[/TD]
[TD="bgcolor: #FFFF00"]ST399[/TD]
[TD][/TD]
[TD]704-2[/TD]
[TD]

704​

[/TD]
[TD]ST443[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

700​

[/TD]
[TD]

12355​

[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD]705-2[/TD]
[TD]

705​

[/TD]
[TD]ST274[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

705​

[/TD]
[TD]

12356​

[/TD]
[TD="bgcolor: #FFFF00"]ST568[/TD]
[TD][/TD]
[TD]707-2[/TD]
[TD]

707​

[/TD]
[TD]ST494[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

710​

[/TD]
[TD]

12357​

[/TD]
[TD="bgcolor: #FFFF00"]ST210[/TD]
[TD][/TD]
[TD]707-3[/TD]
[TD]

707​

[/TD]
[TD]ST482[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

705​

[/TD]
[TD]

12358​

[/TD]
[TD="bgcolor: #FFFF00"]ST274[/TD]
[TD][/TD]
[TD]710-2[/TD]
[TD]

710​

[/TD]
[TD]ST242[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

703​

[/TD]
[TD]

12359​

[/TD]
[TD="bgcolor: #FFFF00"]ST407[/TD]
[TD][/TD]
[TD]710-3[/TD]
[TD]

710​

[/TD]
[TD]ST416[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

700​

[/TD]
[TD]

12360​

[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD]709-2[/TD]
[TD]

709​

[/TD]
[TD]ST275[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

710​

[/TD]
[TD]

12361​

[/TD]
[TD="bgcolor: #FFFF00"]ST242[/TD]
[TD][/TD]
[TD]704-3[/TD]
[TD]

704​

[/TD]
[TD]ST317[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

703​

[/TD]
[TD]

12362​

[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD]709-3[/TD]
[TD]

709​

[/TD]
[TD]ST240[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

703​

[/TD]
[TD]

12363​

[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD]703-1[/TD]
[TD]

703​

[/TD]
[TD]ST361[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

707​

[/TD]
[TD]

12364​

[/TD]
[TD="bgcolor: #FFFF00"]ST385[/TD]
[TD][/TD]
[TD]710-4[/TD]
[TD]

710​

[/TD]
[TD]ST413[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

710​

[/TD]
[TD]

12365​

[/TD]
[TD="bgcolor: #FFFF00"]ST416[/TD]
[TD][/TD]
[TD]707-4[/TD]
[TD]

707​

[/TD]
[TD]ST486[/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

700​

[/TD]
[TD]

12366​

[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD]702-2[/TD]
[TD]

702​

[/TD]
[TD]ST381[/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

707​

[/TD]
[TD]

12367​

[/TD]
[TD="bgcolor: #FFFF00"]ST494[/TD]
[TD][/TD]
[TD]703-2[/TD]
[TD]

703​

[/TD]
[TD]ST407[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]

709​

[/TD]
[TD]

12368​

[/TD]
[TD="bgcolor: #FFFF00"]ST275[/TD]
[TD][/TD]
[TD]700-1[/TD]
[TD]

700​

[/TD]
[TD]ST456[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD="bgcolor: #FFD1D1"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFD1D1"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD="bgcolor: #FFD1D1"]Tab X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFD1D1"]Tab Y[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


You have to adjust the formula's so that they refere to your sheetnames.

That did it! Thank you so much for your help. I should have thought of that, but I didn't and I don't think it was going to come to me overnight. Much appreciated!

 

[strooman]

Thanks for the feedback. Glad i could help.