I have a table of number entries. Say, for each Product (rows) a number of purchases on each Date (columns).

Date Date
Product 150 200
Product 350 400

I'd like to turn this into one entry for each number on the 2D table. Example:

Product Date 200
Product Date 200
Product Date 350
Product Date 400

Is there a way to do this with formulas, without having to dive into a script to run each time I update the data?

  • Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Mar 28 at 18:05

If each product has the same number of date entries, this is quite easy. You can simply unwrap it by indexing into the data (i,j) using the INDEX function. No need for V and HLookups. Say you have 3 products and 4 dates, that means you will have 12 rows:

Product 1     Date 1       # = index(data,product#,date#)
Product 1     Date 2       #
Product 1     Date 3       #
Product 1     Date 4       #
Product 2     Date 1       #
Product 2     Date 2       #
Product 2     Date 3       #
Product 2     Date 4       #
Product 3     Date 1       #
Product 3     Date 2       #
Product 3     Date 3       #
Product 3     Date 4       #

To generate those patterns (i, j) we can use the ROW(), MOD() and INT() functions. Define your data range (only the sales numbers e.g. $B$2:$C$3 in your example) in sheet1 as a named range call "DATA". Then in sheet 2, in cell A1:

=INDEX(DATA,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1) ...replace 4 with the number of date columns

Not the answer you're looking for? Browse other questions tagged or ask your own question.