Need help with multiple values

[cfmartinez]

I am new to this forum. I need help with either lookups or other more appropriate functions that may help me in handling multiple values on a couple of worksheets. I am enclosing a sample file of what I would like to accomplish. Any help would be greatly appreciated.

[NBVC]

You can copy Sheet2 to Sheet1 as is, then use the Remove Duplicates feature from the Data menu to Remove the duplicates based on the 3 columns of interest.

Then after removing duplicates you can delete the weight and quantity columns and re-arrange the left over 3 columns as desired.

[cfmartinez]

That is not an option since we have situations where we have multiple work orders and purchase orders for the same part number. Plus I would like those cells in Sheet1 protected.

[NBVC]

That is not an option since we have situations where we have multiple work orders and purchase orders for the same part number.

FYI, If you do the Remove Dupes right, it will keep all unique POs and/or Work orders for each part.. it won't remove items that are unique.

[NBVC]

Okay with formulas, see attached.

Assuming Sheet2, A1 starts your original data headers, then in F2:

=IF(COUNTIFS(A$2:A2,A2,D$2:D2,D2,E$2:E2,E2)=1,COUNT(F$1:F1)+1,"")

copied down.

In G2:

=MAX(F:F)

Then in Sheet1, A2:

=IF(ROWS($A$1:$A1)>Sheet2!$G$2,"",INDEX(Sheet2!A:A,MATCH(ROWS($A$1:$A1),Sheet2!$F:$F)))

copied down and similarly in C2:

=IF(ROWS($A$1:$A1)>Sheet2!$G$2,"",INDEX(Sheet2!E:E,MATCH(ROWS($A$1:$A1),Sheet2!$F:$F)))

and in E2:

=IF(ROWS($A$1:$A1)>Sheet2!$G$2,"",INDEX(Sheet2!D:D,MATCH(ROWS($A$1:$A1),Sheet2!$F:$F)))

each copied down as far as you want.