This is a bit hacky, but it should work, if my "Assumptions & Interpretation" is correct.
Assumptions & Interpretation
Assumption 1: Your Part Numbers are all unique, and your AttributeIdentifiers are all exactly the same for the attributes you want to have represented in each column, across all parts.
Assumption 2: AttributeIdentifiers are unique within each part - that is, there will never be two lines having both the same AttributeIdentifier and the same Part Number.
Assumption 3: Original data has headers at row 1, and data is populated continuously, beginning at row 2.
Assumption 4: Original data is laid out similar to screenshots provided. "Part Numbers" are in column A, and "AttributeIdentifiers" are in column B, "Values" are in column C, and there is no other data on the sheet relevant to this problem.
Interpretation: You want the Part Numbers to remain in column A, and all attributes for that part listed on one row under headings according to their AttributeIdentifier.
Solution
- Create a new sheet in the same workbook.
- Name the first sheet
Old Data, and the second sheetNew Report. - Copy
'Old Data'!A:Ato'New Report'!A:A. - Perform "Remove Duplicates on
'New Report'!A:A. - Copy
'Old Data'!B:Bto'New Report'!B:B. - Perform "Remove Duplicates" on
'New Report'!B:B.- Make sure you do not expand the selection beyond this column for this operation.
- Delete
'New Report'!B1, and shift the remaining cells in that column up. - Select all cells that have data within
'New Report'!B:B. COPY those cells, and use "Paste Special" and "Transpose" to paste them starting at'New Report'!C1. - Delete
'New Report'!B:B. Make sure'New Report'!C:Cshifts over to take its place. - Add a new column to the left of
'Old Data'!A:A.- Note: This will shift all columns to the right, so that "Part Number" will be in column B and so on.
- (Optional) Set
'Old Data'!A1toUID.
- Set
'Old Data'!A2to=CONCATENATE(B2,C2). - Copy
'Old Data'!A2down the entire column, until the end of the data set. - Set
'New Report'!B2to:=IFERROR(VLOOKUP(CONCATENATE($A2,B$1),'Old Data'!$A:$D,4,FALSE),""). - Copy
'New Report'!B2down the rest of the column, until the end of the data set. - Copy all cells in
'New Report'!B:B, starting with'New Report'!B2and ending at the bottom of the data set, across to the remaining columns on the right. - Select the entire
'New Report'sheet. Copy it and paste it in-place "As Values", to lock in the data. - (Optional) Delete
'Old Data'!A:A.
Explanation of Step 14
The formula, in short, searches 'Old Data' for an appropriate value to place in the cell according to the matching "Part Number" and "AttributeIdentifier". If none is found, it will leave the cell blank.
Note: I have not personally tested this solution for your use case. However, I'm familiar enough with the steps involved to be reasonably confident that it should work for you with little need for modification. Please let me know if you encounter any errors.