Apr 03 2021 09:03 PM
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
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.
Apr 04 2021 12:21 AM
SolutionSince 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.
Apr 04 2021 01:27 AM
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.
Apr 04 2021 06:48 AM
Apr 04 2021 12:21 AM
SolutionSince 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.