How can you eliminate duplicates in a bill of materials while also showing the reference designators separated by commas?


The image shows a simple example of what I am after. Starting with the table on the left, how to make it look like the one on the right?
Thank you.

Attached Images

Capture.JPG (47.9 KB, 21 views)

An example how organize data entry and get bill details table for any chosen bill.

Columns in Tables having formulas are colored differently. In case you feel there is no need for users to see some of calculated columns, you can hide them. Some calculated columns are helper columns, and are needed to allow to use SUMIFS()/COUNTIFS() formulas.

I used Defined Tables in my example. In Defined Tables (From menu: Insert>Table), formulas and formats are automatically expanded whenever a new row is added (assumed the formula/format remains same for entire column). And whenever you change Table or Column Name, those changes are automatically reflected in all Table formulas over entire workbook.

Attached Files

PurchasesExample.xlsx (19.9 KB, 11 views)

Thanks a lot. Will work on digesting that.

Update the table on the left, then right-click the table on the right and choose Refresh.
I've assumed a grouping based on Val and Size.

Attached Files

msofficeforums46945.xlsx (18.2 KB, 10 views)