Excel 2007 : Align rows by matching values in other columns

[jhanson60]

I have an extremely large spreadhseet with many rows of data.

Column A is a master list of part numbers (in ascending order).
Column D is a list of part numbers with a corresponding quantity in column E.

A	B	C	D	E
Part No	Description	Location	Part No	Qty
1028-001	7/16 J channel, white	W531	1028-001	525
1028-002	7/16 J channel, blue	W534	1028-003	101
1028-003	7/16 J channel, cream	W653	1028-004	630
1028-004	7/16 J channel, slate	W413	1028-005	542
1028-005	7/16 J channel, red	W535	1028-006	678

I need the part numbers in D (along with their quantities in E) to line up with corresponding part numbers in Column A like this:

A	B	C	D	E
Part No	Description	Location	Part No	Qty
1028-001	7/16 J channel, white	W531	1028-001	525
1028-002	7/16 J channel, blue	W534
1028-003	7/16 J channel, cream	W653	1028-003	101
1028-004	7/16 J channel, slate	W413	1028-004	630
1028-005	7/16 J channel, red	W535	1028-005	542

I'm not sure this makes sense or is even possible but if anyone can help, I'd greatly appreciate it.

[FDibbins]

I have added a column to your data that does what you need. However, it might help if the 2nd set of part mumber info could be in a seperate table. The formula provided will work either way though

[jhanson60]

I have added a column to your data that does what you need. However, it might help if the 2nd set of part mumber info could be in a seperate table. The formula provided will work either way though

That file has nothing to do with what I need.

[arlu1201]

Try this out - in F2 put this formula -

Please Login or Register  to view this content.

Put this formula in G2 -

Please Login or Register  to view this content.

Drag both down as necessary.
Then copy columns F & G and paste special values them in columns D & E.

[jhanson60]

Try this out - in F2 put this formula -

Please Login or Register  to view this content.

Put this formula in G2 -

Please Login or Register  to view this content.

Drag both down as necessary.
Then copy columns F & G and paste special values them in columns D & E.

THANK YOU! THANK YOU! THANK YOU! It worked perfectly! You saved me hours, if not days of work. I truly appreciate it.

[klover0210]

hi, tried this out but #NAME? appears. something i am missing? thanks

[arlu1201]

Have you changed the cell references to match your file?

[grass03]

Works for me. Thank you arlu1201