Forum Discussion
Solved
Create a list of columns with value per row
Subject probably makes no sense so let me explain with an example: A.1 A.2 A.3 A.4 A.5 A.6 A.7 A.8 DESIRED RESULT #001 1 3 4 A.1, A.4, A.5 #002 ...
=BYROW(B2:I9,LAMBDA(row,TEXTJOIN(",",,FILTER(B1:I1,NOT(ISBLANK(row))))))You can try this formula.
OliverScheurich Vielen Dank!!!
That works perfectly - I won't pretend to follow the logic or understand what a LAMBDA function is but the formula does exactly what I asked. I've even managed to transpose it so I get the IDs per column as follows:
=BYCOL(B2:I10,LAMBDA(column,TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A10,NOT(ISBLANK(column))))))
Your help is very much appreciated!
Blast; I spoke too soon 😞
The formula works perfectly in the example I provided however, the real use case is slightly different.
A table similar to my example is one tab of a workbook named Assessment, the IDs are in cells A7:A130, the header values are in cells L7:CZ7 so the range of cells is L7:CZ130.
Another tab named SOA contains a list of the header values in 'Assessment'!L7:CZ7 in Col A (cells A7:A95). The SOA tab contains a range of information associated with each header value in Cols B:K.
I'd like to get the IDs per header value column using the transposed formula in my last reply and insert these into a new column in the SOA tab.
I put the example formula into cell 'SOA'!L7 and it returned the expected results but these are inserted into 'SOA'!L7:CZ7. I need the IDs going down Col L from L3:L95 aligned with the relevant entry from 'Assessment'!L7:L130
I've tried simply changing to BYROW which does fill Col L but only returns a "#VALUE!" error.
My amended formula in 'SOA'!L3 is:
=BYROW('Assessment'!L7:CZ130,LAMBDA(column,TEXTJOIN(",",TRUE,FILTER('Assessment'!A7:A130,NOT(ISBLANK(column)))))).
Sorry for not explaining my use case properly; I incorrectly assumed that transposing the desired results would have little impact on the formula.
thanks in advance if anyone can help
The formula works perfectly in the example I provided however, the real use case is slightly different.
A table similar to my example is one tab of a workbook named Assessment, the IDs are in cells A7:A130, the header values are in cells L7:CZ7 so the range of cells is L7:CZ130.
Another tab named SOA contains a list of the header values in 'Assessment'!L7:CZ7 in Col A (cells A7:A95). The SOA tab contains a range of information associated with each header value in Cols B:K.
I'd like to get the IDs per header value column using the transposed formula in my last reply and insert these into a new column in the SOA tab.
I put the example formula into cell 'SOA'!L7 and it returned the expected results but these are inserted into 'SOA'!L7:CZ7. I need the IDs going down Col L from L3:L95 aligned with the relevant entry from 'Assessment'!L7:L130
I've tried simply changing to BYROW which does fill Col L but only returns a "#VALUE!" error.
My amended formula in 'SOA'!L3 is:
=BYROW('Assessment'!L7:CZ130,LAMBDA(column,TEXTJOIN(",",TRUE,FILTER('Assessment'!A7:A130,NOT(ISBLANK(column)))))).
Sorry for not explaining my use case properly; I incorrectly assumed that transposing the desired results would have little impact on the formula.
thanks in advance if anyone can help
=TRANSPOSE(BYCOL(Assessment!L7:CZ130,LAMBDA(column,TEXTJOIN(",",TRUE,FILTER(Assessment!A7:A130,NOT(ISBLANK(column)))))))I am unsure if i correctly understand how your data is actually laid out. I've tried to replicate the layout of the data and this formula could return the expected result.
Sheet "SOA":
- Once again, thanks very much for your assistance ! Transpose worked a treat.
As background, the formula is being used in a risk assessment spreadsheet whhich now details the controls selected per risk and the risk associated with each control. These provide a direct link between risk assessment and risk treatment/security controls and help support audit and assessment of the risk process.