Forum Discussion
Changing row/column orientation (?)
Ok, I'm not sure how to word this...
I have a huge list of items that all have several ROWS each. Each row of information for the products varies only by quantities and price.
Example...this is ONE item (I have hundreds)
| Item | Quantity | Base Net Price (Each) |
| 13001 | 125 | 0.92000 |
| 13001 | 250 | 0.52500 |
| 13001 | 500 | 0.29450 |
| 13001 | 1000 | 0.17500 |
| 13001 | 1500 | 0.13500 |
| 13001 | 2500 | 0.09900 |
| 13001 | 5000 | 0.06240 |
This is the result I want:
| Item | Qty 1 | Qty 2 | Qty 3 | Qty 4 | Qty 5 | Qty 6 | Qty 7 | Base Net 1 | Base Net 2 | Base Net 3 | Base Net 4 | Base Net 5 | Base Net 6 | Base Net 7 |
| 13001 | 125 | 250 | 500 | 1000 | 1500 | 2500 | 5000 | 0.92 | 0.525 | 0.2945 | 0.175 | 0.135 | 0.099 | 0.0624 |
How do I do this quickly (without just cutting/pasting for hundreds of products?)
2 Replies
=IFERROR(INDEX($B$3:$B$21,SMALL(IF($A$3:$A$21=$E3,ROW($1:$19)),COLUMN(A1))),"")
=IFERROR(INDEX($C$3:$C$21,SMALL(IF($A$3:$A$21=$E3,ROW($1:$19)),COLUMN(A1))),"")
These formulas could be an alternative solution. Enter formulas as matrixformulas with ctrl+shift+enter if you don't work with Office365 or 2021. The unique list of items can be created with data -> remove duplicates.
ppaccounting The attached file contains two possible solutions. One with old-fashioned indexing formula. The other with modern dynamic array functions UNIQUE, FILTER and TRANSPOSE. The yellow coloured cell are the ones that contain formlae.
Both can be improved by using a structured table for the data or named ranges. But in this example I kept the direct cell references so that you can figure out how it all works.
Edit: Since you mentioned to work with hundreds of products, consider using Power Query. Added another file with an example.
