How to create a table from grid data with independant axes

user_13535654 0
Viewed 32

I need to create a table (either pivot or normal) that combines data laid out in this grid:

grid data

The grid shows the count of items by their size, broken down by width on one axis and height on the other. I can't figure out a way to correlate the sizes with the data. The only way I have gotten it to work so far is by manually creating table rows one by one using formulas that reference each cell.

For example, the table should read like this:

Size Count
3 X 3 0
3 X 4 0
3 X 6 20

I'm eventually going to use this table to create a Pareto chart, but if I can create the chart from this grid that would work too.

excel
1 Answers

If you can get columns K and L below to show your distinct combinations, which shouldn't take more than several minutes, then the rest is pretty easy.

Column M is simply concatenating your results to show your data how you desire.

M2 formula: =CONCAT(K2, " x ", L2)

Then column N is using a formula against your data, referencing your values in columns K and L.

N2 formula: =INDEX($A$1:$H$8,MATCH(K2,$A$1:$H$1,0),MATCH(L2,$A$1:$A$8,0))

Drag 'em down and that's it.

enter image description here

user_131180090
Related