Process the table with mixed quantities and prices in the rows

Question

Hi!

I'm stuck with processing an unconventional table. I have a list of unique products in the leftmost column, and different product types fall into different quantity ranges category (see attached excel and/or screenshot). Yellow rows are quantities ranges.

What I need is to be able to either:

- separate this table into separate table with 1 quantity range per table (obviously keeping the data intact so that it can be seen what price is attributed to which product-quantity combination) or

- put all the quantity ranges into 1 consistent range in the column header (so have like 80 columns instead of 20 with a lot of blank cells for product-quantities that do not exist.

The issue is that the table can be dynamic, changing both the quantities range (adding more column) or adding more products(rows)

I've been trying different stuff for quite some time now, but I can't seem to be able to wrap my head around it. Any advice would be appreciated!

Thank you

table_example.xlsx

Screenshot 2024-01-05 at 11.49.31.png

OllieClarke · Accepted Answer

Hi @kirill_kovalchuk

Here's how I would parse/structure this data. So you then have each individual price and quantity pair per product. This approach is fully dynamic, but works on the assumption that blank product rows signify the quantity headers.

If you really want you can cross-tab it out at the end to make 1 big table, but I wouldn't recommend that personally: