Extract Data From Specific Column

[powerpuffgirl]

I have been struggling with coming up with the right formula/ code to match multiple criteria and provide data from specific column from sheet 2 to sheet 1. I have two worksheets, one in terms of vendor and the other in customer PO.

Sheet 1

Customer Order	Qty	Part Number	Due Date
CH34567	14	H738490	Nov 4

<tbody>
</tbody>

I would like to spit out PO Line Item column (B) next to each line on sheet 1 from sheet 2. This is an issue because the data in sheet 2 is formatted differently as I have 4 part numbers columns corresponding to PO Line Item in each row, unlike flattened data in sheet 1. I have to match column A sheet 1 to column A sheet 2. If that is true, I need to match column C sheet 1 to column C, D, E and F on sheet 2. If both are true, I would like the formula to produce Column B sheet 2 in sheet 1 column E.

Sheet 2

Customer Order	PO Line Item	PN Hubs	PN Flanges	PN Seal Rings	PN Bolts
CH34567	131	H778400	A73973	SR38377	B38388
CH34895	225	H363839	A77489	SR47488	B39847
CH07478	145	H476567	A36378	SR73738	B74466
CH34567	155	H778400	A87664	SR64747	B56378

<tbody>
</tbody>

The issue is that on sheet 2, a same part number can correspond to multiple PO line item (note above Hub PN H778400 corresponds to PO Line 131 and 155). I need to be able to spit out all PO Line Items next to Ship Date column on sheet 1.

I have been playing around with Index and Match formulas, but I am just not getting it right. Any help would be greatly appreciated.

 

[powerpuffgirl]

Not getting the second formula to work. Why are we referencing B1 in Match formula? MATCH(B$1,Sheet01!$A$1:$F$1,0) < I am a bit confused about this portion of the formula.

Maybe this:

Layout

[TABLE="width: 741"]
<tbody>[TR]
[TD="class: xl66, width: 27, bgcolor: transparent"]PO #[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]Order[/TD]
[TD="class: xl66, width: 27, bgcolor: transparent"]Qty[/TD]
[TD="class: xl66, width: 77, bgcolor: transparent"]Item[/TD]
[TD="class: xl66, width: 57, bgcolor: transparent"]Ship Date[/TD]
[TD="class: xl66, width: 170, bgcolor: transparent"]Item Description[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 1[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 2[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 3[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 4[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 5[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 6[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 7[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 8[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]PO Line 9[/TD]
[TD="class: xl66, width: 62, bgcolor: transparent"]PO Line 10[/TD]
[TD="class: xl65, width: 46, bgcolor: transparent"]Sheet01[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]131[/TD]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]31[/TD]
[TD="class: xl63, bgcolor: transparent"]A90019-78WB[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/nov[/TD]
[TD="class: xl63, bgcolor: transparent"]CLAMP,2PC 4BOLT, 1, AISI 4140,[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]53,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]61,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]69,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]70,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]73,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]81,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]82,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]86,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]87,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]131[/TD]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl63, bgcolor: transparent"]A90148-96WB[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]18/nov[/TD]
[TD="class: xl63, bgcolor: transparent"]CLAMP,2PC 4BOLT, 2, AISI 4140,[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]55,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]65,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]75,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]88,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]131[/TD]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl63, bgcolor: transparent"]A90413-77WB[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/nov[/TD]
[TD="class: xl63, bgcolor: transparent"]CLAMP,2PC 4BOLT, 2-1/2- 3, AISI[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]57,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]67,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]76,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]79,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]80,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]84,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]131[/TD]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]H90235-45WB[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/nov[/TD]
[TD="class: xl63, bgcolor: transparent"]CLAMP,2PC 4BOLT, 4, AISI 4140,[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]77,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]83,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]85,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]131[/TD]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl63, bgcolor: transparent"]H90329-35WB[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]05/nov[/TD]
[TD="class: xl63, bgcolor: transparent"]CLAMP,2PC 4BOLT, 8, AISI 4140,[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]71,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]72,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]74,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]131[/TD]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]H90314-36WB[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/nov[/TD]
[TD="class: xl63, bgcolor: transparent"]CLAMP,2PC 4BOLT,10H, AISI 4140,[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]63,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]66,1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]78,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]****[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]****[/TD]
[TD="class: xl63, bgcolor: transparent"]**************[/TD]
[TD="class: xl63, bgcolor: transparent"]**********[/TD]
[TD="class: xl63, bgcolor: transparent"]********************************[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[TD="class: xl63, bgcolor: transparent"]***********[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 335"]
<tbody>[TR]
[TD="class: xl69, width: 42, bgcolor: transparent"]PO Line[/TD]
[TD="class: xl69, width: 52, bgcolor: transparent"]PO #[/TD]
[TD="class: xl69, width: 57, bgcolor: transparent"]Ship Date[/TD]
[TD="class: xl69, width: 77, bgcolor: transparent"]Item[/TD]
[TD="class: xl69, width: 170, bgcolor: transparent"]Item Description[/TD]
[TD="class: xl67, width: 46, bgcolor: transparent"]Sheet03[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]53,1[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]131[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]04/nov[/TD]
[TD="class: xl68, bgcolor: yellow"]A90019-78WB[/TD]
[TD="class: xl68, bgcolor: yellow"]CLAMP,2PC 4BOLT, 1, AISI 4140,[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]55,1[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]131[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]18/nov[/TD]
[TD="class: xl68, bgcolor: yellow"]A90148-96WB[/TD]
[TD="class: xl68, bgcolor: yellow"]CLAMP,2PC 4BOLT, 2, AISI 4140,[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]57,1[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]131[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]22/nov[/TD]
[TD="class: xl68, bgcolor: yellow"]A90413-77WB[/TD]
[TD="class: xl68, bgcolor: yellow"]CLAMP,2PC 4BOLT, 2-1/2- 3, AISI[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]61,1[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]131[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]04/nov[/TD]
[TD="class: xl68, bgcolor: yellow"]A90019-78WB[/TD]
[TD="class: xl68, bgcolor: yellow"]CLAMP,2PC 4BOLT, 1, AISI 4140,[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]63,1[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]131[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]07/nov[/TD]
[TD="class: xl68, bgcolor: yellow"]H90314-36WB[/TD]
[TD="class: xl68, bgcolor: yellow"]CLAMP,2PC 4BOLT,10H, AISI 4140,[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]65,1[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]131[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]18/nov[/TD]
[TD="class: xl68, bgcolor: yellow"]A90148-96WB[/TD]
[TD="class: xl68, bgcolor: yellow"]CLAMP,2PC 4BOLT, 2, AISI 4140,[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*******[/TD]
[TD="class: xl65, bgcolor: transparent"]*********[/TD]
[TD="class: xl66, bgcolor: transparent"]**********[/TD]
[TD="class: xl65, bgcolor: transparent"]**************[/TD]
[TD="class: xl65, bgcolor: transparent"]********************************[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Array formulas - use Ctrl+Shift+Enter and not only Enter to enter the formula or use fn+ F2 and then Ctrl+Shift+Enter on a Mac (like you said).

In A2

=IFERROR(SMALL(IF(Sheet01!$G$2:$P$7<>"",Sheet01!$G$2:$P$7),ROWS($A$2:$A2)),"")

And copy down.

In B2

=IF($A2="","",INDEX(Sheet01!$A$2:$F$7,SMALL(IF(Sheet01!$G$2:$P$7=$A2,ROW(Sheet01!$G$2:$P$7)-ROW(Sheet01!$G$2)+1),1),MATCH(B$1,Sheet01!$A$1:$F$1,0)))

And copy to the right and down.

[COLOR=#FF0000]PS: here, in my computer configuration, "." = ","[/COLOR]

Markmzz

 

[markmzz]

Not getting the second formula to work. Why are we referencing B1 in Match formula? MATCH(B$1,Sheet01!$A$1:$F$1,0) < I am a bit confused about this portion of the formula.

Hi,

B$1 (and C$1, D$1 and E$1) is the header in Sheet03 and the same header in Sheet01. Look at my examples sheets.

So MATCH(B$1,Sheet01!$A$1:$F$1,0) find the column of the data that you need (PO #, Ship Date, Item and Item Description).

Markmzz

 

[powerpuffgirl]

The first formula is skipping some PO Line Items on sheet01. Not sure why that would be. There are rows in sheet01 that are completely blank. Maybe because of that it's not capturing everything and skipping items between columns?

For example:

[TABLE="width: 1216"]
<colgroup><col><col><col><col><col span="2"><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]PO[/TD]
[TD]Customer Order No.[/TD]
[TD]Job No.[/TD]
[TD]Part Number[/TD]
[TD]Qty Released[/TD]
[TD]Qty Complete[/TD]
[TD]Ship Date[/TD]
[TD]Status[/TD]
[TD]Description[/TD]
[TD]PO Line 1[/TD]
[TD]PO Line 2[/TD]
[TD]PO Line 3[/TD]
[TD]PO Line 4[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]JH43694[/TD]
[TD]H90027-51[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]11/15/13[/TD]
[TD]R[/TD]
[TD]GRAYLOC SEAL RING, 23,HIGH STRENGTH, AI[/TD]
[TD]79.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]JH43696[/TD]
[TD]H90034-91[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]12/02/13[/TD]
[TD]R[/TD]
[TD]GRAYLOC SEAL RING, 62,HIGH STRENGTH, AI[/TD]
[TD]74.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]JH43707[/TD]
[TD]H90133-1396[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]11/15/13[/TD]
[TD]R[/TD]
[TD]GRAYLOC HUB,BW, 8 GR42, 8.625 OD x 4.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]JH43713[/TD]
[TD]H90208-163[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]11/15/13[/TD]
[TD]R[/TD]
[TD]GRAYLOC HUB,BW, 8 GR67, 8.625 OD x 6.625[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]JH43714[/TD]
[TD]H90208-164[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]11/15/13[/TD]
[TD]R[/TD]
[TD]GRAYLOC HUB,BW, 8 GR62, 8.625 OD x 6.001[/TD]
[TD]74.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Hi,

B$1 (and C$1, D$1 and E$1) is the header in Sheet03 and the same header in Sheet01. Look at my examples sheets.

So MATCH(B$1,Sheet01!$A$1:$F$1,0) find the column of the data that you need (PO #, Ship Date, Item and Item Description).

Markmzz

 

[markmzz]

The first formula is skipping some PO Line Items on sheet01. Not sure why that would be. There are rows in sheet01 that are completely blank. Maybe because of that it's not capturing everything and skipping items between columns?

For example:

[TABLE="width: 1216"]
<tbody>[TR]
[TD]PO
[/TD]
[TD]Customer Order No.
[/TD]
[TD]Job No.
[/TD]
[TD]Part Number
[/TD]
[TD]Qty Released
[/TD]
[TD]Qty Complete
[/TD]
[TD]Ship Date
[/TD]
[TD]Status
[/TD]
[TD]Description
[/TD]
[TD]PO Line 1
[/TD]
[TD]PO Line 2
[/TD]
[TD]PO Line 3
[/TD]
[TD]PO Line 4
[/TD]
[/TR]
[TR]
[TD]131
[/TD]
[TD]CH34270
[/TD]
[TD]JH43694
[/TD]
[TD]H90027-51
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]11/15/13
[/TD]
[TD]R
[/TD]
[TD]GRAYLOC SEAL RING, 23,HIGH STRENGTH, AI
[/TD]
[TD]79.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]131
[/TD]
[TD]CH34270
[/TD]
[TD]JH43696
[/TD]
[TD]H90034-91
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]12/02/13
[/TD]
[TD]R
[/TD]
[TD]GRAYLOC SEAL RING, 62,HIGH STRENGTH, AI
[/TD]
[TD]74.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]131
[/TD]
[TD]CH34270[/TD]
[TD]JH43707
[/TD]
[TD]H90133-1396
[/TD]
[TD]24
[/TD]
[TD][/TD]
[TD]11/15/13
[/TD]
[TD]R
[/TD]
[TD]GRAYLOC HUB,BW, 8 GR42, 8.625 OD x 4.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]131
[/TD]
[TD]CH34270
[/TD]
[TD]JH43713
[/TD]
[TD]H90208-163
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD]11/15/13
[/TD]
[TD]R
[/TD]
[TD]GRAYLOC HUB,BW, 8 GR67, 8.625 OD x 6.625
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]131
[/TD]
[TD]CH34270
[/TD]
[TD]JH43714
[/TD]
[TD]H90208-164
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]11/15/13
[/TD]
[TD]R
[/TD]
[TD]GRAYLOC HUB,BW, 8 GR62, 8.625 OD x 6.001
[/TD]
[TD]74.1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi,

You don't have data in the Master Data sheet for CH34270 & H90133-1396 and CH34270 & H90208-163.

So you don't have PO for that values.

Markmzz

 

[powerpuffgirl]

That's correct. I was just thinking maybe blank cells were driving skipped entries on Sheet3. If you notice, Sheet3 jumps from 72.1 to 152.1 but I have several entries on Sheet1 in between those numbers on Order CH34270.

Also, the second formula is only capturing the first item descriptions related to PO Line item

Ex Sheet3:
1.1 Seal Ring
1.1 Seal Ring
1.1 Seal Ring

When it should capture different 1.1s (there are 3 different identifiers)
1.1 Seal Ring
1.1 Clamp
1.1 Hub

In Sheet1, seal ring is listed first so I am guessing it only captures the first item description and part number?

Hi,

You don't have data in the Master Data sheet for CH34270 & H90133-1396 and CH34270 & H90208-163.

So you don't have PO for that values.

Markmzz

 

[markmzz]

That's correct. I was just thinking maybe blank cells were driving skipped entries on Sheet3. If you notice, Sheet3 jumps from 72.1 to 152.1 but I have several entries on Sheet1 in between those numbers on Order CH34270.

Also, the second formula is only capturing the first item descriptions related to PO Line item

Ex Sheet3:
1.1 Seal Ring
1.1 Seal Ring
1.1 Seal Ring

When it should capture different 1.1s (there are 3 different identifiers)
1.1 Seal Ring
1.1 Clamp
1.1 Hub

In Sheet1, seal ring is listed first so I am guessing it only captures the first item description and part number?

Look at your PM Box.

Markmzz

 

[markmzz]

Hi,

Here is the last formula:

In B2 of the sheet Sheet3 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF($A2="","",INDEX(Sheet1!$A$2:$I$286,
SMALL(IF(IF(Sheet1!$J$2:$U$286<>"",Sheet1!$J$2:$U$286+ROW(Sheet1!$J$2:$U$286)/10^7)=
SMALL(IF(Sheet1!$J$2:$U$286<>"",Sheet1!$J$2:$U$286+ROW(Sheet1!$J$2:$U$286)/10^7),ROWS($A$2:$A2)),ROW(Sheet1!$J$2:$U$286)-ROW(Sheet1!$J$2)+1),1),
MATCH(B$1,Sheet1!$A$1:$I$1,0)))

Markmzz