On Fri, 17 Aug 2007 09:02:04 -0700, Tabit
wrote:


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