Search code examples
excelconsolidation

How to consolidate duplicate data with both numbers and text

Good afternoon everyone.

I'm attempting to consolidate a table full of duplicate values and I've found the "consolidate" feature works very well for this but, the problem I've stumbled upon is it won't keep the "material" text with it after it consolidates.

This is the raw data:

Part Number Quantity    Material
02210144US  3   22ga Galvanized
02210144US  3   22ga Galvanized
02210144US  3   22ga Galvanized
02210144US  2   22ga Galvanized
02210144US  2   22ga Galvanized
02210144US  2   22ga Galvanized
02332256US  2   26ga Galvanized
02332256US  2   26ga Galvanized
21010601    1   18ga Galvaneal
21010601    1   18ga Galvaneal
21073101    1   26ga Galvanized
21073101    1   26ga Galvanized
21073102    1   26ga Galvanized
21073103    1   26ga Galvanized
21073103    1   26ga Galvanized
21073103    1   26ga Galvanized
21073111    1   26ga Galvanized
21073111    1   26ga Galvanized
21073112    1   26ga Galvanized
21073113    1   26ga Galvanized
21073113    1   26ga Galvanized
21073113    1   26ga Galvanized
21073201    1   26ga Galvanized
21073201    1   26ga Galvanized
21073202    1   26ga Galvanized
21073203    1   26ga Galvanized
21073203    1   26ga Galvanized
21073203    1   26ga Galvanized
21073301    2   26ga Galvanized
21073301    2   26ga Galvanized
21073302    2   26ga Galvanized
21073303    2   26ga Galvanized
21073303    2   26ga Galvanized
21073303    2   26ga Galvanized
21073501    2   26ga Galvanized
21073501    2   26ga Galvanized
21073502    2   26ga Galvanized
21073503    2   26ga Galvanized
21073503    2   26ga Galvanized
21073503    2   26ga Galvanized
21081101    1   26ga Galvalume (Alu-Zinc)
21081101    1   26ga Galvalume (Alu-Zinc)
21081102    1   26ga Galvalume (Alu-Zinc)
21081103    1   26ga Galvalume (Alu-Zinc)
21081103    1   26ga Galvalume (Alu-Zinc)
21081103    1   26ga Galvalume (Alu-Zinc)
21081201    1   26ga Galvanized
21081201    1   26ga Galvanized
21081202    1   26ga Galvanized
21081203    1   26ga Galvanized
21081203    1   26ga Galvanized
21081203    1   26ga Galvanized
21420061P   1   22ga Galvaneal
21420061P   1   22ga Galvaneal
21422092P   2   22ga Galvaneal
21422092P   2   22ga Galvaneal
22011035B   2   22ga Black Pre-Painted
22011035B   2   22ga Black Pre-Painted
22011036B   3   22ga Black Pre-Painted
22011036B   3   22ga Black Pre-Painted
22011036B   3   22ga Black Pre-Painted
22011036B   2   22ga Black Pre-Painted
22070874BL  1   22ga Black Pre-Painted
22070874BL  1   22ga Black Pre-Painted
22070875BL  1   22ga Black Pre-Painted
22070876BL  1   22ga Black Pre-Painted
22070876BL  1   22ga Black Pre-Painted
22070876BL  1   22ga Black Pre-Painted
22070887B   1   22ga Black Pre-Painted
22070887B   2   22ga Black Pre-Painted
22070887B   1   22ga Black Pre-Painted
22070887B   2   22ga Black Pre-Painted
22070887B   2   22ga Black Pre-Painted
22273301    2   26ga Galvanized
22273301    2   26ga Galvanized
22273302    2   26ga Galvanized
22273303    2   26ga Galvanized
22273303    2   26ga Galvanized
22273303    2   26ga Galvanized

And here is what it looks like after I consolidate it: 

(blank) Quantity    Material
02210144US  15  (blank)
02332256US  4   (blank)
21010601    2   (blank)
21073101    2   (blank)
21073102    1   (blank)
21073103    3   (blank)
21073111    2   (blank)
21073112    1   (blank)
21073113    3   (blank)
21073201    2   (blank)
21073202    1   (blank)
21073203    3   (blank)
21073301    4   (blank)
21073302    2   (blank)
21073303    6   (blank)
21073501    4   (blank)
21073502    2   (blank)
21073503    6   (blank)
21081101    2   (blank)
21081102    1   (blank)
21081103    3   (blank)
21081201    2   (blank)
21081202    1   (blank)
21081203    3   (blank)
21420061P   2   (blank)
21422092P   4   (blank)
22011035B   4   (blank)
22011036B   11  (blank)
22070874BL  2   (blank)
22070875BL  1   (blank)
22070876BL  3   (blank)
22070887B   8   (blank)
22273301    4   (blank)
22273302    2   (blank)
22273303    6   (blank)

And this is how I was hoping it turned out:

Part Number Quantity    Material
02210144US  15  22ga Galvanized
02332256US  4   26ga Galvanized
21010601    2   18ga Neal
21073101    2   26ga Galvanized
21073102    1   26ga Galvanized
21073103    3   26ga Galvanized
21073111    2   26ga Galvanized
21073112    1   26ga Galvanized
21073113    3   26ga Galvanized
21073201    2   26ga Galvanized
21073202    1   26ga Galvanized
21073203    3   26ga Galvanized
21073301    4   26ga Galvanized
21073302    2   26ga Galvanized
21073303    6   26ga Galvanized
21073501    4   26ga Galvanized
21073502    2   26ga Galvanized
21073503    6   26ga Galvanized
21081101    2   22ga Galvanized
21081102    1   22ga Galvanized
21081103    3   22ga Galvanized
21081201    2   26ga Galvanized
21081202    1   26ga Galvanized
21081203    3   26ga Galvanized
21420061P   2   22ga Galvaneal
21422092P   4   22ga Galvaneal
22011035B   4   22ga Black Pre-Painted
22011036B   11  22ga Black Pre-Painted
22070874BL  2   22ga Black Pre-Painted
22070875BL  1   22ga Black Pre-Painted
22070876BL  3   22ga Black Pre-Painted
22070887B   8   22ga Black Pre-Painted
22273301    4   26ga Galvanized
22273302    2   26ga Galvanized
22273303    6   26ga Galvanized

Any idea what I'm doing wrong? I've checked both "left column" and "top row" check-boxes, as well as trying a different combination of the two, only one, none, etc. Thank you for any and all help you can provide!

Solution

  • Try:

    1. Select all the data
    2. Click PivotTable
    3. Drag 'Part Number' to 'rows' box on the right pane that pops up.
    4. Drag 'Materials' under 'Part Number' in the rows box.
    5. Drag 'Quantity' to the 'Values' box

    Next we correct the formatting:

    1. Click the 'Design' tab at the top
    2. Click 'Subtotals', 'Do not show subtotals'
    3. Click 'Grand totals', 'Off for rows and columns'
    4. Click 'Report layout', 'Repeat all item labels'
    5. Click 'Report Layout', 'show in tabular form'

    This should do it, but it switches the material and quantity columns.

    1. Now that you have the data, if you want it out of the pivotTable form you can select all the data, copy, right click in another sheet, click the paste icon with the 123 on it (paste values).