Forum Discussion
Consolidate a worksheet with many columns to show only the cells that are used.
Hello,
I have an exported item price list, which shows 4 different levels of pricing for every item that we sell. Each row has the Item SKU in column A, and the 4 prices are listed to the right in subsequent columns. However, because the pricing in our system is based on item category (we have 13 item categories), every category has its own column for each pricing tier. There are 13 categories with 4 tiers in each category for a total of 52 price columns. For each item, only four of the 52 price columns have prices in them. The columns are organized L-R by item category, then by price tier.
On a new sheet of the workbook, I have copied the list of SKUs. I would like to write a formula on this sheet that will copy the values of whichever four columns have prices in them for each SKU and paste them into columns B, C, D, and E, so that I don't have everything spread across 52 columns. Could someone recommend the best way to do this?
Thank you!
Anonymous66 I would do this with Power Query (PQ). Connect to the pricing table and flatten it with the Unpivot command. In the attached file you find a simple example, with a table containing only 3 categories. But the principle is the same for 13.
If you are not familiar with PQ or can't get it to work on the real pricing table, come back here.
2 Replies
Anonymous66 I would do this with Power Query (PQ). Connect to the pricing table and flatten it with the Unpivot command. In the attached file you find a simple example, with a table containing only 3 categories. But the principle is the same for 13.
If you are not familiar with PQ or can't get it to work on the real pricing table, come back here.
- Thank you for your help! This works for my application 👍
