Extract Data From Specific Column

powerpuffgirl · Oct 18, 2013

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

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

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 · Oct 20, 2013

Thanks, I just compared this to the manually entered column and this comes very, very close. Appreciate your help.

powerpuffgirl · Oct 20, 2013

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.

markmzz · Oct 20, 2013

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.

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

powerpuffgirl · Oct 20, 2013

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

markmzz · Oct 20, 2013

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

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

markmzz · Oct 21, 2013

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

powerpuffgirl · Nov 1, 2013

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!

markmzz · Nov 2, 2013

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]

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

Could you post more information?

Markmzz

powerpuffgirl · Nov 2, 2013

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 Line	PO #	Ship Date	Item	Item Description
53.1	131	A90019-78WB	CLAMP,2PC 4BOLT, 1, AISI 4140,
55.1	131	A90148-96WB	CLAMP,2PC 4BOLT, 2, AISI 4140,
57.1	131	A90413-77WB	CLAMP,2PC 4BOLT, 2-1/2-3, AISI
61.1	131	A90019-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

markmzz · Nov 2, 2013

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

Extract Data From Specific Column

powerpuffgirl

New Member

powerpuffgirl

New Member

Excel Facts

powerpuffgirl

New Member

markmzz

MrExcel MVP

powerpuffgirl

New Member

markmzz

MrExcel MVP

markmzz

MrExcel MVP

powerpuffgirl

New Member

markmzz

MrExcel MVP

powerpuffgirl

New Member

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

Extract Data From Specific Column

New Member

New Member

Excel Facts

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock