Extract Data From Specific Column

P

powerpuffgirl

New Member
Joined
Oct 18, 2013
Messages
12
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.
 
Sort by date Sort by votes

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Markmzz,

I was playing around with formulas mentioned below but I can see some of the errors in them now. I am really not too comfortable/ familiar with index, small and match formulas:

=INDEX(Sheet2!B2:B193,MATCH(A2&C2,Sheet2!A2:A193&Sheet2!G2:G193&Sheet2!E2:E193&Sheet2!C2:C193&Sheet2!F2:F193),Sheet2!B2:B139,0)

=INDEX($A$2:$G$274&Sheet2!A2:G192,SMALL(IF(Sheet2!$A$2:$A$192=$A2&Sheet2!C2:G192=Sheet1!C2,ROW($A$2:$A$274)-1),COLUMNS($L2:L2)),Sheet2!B2:$B$192)

I had the columns set up a bit differently on some of these.

And yes, I did press Ctrl+Shift+Enter. I was retrieving results with your formula but they were an exact match of PO Line Items line by line, even with the second formula you posted.
 
Upvote 0
powerpuffgirl said:
Markmzz,

I was playing around with formulas mentioned below but I can see some of the errors in them now. I am really not too comfortable/ familiar with index, small and match formulas:

=INDEX(Sheet2!B2:B193,MATCH(A2&C2,Sheet2!A2:A193&Sheet2!G2:G193&Sheet2!E2:E193&Sheet2!C2:C193&Sheet2!F2:F193),Sheet2!B2:B139,0)

=INDEX($A$2:$G$274&Sheet2!A2:G192,SMALL(IF(Sheet2!$A$2:$A$192=$A2&Sheet2!C2:G192=Sheet1!C2,ROW($A$2:$A$274)-1),COLUMNS($L2:L2)),Sheet2!B2:$B$192)

I had the columns set up a bit differently on some of these.

And yes, I did press Ctrl+Shift+Enter. I was retrieving results with your formula but they were an exact match of PO Line Items line by line, even with the second formula you posted.
Click to expand...

Here all is ok. Look at this (with your workbook):

Layout

[TABLE="width: 892"]
<colgroup><col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;"> <col width="23" style="width: 17pt; mso-width-source: userset; mso-width-alt: 841;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="269" style="width: 202pt; mso-width-source: userset; mso-width-alt: 9837;"> <col width="20" style="width: 15pt; mso-width-source: userset; mso-width-alt: 731;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" span="9"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <tbody>[TR]
[TD="class: xl63, width: 50, bgcolor: transparent"]Order[/TD]
[TD="class: xl63, width: 23, bgcolor: transparent"]Qty[/TD]
[TD="class: xl63, width: 75, bgcolor: transparent"]Item[/TD]
[TD="class: xl63, width: 269, bgcolor: transparent"]PO Line Item Manually[/TD]
[TD="class: xl63, width: 20, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 1[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 2[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 3[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 4[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 5[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 6[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 7[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 8[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 9[/TD]
[TD="class: xl63, width: 80, bgcolor: transparent"]PO Line Item 10[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl63, bgcolor: transparent"]H90272-241[/TD]
[TD="class: xl63, bgcolor: transparent"]70.1, 81.1, 86.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]70.1[/TD]
[TD="class: xl64, bgcolor: yellow"]81.1[/TD]
[TD="class: xl64, bgcolor: yellow"]86.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]H90272-237[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]61,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]61.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90272-240[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]68.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl63, bgcolor: transparent"]H90005-702[/TD]
[TD="class: xl63, bgcolor: transparent"]69.1, 73.1, 87.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]69.1[/TD]
[TD="class: xl64, bgcolor: yellow"]73.1[/TD]
[TD="class: xl64, bgcolor: yellow"]87.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90221-109[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]63,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]63.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl63, bgcolor: transparent"]H90375-24[/TD]
[TD="class: xl63, bgcolor: transparent"]75.1, 88.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]75.1[/TD]
[TD="class: xl64, bgcolor: yellow"]88.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90374-15[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]76,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]76.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]H90068-307[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]79,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]79.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90069-232[/TD]
[TD="class: xl63, bgcolor: transparent"]83.1, 85.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]83.1[/TD]
[TD="class: xl64, bgcolor: yellow"]85.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90022-64[/TD]
[TD="class: xl63, bgcolor: transparent"]61.1, 82.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]61.1[/TD]
[TD="class: xl64, bgcolor: yellow"]82.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90025-105[/TD]
[TD="class: xl63, bgcolor: transparent"]55.1, 57.1, 77.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]55.1[/TD]
[TD="class: xl64, bgcolor: yellow"]57.1[/TD]
[TD="class: xl64, bgcolor: yellow"]77.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]53.1, 61.1, 68.1, 69.1, 70.1, 73.1, 81.1, 82.1, 86.1, 87.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]53.1[/TD]
[TD="class: xl64, bgcolor: yellow"]61.1[/TD]
[TD="class: xl64, bgcolor: yellow"]68.1[/TD]
[TD="class: xl64, bgcolor: yellow"]69.1[/TD]
[TD="class: xl64, bgcolor: yellow"]70.1[/TD]
[TD="class: xl64, bgcolor: yellow"]73.1[/TD]
[TD="class: xl64, bgcolor: yellow"]81.1[/TD]
[TD="class: xl64, bgcolor: yellow"]82.1[/TD]
[TD="class: xl64, bgcolor: yellow"]86.1[/TD]
[TD="class: xl64, bgcolor: yellow"]87.1[/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]55.1, 65.1, 75.1, 88.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]55.1[/TD]
[TD="class: xl64, bgcolor: yellow"]65.1[/TD]
[TD="class: xl64, bgcolor: yellow"]75.1[/TD]
[TD="class: xl64, bgcolor: yellow"]88.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]57.1, 67.1, 76.1, 79.1, 80.1, 84.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]57.1[/TD]
[TD="class: xl64, bgcolor: yellow"]67.1[/TD]
[TD="class: xl64, bgcolor: yellow"]76.1[/TD]
[TD="class: xl64, bgcolor: yellow"]79.1[/TD]
[TD="class: xl64, bgcolor: yellow"]80.1[/TD]
[TD="class: xl64, bgcolor: yellow"]84.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]77.1, 83.1, 85.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]77.1[/TD]
[TD="class: xl64, bgcolor: yellow"]83.1[/TD]
[TD="class: xl64, bgcolor: yellow"]85.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]71.1, 72.1, 74.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]71.1[/TD]
[TD="class: xl64, bgcolor: yellow"]72.1[/TD]
[TD="class: xl64, bgcolor: yellow"]74.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]63.1, 66.1, 78.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]63.1[/TD]
[TD="class: xl64, bgcolor: yellow"]66.1[/TD]
[TD="class: xl64, bgcolor: yellow"]78.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl63, bgcolor: transparent"]H90133-1396[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90374-14[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]H90068-311[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]80,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]80.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90208-164[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]74,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]74.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]H90208-163[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl63, bgcolor: transparent"]H90021-37[/TD]
[TD="class: xl63, bgcolor: transparent"]69.1,70.1, 73.1, 81.1, 86.1, 87.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]69.1[/TD]
[TD="class: xl64, bgcolor: yellow"]70.1[/TD]
[TD="class: xl64, bgcolor: yellow"]73.1[/TD]
[TD="class: xl64, bgcolor: yellow"]81.1[/TD]
[TD="class: xl64, bgcolor: yellow"]86.1[/TD]
[TD="class: xl64, bgcolor: yellow"]87.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90022-66[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]68.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl63, bgcolor: transparent"]H90023-73[/TD]
[TD="class: xl63, bgcolor: transparent"]75.1, 88.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]75.1[/TD]
[TD="class: xl64, bgcolor: yellow"]88.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90025-107[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]76,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]76.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90027-51[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]79,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]79.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90174-101[/TD]
[TD="class: xl63, bgcolor: transparent"]65.1, 80.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]65.1[/TD]
[TD="class: xl64, bgcolor: yellow"]80.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]H90030-66[/TD]
[TD="class: xl63, bgcolor: transparent"]83.1, 85.1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]83.1[/TD]
[TD="class: xl64, bgcolor: yellow"]85.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90643-55[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]66,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: yellow"]66.1[/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: yellow"] [/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]
[/TR]
</tbody>[/TABLE]

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code: 
In J2

=IFERROR(INDEX(Sheet2!$B$2:$B$194,SMALL(IF(Sheet2!$A$2:$A$194&Sheet2!$C$2:$F$194=$A2&$C2,
ROW(Sheet2!$A$2:$A$194)-ROW(Sheet2!$A$2)+1),COLUMNS($J2:J2))),"")

Markmzz
 
Last edited:
Upvote 0
Got it. Had to use fn+ F2 and then Ctrl+Shift+Enter on a Mac. Thanks!

markmzz said:
Here all is ok. Look at this (with your workbook):

Layout

[TABLE="width: 892"]
<tbody>[TR]
[TD="class: xl63, width: 50, bgcolor: transparent"]Order[/TD]
[TD="class: xl63, width: 23, bgcolor: transparent"]Qty[/TD]
[TD="class: xl63, width: 75, bgcolor: transparent"]Item[/TD]
[TD="class: xl63, width: 269, bgcolor: transparent"]PO Line Item Manually[/TD]
[TD="class: xl63, width: 20, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 1[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 2[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 3[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 4[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 5[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 6[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 7[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 8[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]PO Line Item 9[/TD]
[TD="class: xl63, width: 80, bgcolor: transparent"]PO Line Item 10[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl63, bgcolor: transparent"]H90272-241[/TD]
[TD="class: xl63, bgcolor: transparent"]70.1, 81.1, 86.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]70.1[/TD]
[TD="class: xl64, bgcolor: yellow"]81.1[/TD]
[TD="class: xl64, bgcolor: yellow"]86.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]H90272-237[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]61,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]61.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90272-240[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]68.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl63, bgcolor: transparent"]H90005-702[/TD]
[TD="class: xl63, bgcolor: transparent"]69.1, 73.1, 87.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]69.1[/TD]
[TD="class: xl64, bgcolor: yellow"]73.1[/TD]
[TD="class: xl64, bgcolor: yellow"]87.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90221-109[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]63,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]63.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl63, bgcolor: transparent"]H90375-24[/TD]
[TD="class: xl63, bgcolor: transparent"]75.1, 88.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]75.1[/TD]
[TD="class: xl64, bgcolor: yellow"]88.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90374-15[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]76,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]76.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]H90068-307[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]79,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]79.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90069-232[/TD]
[TD="class: xl63, bgcolor: transparent"]83.1, 85.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]83.1[/TD]
[TD="class: xl64, bgcolor: yellow"]85.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90022-64[/TD]
[TD="class: xl63, bgcolor: transparent"]61.1, 82.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]61.1[/TD]
[TD="class: xl64, bgcolor: yellow"]82.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90025-105[/TD]
[TD="class: xl63, bgcolor: transparent"]55.1, 57.1, 77.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]55.1[/TD]
[TD="class: xl64, bgcolor: yellow"]57.1[/TD]
[TD="class: xl64, bgcolor: yellow"]77.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]53.1, 61.1, 68.1, 69.1, 70.1, 73.1, 81.1, 82.1, 86.1, 87.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]53.1[/TD]
[TD="class: xl64, bgcolor: yellow"]61.1[/TD]
[TD="class: xl64, bgcolor: yellow"]68.1[/TD]
[TD="class: xl64, bgcolor: yellow"]69.1[/TD]
[TD="class: xl64, bgcolor: yellow"]70.1[/TD]
[TD="class: xl64, bgcolor: yellow"]73.1[/TD]
[TD="class: xl64, bgcolor: yellow"]81.1[/TD]
[TD="class: xl64, bgcolor: yellow"]82.1[/TD]
[TD="class: xl64, bgcolor: yellow"]86.1[/TD]
[TD="class: xl64, bgcolor: yellow"]87.1[/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]55.1, 65.1, 75.1, 88.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]55.1[/TD]
[TD="class: xl64, bgcolor: yellow"]65.1[/TD]
[TD="class: xl64, bgcolor: yellow"]75.1[/TD]
[TD="class: xl64, bgcolor: yellow"]88.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]57.1, 67.1, 76.1, 79.1, 80.1, 84.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]57.1[/TD]
[TD="class: xl64, bgcolor: yellow"]67.1[/TD]
[TD="class: xl64, bgcolor: yellow"]76.1[/TD]
[TD="class: xl64, bgcolor: yellow"]79.1[/TD]
[TD="class: xl64, bgcolor: yellow"]80.1[/TD]
[TD="class: xl64, bgcolor: yellow"]84.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]77.1, 83.1, 85.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]77.1[/TD]
[TD="class: xl64, bgcolor: yellow"]83.1[/TD]
[TD="class: xl64, bgcolor: yellow"]85.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]71.1, 72.1, 74.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]71.1[/TD]
[TD="class: xl64, bgcolor: yellow"]72.1[/TD]
[TD="class: xl64, bgcolor: yellow"]74.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[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: xl63, bgcolor: transparent"]63.1, 66.1, 78.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]63.1[/TD]
[TD="class: xl64, bgcolor: yellow"]66.1[/TD]
[TD="class: xl64, bgcolor: yellow"]78.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl63, bgcolor: transparent"]H90133-1396[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90374-14[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]H90068-311[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]80,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]80.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90208-164[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]74,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]74.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]H90208-163[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl63, bgcolor: transparent"]H90021-37[/TD]
[TD="class: xl63, bgcolor: transparent"]69.1,70.1, 73.1, 81.1, 86.1, 87.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]69.1[/TD]
[TD="class: xl64, bgcolor: yellow"]70.1[/TD]
[TD="class: xl64, bgcolor: yellow"]73.1[/TD]
[TD="class: xl64, bgcolor: yellow"]81.1[/TD]
[TD="class: xl64, bgcolor: yellow"]86.1[/TD]
[TD="class: xl64, bgcolor: yellow"]87.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90022-66[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]68.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl63, bgcolor: transparent"]H90023-73[/TD]
[TD="class: xl63, bgcolor: transparent"]75.1, 88.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]75.1[/TD]
[TD="class: xl64, bgcolor: yellow"]88.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]H90025-107[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]76,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]76.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90027-51[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]79,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]79.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90174-101[/TD]
[TD="class: xl63, bgcolor: transparent"]65.1, 80.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]65.1[/TD]
[TD="class: xl64, bgcolor: yellow"]80.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]H90030-66[/TD]
[TD="class: xl63, bgcolor: transparent"]83.1, 85.1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]83.1[/TD]
[TD="class: xl64, bgcolor: yellow"]85.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34270[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]H90643-55[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]66,1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: yellow"]66.1[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/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]
[/TR]
</tbody>[/TABLE]

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code: 
In J2

=IFERROR(INDEX(Sheet2!$B$2:$B$194,SMALL(IF(Sheet2!$A$2:$A$194&Sheet2!$C$2:$F$194=$A2&$C2,
ROW(Sheet2!$A$2:$A$194)-ROW(Sheet2!$A$2)+1),COLUMNS($J2:J2))),"")

Markmzz
Click to expand...
 
Upvote 0
powerpuffgirl said:
Got it. Had to use fn+ F2 and then Ctrl+Shift+Enter on a Mac. Thanks!
Click to expand...

You are welcome.

By the way, if you want all PO Line's in one cell, try this:

Code: 
Public Function ConcatPO(ByRef myRng As Variant, _
                        Optional ByVal myDelim As String = ", ") As String
    Application.Volatile
    Dim myArray As Variant, i As Long, j As Long
    myArray = myRng
    For j = LBound(myArray, 1) To UBound(myArray, 1)
        For i = LBound(myArray, 2) To UBound(myArray, 2)
            If myArray(j, i) <> "" And myArray(j, i) <> 0 Then
              ConcatPO = ConcatPO & myArray(j, i) & myDelim
            End If
        Next i
    Next j
    If Len(ConcatPO) Then
        ConcatPO = Left(ConcatPO, Len(ConcatPO) - Len(myDelim))
    End If
End Function

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code: 
In J2

=ConcatPO(IF(Sheet2!$A$2:$A$194&Sheet2!$C$2:$F$194=$A2&$C2,Sheet2!$B$2:$B$194,""))

Markmzz
 
Last edited:
Upvote 0
A small modification in my last code and formula:

Code: 
Public Function ConcatPO(ByRef myRng1 As Range, ByRef myRng2 As Variant, _
                        Optional ByVal myDelim As String = ", ") As String
    Application.Volatile
    Dim myArray1 As Variant, myArray2 As Variant, i As Long
    myArray1 = myRng1
    myArray2 = myRng2
    For i = LBound(myArray2, 1) To UBound(myArray2, 1)
        If WorksheetFunction.Small(myArray2, i) < 8 ^ 7 Then
            ConcatPO = ConcatPO & myArray1(WorksheetFunction.Small(myArray2, i), 1) & myDelim
        Else
            Exit For
        End If
    Next i
    If Len(ConcatPO) Then
        ConcatPO = Left(ConcatPO, Len(ConcatPO) - Len(myDelim))
    End If
End Function

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code: 
In J2

=IF($A2="","",ConcatPO(Sheet2!$B$2:$B$194,
IF(Sheet2!$A$2:$A$194&Sheet2!$C$2:$F$194=$A2&$C2,ROW(Sheet2!$B$2:$B$194)-ROW(Sheet2!$B$2)+1,8^7)))

Markmzz
 
Last edited:
Upvote 0
So I have another question on the same file. I would like to arrange all the items in PO Line 1 to PO Line 10 in ascending order on another tab and capture the ship date and Order number next to it.

Let's say ship date is in column G and Order is column A.

I used the following formula that was posted:

=IFERROR(INDEX(Sheet2!$B$2:$B$194,SMALL(IF(Sheet2!$A$2:$A$194&Sheet2!$C$2:$F$194=$A2&$C2,
ROW(Sheet2!$A$2:$A$194)-ROW(Sheet2!$A$2)+1),COLUMNS($J2:J2))),"")

I want this new tab to look something like this:

[TABLE="width: 228"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]PO Line [/TD]
[TD]Order[/TD]
[TD]Ship Date[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]CH34270[/TD]
[TD]11/08/13[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]CH34270[/TD]
[TD]11/15/13[/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]CH34270[/TD]
[TD]11/08/13[/TD]
[/TR]
[TR]
[TD]4.1[/TD]
[TD]CH34270[/TD]
[TD]11/15/13[/TD]
[/TR]
</tbody>[/TABLE]

powerpuffgirl said:
Got it. Had to use fn+ F2 and then Ctrl+Shift+Enter on a Mac. Thanks!
Click to expand...
 
Upvote 0
powerpuffgirl said:
So I have another question on the same file. I would like to arrange all the items in PO Line 1 to PO Line 10 in ascending order on another tab and capture the ship date and Order number next to it.

Let's say ship date is in column G and Order is column A.

I used the following formula that was posted:

=IFERROR(INDEX(Sheet2!$B$2:$B$194,SMALL(IF(Sheet2!$A$2:$A$194&Sheet2!$C$2:$F$194=$A2&$C2,
ROW(Sheet2!$A$2:$A$194)-ROW(Sheet2!$A$2)+1),COLUMNS($J2:J2))),"")

I want this new tab to look something like this:

[TABLE="width: 228"]
<tbody>[TR]
[TD]PO Line
[/TD]
[TD]Order
[/TD]
[TD]Ship Date
[/TD]
[/TR]
[TR]
[TD]1.1
[/TD]
[TD]CH34270
[/TD]
[TD]11/08/13
[/TD]
[/TR]
[TR]
[TD]2.1
[/TD]
[TD]CH34270
[/TD]
[TD]11/15/13
[/TD]
[/TR]
[TR]
[TD]3.1
[/TD]
[TD]CH34270
[/TD]
[TD]11/08/13
[/TD]
[/TR]
[TR]
[TD]4.1
[/TD]
[TD]CH34270
[/TD]
[TD]11/15/13
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Sorry, but I didn't understand what you want.

Could you post more information?

Markmzz
 
Upvote 0
Here is how sheet1 looks like right now

[TABLE="width: 1216"]
<colgroup><col><col><col><col><col><col><col span="9"><col></colgroup><tbody>[TR]
[TD]PO #[/TD]
[TD]Order[/TD]
[TD]Qty[/TD]
[TD]Item[/TD]
[TD]Ship Date[/TD]
[TD] Item Description[/TD]
[TD]PO Line 1[/TD]
[TD]PO Line 2[/TD]
[TD]PO Line 3[/TD]
[TD]PO Line 4[/TD]
[TD]PO Line 5[/TD]
[TD]PO Line 6[/TD]
[TD]PO Line 7[/TD]
[TD]PO Line 8[/TD]
[TD]PO Line 9[/TD]
[TD]PO Line 10[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]31[/TD]
[TD]A90019-78WB[/TD]
[TD="align: right"]4-Nov[/TD]
[TD] CLAMP,2PC 4BOLT, 1, AISI 4140,[/TD]
[TD]53.1[/TD]
[TD]61.1[/TD]
[TD]68.1[/TD]
[TD]69.1[/TD]
[TD]70.1[/TD]
[TD]73.1[/TD]
[TD]81.1[/TD]
[TD]82.1[/TD]
[TD]86.1[/TD]
[TD]87.1[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]14[/TD]
[TD]A90148-96WB[/TD]
[TD="align: right"]18-Nov[/TD]
[TD] CLAMP,2PC 4BOLT, 2, AISI 4140,[/TD]
[TD]55.1[/TD]
[TD]65.1[/TD]
[TD]75.1[/TD]
[TD]88.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]15[/TD]
[TD]A90413-77WB[/TD]
[TD="align: right"]22-Nov[/TD]
[TD] CLAMP,2PC 4BOLT, 2-1/2- 3, AISI[/TD]
[TD]57.1[/TD]
[TD]67.1[/TD]
[TD]76.1[/TD]
[TD]79.1[/TD]
[TD]80.1[/TD]
[TD]84.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]2[/TD]
[TD]H90235-45WB[/TD]
[TD="align: right"]4-Nov[/TD]
[TD] CLAMP,2PC 4BOLT, 4, AISI 4140,[/TD]
[TD]77.1[/TD]
[TD]83.1[/TD]
[TD]85.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]20[/TD]
[TD]H90329-35WB[/TD]
[TD="align: right"]5-Nov[/TD]
[TD] CLAMP,2PC 4BOLT, 8, AISI 4140,[/TD]
[TD]71.1[/TD]
[TD]72.1[/TD]
[TD]74.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]CH34270[/TD]
[TD]2[/TD]
[TD]H90314-36WB[/TD]
[TD="align: right"]7-Nov[/TD]
[TD] CLAMP,2PC 4BOLT,10H, AISI 4140,[/TD]
[TD]63.1[/TD]
[TD]66.1[/TD]
[TD]78.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

This is how I would like sheet3 to look like. Main thing I want to capture is to lay out the PO line column in ascending order from sheet1 and capture correlating information.

PO LinePO #Ship Date Item Item Description
53.1131 A90019-78WB CLAMP,2PC 4BOLT, 1, AISI 4140,
55.1131 A90148-96WB CLAMP,2PC 4BOLT, 2, AISI 4140,
57.1131 A90413-77WB CLAMP,2PC 4BOLT, 2-1/2-3, AISI
61.1131A90019-78WB CLAMP,2PC 4BOLT, 1, AISI 4140,

<colgroup><col><col><col><col><col></colgroup><tbody>
[TD="align: right"]4-Nov[/TD]

[TD="align: right"]18-Nov[/TD]

[TD="align: right"]22-Nov[/TD]

[TD="align: right"]4-Nov [/TD]

</tbody>

markmzz said:
Sorry, but I didn't understand what you want.

Could you post more information?

Markmzz
Click to expand...
 
Upvote 0
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).

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Sum the values from the column for a specific period which matches values from another column corresponding to that specific period
Replies
2
Views
253
thetnaingsoe
T
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
300
mumps
mumps
F
  • Solved
Listing All corresponding dates for a specific value
Replies
3
Views
338
Fluff
Fluff
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
925
Peter_SSs
Peter_SSs
T
  • Question Question
Pull data from every 5th column based on dropdown selection
Replies
4
Views
252
Twinky
T

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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