Duplicated lookup value needing unique return

R

russborup

New Member
Joined
May 2, 2018
Messages
23
I have data that can be duplicated but will need to return specific serial numbers.
In one tab i will have the following:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Name[/TD]
[TD="class: xl66, width: 64"]Model[/TD]
[TD="class: xl66, width: 64"]Color[/TD]
[TD="class: xl66, width: 64"]PO[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789
[/TD]
[/TR]
</tbody>[/TABLE]

I will receive serial numbers back referencing duplicated criteria that need to be populated in the existing tab.

Example:

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Name[/TD]
[TD="class: xl66, width: 64"]Model[/TD]
[TD="class: xl66, width: 64"]Color[/TD]
[TD="class: xl66, width: 64"]PO[/TD]
[TD="class: xl67, width: 64"]Serial#[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]234[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]345[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]456[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]567[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]678[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]654[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]543[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]432[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]321[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]210[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]233[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]322[/TD]
[/TR]
</tbody>[/TABLE]

What can I do to have the serial number from the second example populate to the first example?
**Note that the serial numbers will come in at different times for different PO's.
 
Sort by date Sort by votes

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Aladin Akyurek,
I am now running into issues where the serial number is not populating even though it is in the array.
Here is the sheet and formula that I am trying to populate:

Formula in F:
=IF(AG2<>0,AG2,INDEX('HY SER'!$E$2:$E$10,SMALL(IF('HY SER'!$C$2:$C$10=I2,IF('HY SER'!$D$2:$D$10=L2,ROW('HY SER'!$E$2:$E$10)-ROW('Data Entry'!V2)+1)),COUNTIFS($I$2:$I2,$I2,$L$2:$L2,$L2))))

[TABLE="width: 423"]
<tbody>[TR]
[TD]Row/Col
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]PO #
[/TD]
[TD]Serial
[/TD]
[TD]Description
[/TD]
[TD]Color
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]AZ944
[/TD]
[TD]QAMD65LC4JU100008
[/TD]
[TD]Bearring
[/TD]
[TD]GRAY
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]AZ944
[/TD]
[TD]QAMD65LC9JU099759
[/TD]
[TD]Bearring
[/TD]
[TD]GRAY
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]AZ944
[/TD]
[TD]QAMD65LC9JU099759
[/TD]
[TD]Bearring
[/TD]
[TD]GRAY
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]AZ944
[/TD]
[TD] #NUM !
[/TD]
[TD]Bearring
[/TD]
[TD]GRAY
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]AZ944
[/TD]
[TD] #NUM !
[/TD]
[TD]Bearring
[/TD]
[TD]GRAY
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]AZ944
[/TD]
[TD] #NUM !
[/TD]
[TD]Bearring
[/TD]
[TD]GRAY
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]AZ944
[/TD]
[TD] #VALUE !
[/TD]
[TD]Bearring
[/TD]
[TD]SILVER
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]AZ944
[/TD]
[TD] #VALUE !
[/TD]
[TD]Bearring
[/TD]
[TD]SILVER
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]AZ944
[/TD]
[TD] #VALUE !
[/TD]
[TD]Bearring
[/TD]
[TD]SILVER
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]AZ944
[/TD]
[TD] #VALUE !
[/TD]
[TD]Bearring
[/TD]
[TD]SILVER
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]AZ944
[/TD]
[TD]#NUM !
[/TD]
[TD]Bearring
[/TD]
[TD]SILVER
[/TD]
[/TR]
</tbody>[/TABLE]


Tab HY SER

[TABLE="width: 653"]
<tbody>[TR]
[TD]Row/Col
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Order Create Date
[/TD]
[TD]Alloc. Date
[/TD]
[TD]PO#
[/TD]
[TD]Color
[/TD]
[TD]Serial #
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]4/20/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD]AZ944
[/TD]
[TD]SILVER
[/TD]
[TD]QAMD65LC1JU099755
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]4/21/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD]AZ944
[/TD]
[TD]SILVER
[/TD]
[TD]QAMD65LC1JU099996
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4/22/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD]AZ944
[/TD]
[TD]SILVER
[/TD]
[TD]QAMD65LC1JU100046
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]4/23/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD]AZ944
[/TD]
[TD]GRAY
[/TD]
[TD]QAMD65LC4JU100008
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]4/24/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD]AZ944
[/TD]
[TD]SILVER
[/TD]
[TD]QAMD65LC9JU099759
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]4/25/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD]AZ944
[/TD]
[TD]GRAY
[/TD]
[TD]QAMD65LCXJU099687
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]4/26/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD]AZ944
[/TD]
[TD]GRAY
[/TD]
[TD]QAMD65LCXJU100014
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
The formula you post doesn't look correct... Does this work?

=IF(AG2<>0,AG2,INDEX('HY SER'!$E$2:$E$10,SMALL(IF('HY SER'!$C$2:$C$10=$I2,IF('HY SER'!$D$2:$D$10=$L2,ROW('HY SER'!$E$2:$E$10)-ROW('HY SER'!$E$2)+1)),COUNTIFS($I$2:$I2,$I2,$L$2:$L2,$L2))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Multiple emails with each row data
Replies
1
Views
788
starl
starl
R
  • Question Question
Drop Down depencies
Replies
4
Views
661
Fluff
Fluff
L
  • Question Question
Indicate First Duplicate Value in a Column when a condition exists in another column
Replies
1
Views
729
Fluff
Fluff
L
  • Question Question
Use table column headers for cell reference & search criteria?
Replies
9
Views
1K
Fluff
Fluff
A
  • Question Question
Compare/Match for columns in one sheet
Replies
1
Views
484
bhos123
B

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top