How to do a formula like subtotal but with text

[mfkdewmtstriker]

Here’s my some data 9 rows out of 2,000. I need to combine all the data for "1" into one cell, so it looks like 1 apple, back, cat. So that I can in return vlookup "1" and get apple, back, cat. I think of it as "subtotal" for text. So a every change in number "sum/combine" data. The data can be separated with a comma or whatever so long as it is on the same cell.

number data

1 apple

1 back

1 cat

2 dog

2 eagle

3 mug

3 pot

3 oven


Thank you for any help or ideas on how to solve this problem.

 

[Hui]

Mfkdewmtstriker


Firstly, Welcome to the Chandoo.org Forums


You may want to read this post as well as look through all the comments attached at the end of the post

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/


One of my comments refers to a Concatif() UDF which will do the job

 

[mfkdewmtstriker]

Yes, I saw that. I might have been unclear. But I have(table above)column A=numbers and column B=Data. I need to have a formula like, if any Number in the range of A1:A300 are the same then concat all B1:B300 into one cell. I want to do this for the whole worksheet vs manually concat myself. Does this help?

 

[keymaster]

A simple option is to sort your list on column A (the number) and then use concat for each of the numbers one time.


You can even pass an OFFSET range to CONCAT udf so that it would just concatenate all the items with 1 and give output.

 

[Sajan]

Hello Mfkdewmtstriker,


Assuming that your numbers are in sorted, ascending order (i.e. all 1s together, then all 2s together, etc.), you could use the following formula based approach:


Assuming your sample data is setup as follows:

[pre]

Numbers	name	Concat Values
1	apple	apple
1	back	apple,back
1	cat	apple,back,cat
2	dog	dog
2	eagle	dog,eagle
3	mug	mug
3	pot	mug,pot
3	oven	mug,pot,oven
Where numbers is in column A, names in column B, with data starting in row 2.


Put the following formula into cell C2, and copy down to the last row:

=IF(A2=A1,CONCATENATE(C1,",", B2),B2)


Then, in a separate area, you can lookup the concatenated text as follows:

Number to Lookup	Concatenated Text
1	                apple,back,cat
2	                dog,eagle
3	                mug,pot,oven

[/pre]
You can key in the numbers to lookup manually as I have done, or use a formula.

For the concatenated text, use the following formula:

=INDEX(ConcatValues,MATCH(F2,NumbersList))

where F2 refers to number 1 above.

NumbersList refers to your range of numbers (A1:A9 in the sample above)

ConcatValues refers to your helper column (C2:C9 in the sample above)


Cheers,

Sajan.

 

[mfkdewmtstriker]

Chandoo,

I never use offset set so I wasn’t sure how they work.


Sajan,


that seems to how work! When you do the =INDEX(ConcatValues,MATCH(F2,NumbersList))

does it always grab the last value and return that. So in the above table when i =INDEX(ConcatValues,MATCH(F2,NumbersList))for the number 1 and i have 50 number ones, i will get the 50th concatvalue for 1. right?

 

[Sajan]

That is correct. it locates the largest number smaller than the number being sought. since the numbers are sorted, MATCH returns thelocation of the last match.