Help with Concatenation

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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?

If I understand your question correctly, just link the with ampersands.
Assuming we are talking about the first 11 columns...

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11

Rick

 

[Guest]

Thanks for your quick response, my fault, I believe I should rephrase the
question as it was unclear.
I have 250 entries in one sheet, each entry has 11 options going across,
each entry can pick at most 2 options out of the 11.
I would like to find a way to pick up and put on another sheet, only the 2
options selected for each entry.

 

[Rick Rothstein \(MVP - VB\)]

Help needed to concatenate 11 cells on same sheet, all of them in

If I understand your question correctly, just link the with ampersands.
Assuming we are talking about the first 11 columns...

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11

Well, the above formula is for concatenating the first 11 rows of a column,
which is not what you asked, was it? Try this formula assuming we are
talking about the first 11 columns (note, the technique is the same)...

=A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1

Rick

 

[Rick Rothstein \(MVP - VB\)]

I'm still having trouble visualizing your layout. Are all 2750 cells devoted
to options filled in with "something" and you are somehow designating the
two options per row to be selected (maybe background color change)? Or do
you have 11 columns devoted to options (perhaps with an option name in the
header) where you select (maybe by placing an "X" in the two columns) and
you want to pick up the selected cell's header name and concatenate those?
We are still talking about concatenating, right?

Rick

 

[Guest]

I have 250 people, each person has their own row. Each person has the option
of selecting 2 out of 11 options. The options are arranged horizontal going
across. E.g. Person 1 can pick option 1 and 3, person 2 can pick 4 and 11.
Person 3 can pick 5 and 9, etc.
I wanted to collapse the data, so I can get everything summarized into 3
columns, one for the person, one for first option picked and next column over
for second option picked.

Thanks again.

 

[Ron Rosenfeld]

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

 

[Guest]

Great answer, will go and try it now. Thanx

 

[Guest]

Hi Rick,
Is there a way to concatenate a cell range of 500 cells into 1 cell without
macros? I thought I heard there was also a limit to how many cells could be
included in the formula so assume there is a work around for that?

ideas?

thanks in advance, jane