excel - How to transpose all subfields in front of the parent field of a pivot table in Excel

Question

I have a data of automotive spare parts with their multiple store locations in a warehouse. all I want to do is get the locations in front of the part number, so that it is easy to know all the locations of a specific part number.

The current pivot data looks like this

I've manually transposed a few rows in the below image, but the data contains around 70K rows, Hence I'm looking for a better solution

Kindly refer to the below table

+--------------+-----+-------+-------------+
|  Item name   | Qty |  UoM  |    Stock    |
+--------------+-----+-------+-------------+
| '0450000115  | 324 | piece | G12B04      |
| '0450000A61  | 312 | piece | G12B05      |
| '0450000115  | 336 | piece | G12B06      |
| '0450000A61  | 228 | piece | G12B07      |
| '0450000115  | 336 | piece | G12B08      |
| '0450000115  | 192 | piece | G12B09      |
| '087902E200A | 470 | piece | G12B10      |
| '087902E200A | 760 | piece | G12B13      |
| '087902E200A | 759 | piece | G12B14      |
| '0450000115  | 336 | piece | G12B15      |
| '087902E200A | 400 | piece | G12B16      |
| '087902E200A |  10 | piece | G3B32       |
| '084B410426  | 100 | piece | G3B32       |
| '087902E200A | 300 | piece | G4B08       |
| '0450000A61  |   2 | piece | GDB01       |
| '084B410426  |  60 | piece | GR.04.C.04. |
| '087902E200A | 327 | piece | HD.03.K.05. |
+--------------+-----+-------+-------------+

标签： excelvbaexcel-2016

Answer 1

You need to create a measure, using the CONCATENATEX function. For this you need to add your data to the datamodel. You can do this by checking the box add this data to the datamodel on the bottom of the create pivottable dialogbox.

Rightclick the table on the Pivottable Fields Pane and select add measure. Then create the following measure: = CONCATENATEX('table','table'[Stock],", ")

Now put [Item name] on Rows and the measure [StockText] on Values. This should be the result: