Forum Discussion
how can i conditionally move text? Probably IF/THEN
I have text in different columns essentially data is spread and I want to consolidate (dropping blanks). I have column A with numbers that tell me which other columns have text. For example
- A=1 means there is text in D while E and F are blank.
- A=2 means there is text in E while D and F are blank
- A=3 means there is text in F while D and E are blank
I want a move any text that exists for D, E, or F into column M. There is never a situation where there is text in two of D, E, or F.
I think IF(A2=1,???, IF(A2=2, ????,IF(A2=3,????,"NA")) should work, but I am not sure what would replace the ???? to move the text.
Thanks in advance.
Since on;y one of the cells in columns D to F will be filled, you can use the following formula in M2:
=CONCATENATE(D2,E2,F2)
or if you have Excel in Office 2019 or Microsoft 365:
=CONCAT(D2:F2)
Fill down.
4 Replies
Your convention for the values in column A also makes it possible to look up the data up using INDEX
=INDEX(Data, {1;2;3}, A)
How you generate the row numbers as whether you return the result row by row or as a single array will be influenced by the version of Excel you are developing for.
- Thanks! a new function for me.
Since on;y one of the cells in columns D to F will be filled, you can use the following formula in M2:
=CONCATENATE(D2,E2,F2)
or if you have Excel in Office 2019 or Microsoft 365:
=CONCAT(D2:F2)
Fill down.
- Thanks! this worked.
