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.

JLGWhiz · Oct 18, 2013

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

powerpuffgirl · Oct 19, 2013

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.

Order	Qty	Item	Ship Date	PO Line Item
CH77270	13	H90272-241	25-Oct	70.1, 81.1, 86.1
CH77270	2	H90272-237	25-Oct	61.1
CH77270	3	H90272-240	25-Oct	68.1
CH77270	12	H90005-702	25-Oct	69.1, 73.1, 87.1
CH84427	4	H90221-109	25-Oct	63.1
CH84270	14	H90375-24	25-Oct	75.1, 88.1
CH34270	4	H90374-15	25-Oct	76.1
CH34270	8	H90068-307	25-Oct	79.1

JLGWhiz · Oct 19, 2013

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

powerpuffgirl · Oct 19, 2013

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.

markmzz · Oct 19, 2013

Maybe this:

Layout

Customer Order	Qty	Part Number	Due Date	PO Line Item 1	PO Line Item 2	PO Line Item 3	PO Line Item 4	PO Line Item 5	Sheet1
CH34567	14	H778400	nov/04	131	155	156	157
CH34895	15	H363839	dez/04	225
CH07478	16	SR73738	jan/05	145
CH34567	17	B56378	fev/05	155
*
*
*
Customer Order	PO Line Item	PN Hubs	PN Flanges	PN Seal Rings	PN Bolts	Sheet2
CH34567	131	H778400	A73973	SR38377	B38388
CH34895	225	H363839	A77489	SR47488	B39847
CH07478	145	H476567	A36378	SR73738	B74466
CH34567	155	H778400	A87664	SR64747	B56378
CH34567	156	H778400	H778401	H778402
CH34567	157	H778400	H778404	H778405	H778406
***************	************	************	***********	**************	**************	**************	**************	**************	********

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

powerpuffgirl · Oct 19, 2013

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:

second formula sheet1 column E	original sheet2 column b (PO Line Items)
53.1	53.1
55.1	55.1
57.1	57.1
61.1	61.1
63.1	63.1
64.1	64.1
65.1	65.1

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

JLGWhiz · Oct 19, 2013

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.

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..

JLGWhiz · Oct 19, 2013

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.

markmzz · Oct 20, 2013

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

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

Customer Order	Qty	Part Number	Due Date	PO Line Item 1	PO Line Item 2	PO Line Item 3	PO Line Item 4	PO Line Item 5	Sheet1
CH34567	14	H778400	nov/04	131,00	155,00	156,00	157,00
CH34895	15	H363839	dez/04	225,00
CH07478	16	SR73738	jan/05	145,00
CH34567	17	B56378	fev/05	155,00
CH32000	11	A40000	mar/05	150,22	114,02
CH38000	21	SR10000	abr/05	127,18	111,29	111,29
CH20000	28	A10000	mai/05	186,47	171,33	171,33	196,41
CH55000	18	H90000	jun/05	157,46
CH80000	21	H90000	jul/05
CH63000	11	H20000	ago/05
CH49000	23	SR40000	set/05	195,34	188,12
CH43000	27	SR30000	out/05	102,96	140,07	126,94
CH42000	19	B20000	nov/05	185,81
CH23000	18	B80000	dez/05	133,03
***************	****	************	**********	**************	**************	**************	**************	**************	******

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

Extract Data From Specific Column

powerpuffgirl

New Member

Excel Facts

JLGWhiz

Well-known Member

powerpuffgirl

New Member

JLGWhiz

Well-known Member

powerpuffgirl

New Member

markmzz

MrExcel MVP

powerpuffgirl

New Member

JLGWhiz

Well-known Member

JLGWhiz

Well-known Member

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

Extract Data From Specific Column

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

MrExcel MVP

New Member

Well-known Member

Well-known 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