Assume a two column database of approximately 20,000 rows (say from A1:B20237). The first column has vendor names and the second column has Part codes (alphanumeric string). Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1. One vendor supplies multiple parts and therefore there would be repetitions of vendor names in column A.
The task is to reorient this database to show all part numbers in column A and parts supplied by those vendors should appear in different columns of that row. If one attempts to create a pivot table, the problem would be with the data area of the pivot table. When one drags the part codes (alphanumeric string) column to the data area, count of part number would appear instead of the actual part number. This is the very nature of a pivot table. Since it is a summarisation tool, one can never see text entries in the data area of a pivot table.
You may download the workbook showing both solutions (using MS Query and Power Query) from here.
Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem
Solution for MS Excel 2010 and higher versions
If you are using the Power Query add-in, then a few simple steps (minimal formulas) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table). You may refer to Solution2 – Power Query worksheet.
You may watch a short video of my solution here
Solution for all versions of MS Excel
You may refer to Solution1 – MS Query worksheet. To reorient the data i.e. to view text entries in the data area of a pivot table, try the following approach:
1. In cell C1, type Count. So the value in cell A1 is Vendor, in cell B1 is Part_code and in cell C1 is Count
2. In cell C2, enter =$B$1&” “&COUNTIF(A$2:A2,A2) and copy down. Since the number of rows here are quite many, i would suggest copying in batches i.e. first copy down 5,000 rows, then another 5,000 rows and so on. This is solely to prevent Excel from crashing and/or taking unduly long to process.
3. Select range A1:C20237 and press Ctrl+F3 > New. In the name box, enter Dummy
4. Select range A1:C20237 and press Ctrl+T to convert to a Table. This is to take care of data being added beyond row 20237.
5. Save the file and open a new worksheet (in the same workbook)
6. While on any cell in this new worksheet, go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
7. Navigate to the folder where you saved the file in step 5 above, select the file and click on OK
8. With Dummy selected by default, press the > symbol. This will show you three column headings in the right hand side box
9. Click on Next three times
10. Select the second option button and click on Finish
11. Click on the SQL button and delete all contents that you see there. Type the following in the blank white space
TRANSFORM first(Part_code)
SELECT Vendor
FROM dummy
GROUP BY Vendor
PIVOT count
12. Click on OK
13. Go to Data > Return Data to MS Excel
14. In the Import Data box, select Table and in Existing sheet, select cell A1
15. Click on Finish
Data should now appear in the desired format.
For any changes to data in range A2:B20237, right-click on any cell in the output range and select Refresh. Furthermore, since the range has been converted to a Table, you may even add further rows of data and simply right-click to Refresh the output data range.
To improve performance of your workbook, you may want to copy the formulas in column C and paste them as Values (Paste Special > Values). Once again, do not paste special all values at once – do so in groups of 5,000 rows to prevent Excel from crashing and/or taking unduly long to process.
Display text entries in the data area of a pivot table
{ 60 Comments }