Forum Discussion
Take a series of rows and break each row into multiple rows
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!
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")
NotSoFastEddie Perhaps I misunderstood the question. I thought you wanted the data from each row in range A2:H4 returned on multiple rows, but it seems that you simply want the data from the lookup table repeated for each PCODE. If so, try the following instead:
=LET( range1, A2:A4, range2, Table1[[Attribute name]:[Classification Attribute ]], a, SEQUENCE(ROWS(range1)), b, SEQUENCE(, ROWS(range2)), results, HSTACK(TOCOL(IF(b, range1)), CHOOSEROWS(range2, TOCOL(IF(a, b)))), EXPAND(EXPAND(results,, 6, "n/a"),, 7, "EN_CA (static)") )DJ, I apologize for dragging this on. I had some issues on my side with the instructions I received. In the newly attached spreadsheet, I have addressed those and provided explanations. There is a second worksheet, called DesiredResults that hopefully explains more fully what I am looking for.
I have tried to play around with the formula, but I am struggling to understand, exactly what it is doing. I know this is a lot to ask, but would it be possible to get a Teams Meeting to review the actual formula, for my understanding, so I can be more independent. If not, any help is appreciated.
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!