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
Not getting the second formula to work. Why are we referencing B1 in Match formula? MATCH(B$1,Sheet01!$A$1:$F$1,0) < I am a bit confused about this portion of the formula.

markmzz said:
Maybe this:

Layout

PO #OrderQtyItemShip DateItem DescriptionPO Line 1PO Line 2PO Line 3PO Line 4PO Line 5PO Line 6PO Line 7PO Line 8PO Line 9PO Line 10Sheet01
131CH3427031A90019-78WB04/novCLAMP,2PC 4BOLT, 1, AISI 4140,53,161,168,169,170,173,181,182,186,187,1
131CH3427014A90148-96WB18/novCLAMP,2PC 4BOLT, 2, AISI 4140,55,165,175,188,1
131CH3427015A90413-77WB22/novCLAMP,2PC 4BOLT, 2-1/2- 3, AISI57,167,176,179,180,184,1
131CH342702H90235-45WB04/novCLAMP,2PC 4BOLT, 4, AISI 4140,77,183,185,1
131CH3427020H90329-35WB05/novCLAMP,2PC 4BOLT, 8, AISI 4140,71,172,174,1
131CH342702H90314-36WB07/novCLAMP,2PC 4BOLT,10H, AISI 4140,63,166,178,1
*********************************************************************************************************************************************************************

<tbody>
</tbody>

PO LinePO #Ship DateItemItem DescriptionSheet03
53,113104/novA90019-78WBCLAMP,2PC 4BOLT, 1, AISI 4140,
55,113118/novA90148-96WBCLAMP,2PC 4BOLT, 2, AISI 4140,
57,113122/novA90413-77WBCLAMP,2PC 4BOLT, 2-1/2- 3, AISI
61,113104/novA90019-78WBCLAMP,2PC 4BOLT, 1, AISI 4140,
63,113107/novH90314-36WBCLAMP,2PC 4BOLT,10H, AISI 4140,
65,113118/novA90148-96WBCLAMP,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
Click to expand...
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
powerpuffgirl said:
Not getting the second formula to work. Why are we referencing B1 in Match formula? MATCH(B$1,Sheet01!$A$1:$F$1,0) < I am a bit confused about this portion of the formula.
Click to expand...

Hi,

B$1 (and C$1, D$1 and E$1) is the header in Sheet03 and the same header in Sheet01. Look at my examples sheets.

So MATCH(B$1,Sheet01!$A$1:$F$1,0) find the column of the data that you need (PO #, Ship Date, Item and Item Description).

Markmzz
 
Upvote 0
The first formula is skipping some PO Line Items on sheet01. Not sure why that would be. There are rows in sheet01 that are completely blank. Maybe because of that it's not capturing everything and skipping items between columns?

For example:

POCustomer Order No.Job No.Part NumberQty ReleasedQty CompleteShip DateStatusDescriptionPO Line 1PO Line 2PO Line 3PO Line 4
131CH34270JH43694H90027-5112011/15/13RGRAYLOC SEAL RING, 23,HIGH STRENGTH, AI79.1
131CH34270JH43696H90034-914012/02/13RGRAYLOC SEAL RING, 62,HIGH STRENGTH, AI74.1
131CH34270JH43707H90133-139624011/15/13RGRAYLOC HUB,BW, 8 GR42, 8.625 OD x 4.1
131CH34270JH43713H90208-1638011/15/13RGRAYLOC HUB,BW, 8 GR67, 8.625 OD x 6.625
131CH34270JH43714H90208-1644011/15/13RGRAYLOC HUB,BW, 8 GR62, 8.625 OD x 6.00174.1

<colgroup><col><col><col><col><col span="2"><col><col><col><col span="4"></colgroup><tbody>
</tbody>

markmzz said:
Hi,

B$1 (and C$1, D$1 and E$1) is the header in Sheet03 and the same header in Sheet01. Look at my examples sheets.

So MATCH(B$1,Sheet01!$A$1:$F$1,0) find the column of the data that you need (PO #, Ship Date, Item and Item Description).

Markmzz
Click to expand...
 
Upvote 0
powerpuffgirl said:
The first formula is skipping some PO Line Items on sheet01. Not sure why that would be. There are rows in sheet01 that are completely blank. Maybe because of that it's not capturing everything and skipping items between columns?

For example:

PO
Customer Order No.
Job No.
Part Number
Qty Released
Qty Complete
Ship Date
Status
Description
PO Line 1
PO Line 2
PO Line 3
PO Line 4
131
CH34270
JH43694
H90027-51
12
11/15/13
R
GRAYLOC SEAL RING, 23,HIGH STRENGTH, AI
79.1
131
CH34270
JH43696
H90034-91
4
12/02/13
R
GRAYLOC SEAL RING, 62,HIGH STRENGTH, AI
74.1
131
CH34270JH43707
H90133-1396
24
11/15/13
R
GRAYLOC HUB,BW, 8 GR42, 8.625 OD x 4.1
131
CH34270
JH43713
H90208-163
8
11/15/13
R
GRAYLOC HUB,BW, 8 GR67, 8.625 OD x 6.625
131
CH34270
JH43714
H90208-164
4
11/15/13
R
GRAYLOC HUB,BW, 8 GR62, 8.625 OD x 6.001
74.1

<tbody>
</tbody>
Click to expand...

Hi,

You don't have data in the Master Data sheet for CH34270 & H90133-1396 and CH34270 & H90208-163.

So you don't have PO for that values.

Markmzz
 
Upvote 0
That's correct. I was just thinking maybe blank cells were driving skipped entries on Sheet3. If you notice, Sheet3 jumps from 72.1 to 152.1 but I have several entries on Sheet1 in between those numbers on Order CH34270.

Also, the second formula is only capturing the first item descriptions related to PO Line item

Ex Sheet3:
1.1 Seal Ring
1.1 Seal Ring
1.1 Seal Ring

When it should capture different 1.1s (there are 3 different identifiers)
1.1 Seal Ring
1.1 Clamp
1.1 Hub

In Sheet1, seal ring is listed first so I am guessing it only captures the first item description and part number?

markmzz said:
Hi,

You don't have data in the Master Data sheet for CH34270 & H90133-1396 and CH34270 & H90208-163.

So you don't have PO for that values.

Markmzz
Click to expand...
 
Upvote 0
powerpuffgirl said:
That's correct. I was just thinking maybe blank cells were driving skipped entries on Sheet3. If you notice, Sheet3 jumps from 72.1 to 152.1 but I have several entries on Sheet1 in between those numbers on Order CH34270.

Also, the second formula is only capturing the first item descriptions related to PO Line item

Ex Sheet3:
1.1 Seal Ring
1.1 Seal Ring
1.1 Seal Ring

When it should capture different 1.1s (there are 3 different identifiers)
1.1 Seal Ring
1.1 Clamp
1.1 Hub

In Sheet1, seal ring is listed first so I am guessing it only captures the first item description and part number?
Click to expand...

Look at your PM Box.

Markmzz
 
Upvote 0
Hi,

Here is the last formula:

Code: 
In B2 of the sheet Sheet3 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF($A2="","",INDEX(Sheet1!$A$2:$I$286,
SMALL(IF(IF(Sheet1!$J$2:$U$286<>"",Sheet1!$J$2:$U$286+ROW(Sheet1!$J$2:$U$286)/10^7)=
SMALL(IF(Sheet1!$J$2:$U$286<>"",Sheet1!$J$2:$U$286+ROW(Sheet1!$J$2:$U$286)/10^7),ROWS($A$2:$A2)),ROW(Sheet1!$J$2:$U$286)-ROW(Sheet1!$J$2)+1),1),
MATCH(B$1,Sheet1!$A$1:$I$1,0)))

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,461
Messages
6,124,955
Members
449,199
Latest member
Riley Johnson

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