Help needed to concatenate 11 cells on same sheet, all of them in the same
row into two.
Of the 11 cells, only max of 2 in each row are filled out, others are left
blank.
How can I concatenate these 11 cells into 2 for each row?
Thanx
Assuming your 11 cells are in columns B:L, the following will concatenate the
two cells with entries:
=INDEX(A1:L1,1,LARGE(NOT(ISBLANK(B1:L1))*COLUMN(B1:L1),2))&
INDEX(A1:L1,1,LARGE(NOT(ISBLANK(B1:L1))*COLUMN(B1:L1),1))
This is an array formula and must be entered with by holding down <ctrl><shift>
while hittinge <enter>. Excel will place braces {...} around the formula.
If you want the two results in two separate cells, then the formula for the
first cell is the part before the concatenation operator (&) and the formula
for the second cell the part after. The formulas must still be entered with
<ctrl><shift><enter>.
--ron