Forum Discussion
Take a series of rows and break each row into multiple rows
Once again, I look to the Excel Gurus to assist. I have been stewing about this most of the day. I have rows of data and I need to take each row and some of its cells and produce a single row for each of the key cells. The heading is actually a key in a lookup table for XLOOKUP. There are a number of static fields as well. This sample shows the original output from a FILTER formula in the first 4 rows. Below that is the desired state.
NotSoFastEddie Thank you for providing the desired results and additional explanation. Try the newly attached file... it's basically just a combination of the techniques used in the first two versions, with an additional XLOOKUP.
The IF / SEQUENCE method is used to repeat data the required number of times, TOCOL sends each array to a single column, and HSTACK joins each of the columns together horizontally to generate a single output array. To get a better understanding of how these methods work, it's best to start by experimenting with basic examples. For more information, please see: https://techcommunity.microsoft.com/t5/excel/copy-array-n-times-with-excel-formula...
If further explanation is needed, please feel free to send me a private message (just click on my profile name or icon, then click the Message button). Cheers!
16 Replies
NotSoFastEddie To unpivot the data in range A1:H4, try the following:
=LET( tbl, A1:H4, data, DROP(tbl, 1, 1), code, DROP(TAKE(tbl,, 1), 1), attr, DROP(TAKE(tbl, 1),, 1), HSTACK( TOCOL(IF(SEQUENCE(, COLUMNS(data)), code)), TOCOL(IF(SEQUENCE(ROWS(data)), attr)), TOCOL(data) ) )I'm not entirely sure what you need returned for the other 4 columns (value, Unit, valuePosition, language), but if they all need to be filled with "n/a" and "EN_CA (static)", the EXPAND function could work. See attached...
djclements
Thank you very much for your response. It works well, except, the classificationAttributeCode is a lookup in the table to the right using the column heading (e.g. Accessories List) and returning PS_ACCESSORY. What I did is substitute the last entry in your formula for a simple string "test msg") and the your formula continued to work and placed "test msg" into the cell below Classification Attribute. I then substituted an XLOOKUP function for that last entry in your formula, but ran into a some weird behaviour.
=LET( tbl, A1:H4, data, DROP(tbl, 1, 1), code, DROP(TAKE(tbl,, 1), 1), attr, DROP(TAKE(tbl, 1),, 1),
HSTACK(TOCOL(IF(SEQUENCE(, COLUMNS(data)), code)), TOCOL(IF(SEQUENCE(ROWS(data)), attr)),XLOOKUP(attr,Table1[Attribute name],Table1[[Classification Attribute ]]) )).It kind of worked, but it also looks like it did the XLOOKUP for every heading in the row. It looks like attr will in fact have "Accessories List" as the value for the first row.
As an aside, is there a way to debug or at least view what values are in data, code and attr?
I tried using your EXPAND formula putting the XLOOKUP there. I had some luck, but it only looked up for row b8. If I use B8#, I get a #REF error
=EXPAND(XLOOKUP(B8,Table1[Attribute name],Table1[[Classification Attribute ]]), ROWS(A8#), COLUMNS(A8#), "n/a")
