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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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

OrderQtyItemPO Line Item Manually PO Line Item 1PO Line Item 2PO Line Item 3PO Line Item 4PO Line Item 5PO Line Item 6PO Line Item 7PO Line Item 8PO Line Item 9PO Line Item 10
CH3427013H90272-24170.1, 81.1, 86.1 70.181.186.1
CH342702H90272-23761,1 61.1
CH342703H90272-24068,1 68.1
CH3427012H90005-70269.1, 73.1, 87.1 69.173.187.1
CH342704H90221-10963,1 63.1
CH3427014H90375-2475.1, 88.1 75.188.1
CH342704H90374-1576,1 76.1
CH342708H90068-30779,1 79.1
CH342704H90069-23283.1, 85.1 83.185.1
CH342703H90022-6461.1, 82.1 61.182.1
CH342704H90025-10555.1, 57.1, 77.1 55.157.177.1
CH3427031A90019-78WB53.1, 61.1, 68.1, 69.1, 70.1, 73.1, 81.1, 82.1, 86.1, 87.1 53.161.168.169.170.173.181.182.186.187.1
CH3427014A90148-96WB55.1, 65.1, 75.1, 88.1 55.165.175.188.1
CH3427015A90413-77WB57.1, 67.1, 76.1, 79.1, 80.1, 84.1 57.167.176.179.180.184.1
CH342702H90235-45WB77.1, 83.1, 85.1 77.183.185.1
CH3427020H90329-35WB71.1, 72.1, 74.1 71.172.174.1
CH342702H90314-36WB63.1, 66.1, 78.1 63.166.178.1
CH3427024H90133-1396
CH342704H90374-14
CH342708H90068-31180,1 80.1
CH342704H90208-16474,1 74.1
CH342708H90208-163
CH3427025H90021-3769.1,70.1, 73.1, 81.1, 86.1, 87.1 69.170.173.181.186.187.1
CH342703H90022-6668,1 68.1
CH3427014H90023-7375.1, 88.1 75.188.1
CH342703H90025-10776,1 76.1
CH342704H90027-5179,1 79.1
CH342704H90174-10165.1, 80.1 65.180.1
CH342702H90030-6683.1, 85.1 83.185.1
CH342704H90643-5566,1 66.1
******************************************************************************************************************************************************************************************************************************

<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>
</tbody>

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

OrderQtyItemPO Line Item ManuallyPO Line Item 1PO Line Item 2PO Line Item 3PO Line Item 4PO Line Item 5PO Line Item 6PO Line Item 7PO Line Item 8PO Line Item 9PO Line Item 10
CH3427013H90272-24170.1, 81.1, 86.170.181.186.1
CH342702H90272-23761,161.1
CH342703H90272-24068,168.1
CH3427012H90005-70269.1, 73.1, 87.169.173.187.1
CH342704H90221-10963,163.1
CH3427014H90375-2475.1, 88.175.188.1
CH342704H90374-1576,176.1
CH342708H90068-30779,179.1
CH342704H90069-23283.1, 85.183.185.1
CH342703H90022-6461.1, 82.161.182.1
CH342704H90025-10555.1, 57.1, 77.155.157.177.1
CH3427031A90019-78WB53.1, 61.1, 68.1, 69.1, 70.1, 73.1, 81.1, 82.1, 86.1, 87.153.161.168.169.170.173.181.182.186.187.1
CH3427014A90148-96WB55.1, 65.1, 75.1, 88.155.165.175.188.1
CH3427015A90413-77WB57.1, 67.1, 76.1, 79.1, 80.1, 84.157.167.176.179.180.184.1
CH342702H90235-45WB77.1, 83.1, 85.177.183.185.1
CH3427020H90329-35WB71.1, 72.1, 74.171.172.174.1
CH342702H90314-36WB63.1, 66.1, 78.163.166.178.1
CH3427024H90133-1396
CH342704H90374-14
CH342708H90068-31180,180.1
CH342704H90208-16474,174.1
CH342708H90208-163
CH3427025H90021-3769.1,70.1, 73.1, 81.1, 86.1, 87.169.170.173.181.186.187.1
CH342703H90022-6668,168.1
CH3427014H90023-7375.1, 88.175.188.1
CH342703H90025-10776,176.1
CH342704H90027-5179,179.1
CH342704H90174-10165.1, 80.165.180.1
CH342702H90030-6683.1, 85.183.185.1
CH342704H90643-5566,166.1
******************************************************************************************************************************************************************************************************************************

<tbody>
</tbody>

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:

PO Line OrderShip Date
1.1CH3427011/08/13
2.1CH3427011/15/13
3.1CH3427011/08/13
4.1CH3427011/15/13

<colgroup><col span="3"></colgroup><tbody>
</tbody>

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:

PO Line
Order
Ship Date
1.1
CH34270
11/08/13
2.1
CH34270
11/15/13
3.1
CH34270
11/08/13
4.1
CH34270
11/15/13

<tbody>
</tbody>
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

PO #OrderQtyItemShip Date Item DescriptionPO Line 1PO Line 2PO Line 3PO Line 4PO Line 5PO Line 6PO Line 7PO Line 8PO Line 9PO Line 10
131CH3427031A90019-78WB4-Nov CLAMP,2PC 4BOLT, 1, AISI 4140,53.161.168.169.170.173.181.182.186.187.1
131CH3427014A90148-96WB18-Nov CLAMP,2PC 4BOLT, 2, AISI 4140,55.165.175.188.1
131CH3427015A90413-77WB22-Nov CLAMP,2PC 4BOLT, 2-1/2- 3, AISI57.167.176.179.180.184.1
131CH342702H90235-45WB4-Nov CLAMP,2PC 4BOLT, 4, AISI 4140,77.183.185.1
131CH3427020H90329-35WB5-Nov CLAMP,2PC 4BOLT, 8, AISI 4140,71.172.174.1
131CH342702H90314-36WB7-Nov CLAMP,2PC 4BOLT,10H, AISI 4140,63.166.178.1

<colgroup><col><col><col><col><col><col><col span="9"><col></colgroup><tbody>
</tbody>

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.11314-Nov A90019-78WB CLAMP,2PC 4BOLT, 1, AISI 4140,
55.113118-Nov A90148-96WB CLAMP,2PC 4BOLT, 2, AISI 4140,
57.113122-Nov A90413-77WB CLAMP,2PC 4BOLT, 2-1/2-3, AISI
61.11314-Nov A90019-78WB CLAMP,2PC 4BOLT, 1, AISI 4140,

<colgroup><col><col><col><col><col></colgroup><tbody>
</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

PO #
Order
Qty
Item
Ship Date
Item Description
PO Line 1
PO Line 2
PO Line 3
PO Line 4
PO Line 5
PO Line 6
PO Line 7
PO Line 8
PO Line 9
PO Line 10
Sheet01
131
CH34270
31
A90019-78WB
04/nov
CLAMP,2PC 4BOLT, 1, AISI 4140,
53,1
61,1
68,1
69,1
70,1
73,1
81,1
82,1
86,1
87,1
131
CH34270
14
A90148-96WB
18/nov
CLAMP,2PC 4BOLT, 2, AISI 4140,
55,1
65,1
75,1
88,1
131
CH34270
15
A90413-77WB
22/nov
CLAMP,2PC 4BOLT, 2-1/2- 3, AISI
57,1
67,1
76,1
79,1
80,1
84,1
131
CH34270
2
H90235-45WB
04/nov
CLAMP,2PC 4BOLT, 4, AISI 4140,
77,1
83,1
85,1
131
CH34270
20
H90329-35WB
05/nov
CLAMP,2PC 4BOLT, 8, AISI 4140,
71,1
72,1
74,1
131
CH34270
2
H90314-36WB
07/nov
CLAMP,2PC 4BOLT,10H, AISI 4140,
63,1
66,1
78,1
****
*********
****
**************
**********
********************************
*********
*********
*********
*********
*********
*********
*********
*********
*********
***********

<tbody>
</tbody>

PO Line
PO #
Ship Date
Item
Item Description
Sheet03
53,1
131
04/nov
A90019-78WB
CLAMP,2PC 4BOLT, 1, AISI 4140,
55,1
131
18/nov
A90148-96WB
CLAMP,2PC 4BOLT, 2, AISI 4140,
57,1
131
22/nov
A90413-77WB
CLAMP,2PC 4BOLT, 2-1/2- 3, AISI
61,1
131
04/nov
A90019-78WB
CLAMP,2PC 4BOLT, 1, AISI 4140,
63,1
131
07/nov
H90314-36WB
CLAMP,2PC 4BOLT,10H, AISI 4140,
65,1
131
18/nov
A90148-96WB
CLAMP,2PC 4BOLT, 2, AISI 4140,
*******
*********
**********
**************
********************************

<tbody>
</tbody>


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
Sum the values from the column for a specific period which matches values from another column corresponding to that specific period
Replies
2
Views
172
thetnaingsoe
T
F
  • Solved
Listing All corresponding dates for a specific value
Replies
3
Views
195
Fluff
Fluff
X
  • Question
VBA code to move data from Pivot Table to unhidden columns on a different sheet?
Replies
0
Views
151
xxbc320xx
X
J
  • Question
Sum data in a range based on a column and row and an equation
Replies
1
Views
168
bobaol
B
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
138
JMC57
J

Forum statistics

Threads
1,215,463
Messages
6,124,965
Members
449,201
Latest member
Jamil ahmed

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