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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
See if this will work.
Code: 
Sub poChase()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, po As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
    For Each c In rng
        Set po = sh2.Range("A:A").Find(c.Value, LookIn:=xlValues)
            If Not po Is Nothing Then
                fAdr = po.Address
                Do
                    Select Case Left(c.Offset(0, 2).Value, 1)
                        Case "H"
                            If c.Offset(0, 2).Value = po.Offset(0, 2).Value Then
                                c.Offset(0, 4) = po.Offset(0, 1).Value
                                Exit Do
                            End If
                        Case "A"
                            If c.Offset(0, 2).Value = po.Offset(0, 3).Value Then
                                c.Offset(0, 4) = po.Offset(0, 1).Value
                                Exit Do
                            End If
                        Case "S"
                            If c.Offset(0, 2).Value = po.Offset(0, 4).Value Then
                                c.Offset(0, 4) = po.Offset(0, 1).Value
                                Exit Do
                            End If
                        Case "B"
                            If c.Offset(0, 2).Value = po.Offset(0, 5).Value Then
                                c.Offset(0, 4) = po.Offset(0, 1).Value
                                Exit Do
                            End If
                    End Select
                    po.Value = c.Value
                    Set po = sh2.Range("A:A").FindNext(po)
                Loop While po.Address <> fAdr
            End If
    Next
End Sub
 
Upvote 0
Ran the code but no luck. This is what sheet 1 should look like after running the code. As you can see, I wrote out each PO Line Item next to the ship date in flatten data tab (sheet 1). The first line suggests that there are 3 PO line items for part number H90272-241 on Order CH77270.

[TABLE="width: 382"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Qty[/TD]
[TD]Item[/TD]
[TD]Ship Date[/TD]
[TD]PO Line Item[/TD]
[/TR]
[TR]
[TD]CH77270[/TD]
[TD]13[/TD]
[TD]H90272-241[/TD]
[TD]25-Oct[/TD]
[TD]70.1, 81.1, 86.1[/TD]
[/TR]
[TR]
[TD]CH77270[/TD]
[TD]2[/TD]
[TD]H90272-237[/TD]
[TD]25-Oct[/TD]
[TD]61.1[/TD]
[/TR]
[TR]
[TD]CH77270[/TD]
[TD]3[/TD]
[TD]H90272-240[/TD]
[TD]25-Oct[/TD]
[TD]68.1[/TD]
[/TR]
[TR]
[TD]CH77270[/TD]
[TD]12[/TD]
[TD]H90005-702[/TD]
[TD]25-Oct[/TD]
[TD]69.1, 73.1, 87.1[/TD]
[/TR]
[TR]
[TD]CH84427[/TD]
[TD]4[/TD]
[TD]H90221-109[/TD]
[TD]25-Oct[/TD]
[TD]63.1[/TD]
[/TR]
[TR]
[TD]CH84270[/TD]
[TD]14[/TD]
[TD]H90375-24[/TD]
[TD]25-Oct[/TD]
[TD]75.1, 88.1[/TD]
[/TR]
[TR]
[TD]CH34270[/TD]
[TD]4[/TD]
[TD]H90374-15[/TD]
[TD]25-Oct[/TD]
[TD]76.1[/TD]
[/TR]
[TR]
[TD]CH34270[/TD]
[TD]8[/TD]
[TD]H90068-307[/TD]
[TD]25-Oct[/TD]
[TD]79.1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 382"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I didn't see that the PO would have duplicate part numbers for different item numbers in the OP. However, this modified code should now accomodate them.
Code: 
Sub poChase2()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, po As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
    For Each c In rng
        Set po = sh2.Range("A:A").Find(c.Value, LookIn:=xlValues)
            If Not po Is Nothing Then
                fAdr = po.Address
                Do
                    Select Case Left(c.Offset(0, 2).Value, 1)
                        Case "H"
                            If c.Offset(0, 4) = "" Then
                                If c.Offset(0, 2).Value = po.Offset(0, 2).Value Then
                                    c.Offset(0, 4) = po.Offset(0, 1).Value
                                End If
                            Else
                                If c.Offset(0, 2).Value = po.Offset(0, 2).Value Then
                                    c.Offset(0, 4) = c.Offset(0, 4).Value _
                                    & ", " & po.Offset(0, 1).Value
                                End If
                            End If
                        Case "A"
                            If c.Offset(0, 4) = "" Then
                                If c.Offset(0, 2).Value = po.Offset(0, 3).Value Then
                                    c.Offset(0, 4) = po.Offset(0, 1).Value
                                End If
                            Else
                                If c.Offset(0, 2).Value = po.Offset(0, 3).Value Then
                                    c.Offset(0, 4) = c.Offset(0, 4).Value _
                                    & ", " & po.Offset(0, 1).Value
                                End If
                            End If
                        Case "S"
                            If c.Offset(0, 4) = "" Then
                                If c.Offset(0, 2).Value = po.Offset(0, 4).Value Then
                                    c.Offset(0, 4) = po.Offset(0, 1).Value
                                End If
                            Else
                                If c.Offset(0, 2).Value = po.Offset(0, 4).Value Then
                                    c.Offset(0, 4) = c.Offset(0, 4).Value _
                                    & ", " & po.Offset(0, 1).Value
                                End If
                            End If
                        Case "B"
                            If c.Offset(0, 4) = "" Then
                                If c.Offset(0, 2).Value = po.Offset(0, 5).Value Then
                                    c.Offset(0, 4) = po.Offset(0, 1).Value
                                End If
                            Else
                                If c.Offset(0, 2).Value = po.Offset(0, 5).Value Then
                                    c.Offset(0, 4) = c.Offset(0, 4).Value _
                                    & ", " & po.Offset(0, 1).Value
                                End If
                            End If
                    End Select
                    po.Value = c.Value
                    Set po = sh2.Range("A:A").FindNext(po)
                Loop While po.Address <> fAdr
            End If
    Next
End Sub
 
Upvote 0
Thanks! The code is working but only on certain cells. Not sure why.

On those certain cells, first results, however, are always a combination of date/ time? Ex: 3/9/1900 2:24:00 AM, 73.1, 87.1

This line should actually read: 69.1, 73.1, 87.1

The code ran only on a certain percentage of the cells. I had finished some cells manually and know what they should look like, but the results didn't get populated at all on those.
 
Upvote 0
Maybe this:

Layout

[TABLE="width: 534"]
<tbody>[TR]
[TD="class: xl63, width: 82, bgcolor: transparent"]Customer Order
[/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"]Qty
[/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"]Part Number
[/TD]
[TD="class: xl63, width: 62, bgcolor: transparent"]Due Date
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 1
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 2
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 3
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 4
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 5
[/TD]
[TD="class: xl65, width: 47, bgcolor: transparent"]Sheet1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778400
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]nov/04
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]131
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]155
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]156
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]157
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34895
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent"]H363839
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]dez/04
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]225
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH07478
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR73738
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]jan/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]145
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl63, bgcolor: transparent"]B56378
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]fev/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]155
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/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]
[/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]
[/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]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Customer Order
[/TD]
[TD="class: xl63, bgcolor: transparent"]PO Line Item
[/TD]
[TD="class: xl63, bgcolor: transparent"]PN Hubs
[/TD]
[TD="class: xl63, bgcolor: transparent"]PN Flanges
[/TD]
[TD="class: xl63, bgcolor: transparent"]PN Seal Rings
[/TD]
[TD="class: xl63, bgcolor: transparent"]PN Bolts
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sheet2
[/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"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]131
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778400
[/TD]
[TD="class: xl63, bgcolor: transparent"]A73973
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR38377
[/TD]
[TD="class: xl63, bgcolor: transparent"]B38388
[/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"]CH34895
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]225
[/TD]
[TD="class: xl63, bgcolor: transparent"]H363839
[/TD]
[TD="class: xl63, bgcolor: transparent"]A77489
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR47488
[/TD]
[TD="class: xl63, bgcolor: transparent"]B39847
[/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"]CH07478
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]145
[/TD]
[TD="class: xl63, bgcolor: transparent"]H476567
[/TD]
[TD="class: xl63, bgcolor: transparent"]A36378
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR73738
[/TD]
[TD="class: xl63, bgcolor: transparent"]B74466
[/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"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]155
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778400
[/TD]
[TD="class: xl63, bgcolor: transparent"]A87664
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR64747
[/TD]
[TD="class: xl63, bgcolor: transparent"]B56378
[/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"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]156
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778400
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778401
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778402
[/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"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]157
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778400
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778404
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778405
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778406
[/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]
[/TR]
</tbody>[/TABLE]

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

Code: 
In E2

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

Or

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


Markmzz
 
Last edited:
Upvote 0
That layout should work fine for me but the formulas are not working for me. First one doesn't give me anything and the second one just starts listing sheet2 PO Line column after confirming Customer Order I believe:

[TABLE="width: 203"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]second formula sheet1 column E[/TD]
[TD]original sheet2 column b (PO Line Items)[/TD]
[/TR]
[TR]
[TD]53.1[/TD]
[TD]53.1[/TD]
[/TR]
[TR]
[TD]55.1[/TD]
[TD]55.1[/TD]
[/TR]
[TR]
[TD]57.1[/TD]
[TD]57.1[/TD]
[/TR]
[TR]
[TD]61.1[/TD]
[TD]61.1[/TD]
[/TR]
[TR]
[TD]63.1[/TD]
[TD]63.1[/TD]
[/TR]
[TR]
[TD]64.1[/TD]
[TD]64.1[/TD]
[/TR]
[TR]
[TD]65.1[/TD]
[TD]65.1[/TD]
[/TR]
</tbody>[/TABLE]

I changed reference to "7" to "194" as those are the number of rows on sheet 2
 
Upvote 0
powerpuffgirl said:
Thanks! The code is working but only on certain cells. Not sure why.

On those certain cells, first results, however, are always a combination of date/ time? Ex: 3/9/1900 2:24:00 AM, 73.1, 87.1

This line should actually read: 69.1, 73.1, 87.1

The code ran only on a certain percentage of the cells. I had finished some cells manually and know what they should look like, but the results didn't get populated at all on those.
Click to expand...

Okay, don't know what else to try without seeing the actual sheets. I set up the test based on the sheet layouts in your post and the code ran fine for me, so I have now way of troubleshooting the code without seeint the actual sheets. Sorry. Regards, JLG.

P.S. The code only returns what is in column B of sheet 2 so maybe you should check your data to see if some errant entries were made..
 
Last edited:
Upvote 0
Ok, I see the problem. This code will take a little longer to run, but it should do the job.
Code: 
Sub poChase3()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, po As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
    For Each c In rng
        Set po = sh2.Range("A:A").Find(c.Value, LookIn:=xlValues)
            If Not po Is Nothing Then
                fAdr = po.Address
                Do
            With sh2
                For i = 3 To 6
                    If .Cells(po.Row, i).Value = c.Offset(0, 2).Value Then
                        If c.Offset(0, 4) = "" Then
                            c.Offset(0, 7) = po.Offset(0, 1).Value
                        Else
                            c.Offset(0, 7) = c.Offset(0, 7).Value & ", " & po.Offset(0, 1).Value
                        End If
                    End If
                Next
            End With
            po.Value = c.Value
            Set po = sh2.Range("A:A").FindNext(po)
        Loop While fAdr <> po.Address
            End If
    Next
End Sub
I had erroneously assumed that each part number column consisted of parts beginning with the same letter for each type part. I see now that it is a mix and mash of part number congiguration so I don't see any choice but to loop through the columns of each row for each PO match. Like I said, time consuming but it should find them all. Also note that I changed the posting column to column H to match your file.
 
Upvote 0
powerpuffgirl said:
That layout should work fine for me but the formulas are not working for me. First one doesn't give me anything and the second one just starts listing sheet2 PO Line column after confirming Customer Order I believe:

I changed reference to "7" to "194" as those are the number of rows on sheet 2
Click to expand...

Here, the last formula work with the range until row 194. Look at this:

[TABLE="width: 504"]
<tbody>[TR]
[TD="class: xl63, width: 82, bgcolor: transparent"]Customer Order
[/TD]
[TD="class: xl63, width: 27, bgcolor: transparent"]Qty
[/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"]Part Number
[/TD]
[TD="class: xl63, width: 62, bgcolor: transparent"]Due Date
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 1
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 2
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 3
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 4
[/TD]
[TD="class: xl63, width: 77, bgcolor: transparent"]PO Line Item 5
[/TD]
[TD="class: xl65, width: 47, bgcolor: transparent"]Sheet1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl63, bgcolor: transparent"]H778400
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]nov/04
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]131,00
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]155,00
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]156,00
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]157,00
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34895
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent"]H363839
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]dez/04
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]225,00
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH07478
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR73738
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]jan/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]145,00
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH34567
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl63, bgcolor: transparent"]B56378
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]fev/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]155,00
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH32000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl63, bgcolor: transparent"]A40000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]mar/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]150,22
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]114,02
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH38000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR10000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]abr/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]127,18
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]111,29
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]111,29
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH20000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]28
[/TD]
[TD="class: xl63, bgcolor: transparent"]A10000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]mai/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]186,47
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]171,33
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]171,33
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]196,41
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH55000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl63, bgcolor: transparent"]H90000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]jun/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]157,46
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH80000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent"]H90000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]jul/05
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH63000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl63, bgcolor: transparent"]H20000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]ago/05
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH49000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]23
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR40000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]set/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]195,34
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]188,12
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH43000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent"]SR30000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]out/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]102,96
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]140,07
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]126,94
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH42000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl63, bgcolor: transparent"]B20000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]nov/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]185,81
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]CH23000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl63, bgcolor: transparent"]B80000
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]dez/05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]133,03
[/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[TD="class: xl66, bgcolor: yellow"][/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: xl64, bgcolor: transparent"]**********
[/TD]
[TD="class: xl67, bgcolor: transparent"]**************
[/TD]
[TD="class: xl67, bgcolor: transparent"]**************
[/TD]
[TD="class: xl67, bgcolor: transparent"]**************
[/TD]
[TD="class: xl67, bgcolor: transparent"]**************
[/TD]
[TD="class: xl67, bgcolor: transparent"]**************
[/TD]
[TD="class: xl67, bgcolor: transparent"]******
[/TD]
[/TR]
</tbody>[/TABLE]


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

Code: 
In E2 of the Sheet1

=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($E2:E2))),"")

[COLOR=#ff0000]After that, copy E2 to the right until cell I2, 
then copy the range E2:I2 down until the last row with data in column A of the Sheet1 (in my example until row 15).[/COLOR]

Did you press Ctrl+Shift+Enter to enter the formula?

Could you post the formula that you used with your data?


Markmzz
 
Last edited:
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
238
mumps
mumps
F
  • Solved
Listing All corresponding dates for a specific value
Replies
3
Views
336
Fluff
Fluff
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
882
Peter_SSs
Peter_SSs
T
  • Question Question
Pull data from every 5th column based on dropdown selection
Replies
4
Views
244
Twinky
T

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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