Vlookup

[tracktor]

I have a list of 100 items of data

I want to be able to go down the list of items and select the ones that I want.

I then want to concatenate the items with a “, “ (comma and a space) between each one

I never know how many I will be selecting, it could range from 0 – 30 of the items.

I also need the order of the items concatenated in a specific order.

Let’s say the items are in column B from rows 1 - 100

Excel Workbook
	A	B
1	*	Item 1
2	*	Item 2
3	*	Item 3
4	*	Item 4
5	*	Item 5
6	*	Item 6
7	*	Item 7
8	*	Item 8
9	*	Item 9
10	*	Item 10
11	*	Item 11
12	*	Item 12
Sheet3

And so on.

Now I want to select 3 items out of the list Item 1, Item 3 and Item 5. So I go down the list and put a number in column "A" to correspond to the order I want the items concatenated in

Excel Workbook
	A	B
1	3	Item 1
2	*	Item 2
3	1	Item 3
4	*	Item 4
5	2	Item 5
6	*	Item 6
7	*	Item 7
8	*	Item 8
9	*	Item 9
10	*	Item 10
11	*	Item 11
12	*	Item 12
Sheet3

Now I want to concatenate the items I need like this:
Item 3, Item 5, and Item 1

I am using VLOOKUP function to extract and order the items But the problem I have is I never know how many if any items I will be selecting, or the order in which they are concatenated. So I created 30 VLOOKUP functions like this on another sheet and I get the data back like this:

A B
1 Item 3
2 Item 5
3 Item 1
4 #N/A
5 #N/A
6 #N/A
And so on until row 30

Now I have the items I need and in the order I want along with the rest of the #N/A's.

Because I never know how many items will be selected from 0 to 30, how do I create a formula that will just concatenate the items I need?

 

[Michaels]

Hi Tracktor !

Your message in not clear. Anyways, are you looking for something like this:

Excel Workbook
	A	B	C	D	E	F	G
1	No.	Items	Concatenated			Concantenated	No.
2	1	Item 10	1, Item 10			1, Item 10	1
3	2	Item 9	2, Item 9			2, Item 9	2
4	3	Item 8	3, Item 8			3, Item 8	3
5	4	Item 7	4, Item 7			4, Item 7	4
6	5	Item 6	5, Item 6			5, Item 6	5
7	6	Item 5	6, Item 5			6, Item 5	6
8	7	Item 4	7, Item 4			7, Item 4	7
9	8	Item 3	8, Item 3			8, Item 3	8
10	9	Item 2	9, Item 2			9, Item 2	9
11	10	Item 1	10, Item 1			10, Item 1	10
Sheet2

Excel 2007

Cell Formulas
Range	Formula
C2	=A2&", "&B2
C3	=A3&", "&B3
C4	=A4&", "&B4
C5	=A5&", "&B5
C6	=A6&", "&B6
C7	=A7&", "&B7
C8	=A8&", "&B8
C9	=A9&", "&B9
C10	=A10&", "&B10
C11	=A11&", "&B11
G2	=INDEX($A$2:$A$11,MATCH(F2,$C$2:$C$11,0))
G3	=INDEX($A$2:$A$11,MATCH(F3,$C$2:$C$11,0))
G4	=INDEX($A$2:$A$11,MATCH(F4,$C$2:$C$11,0))
G5	=INDEX($A$2:$A$11,MATCH(F5,$C$2:$C$11,0))
G6	=INDEX($A$2:$A$11,MATCH(F6,$C$2:$C$11,0))
G7	=INDEX($A$2:$A$11,MATCH(F7,$C$2:$C$11,0))
G8	=INDEX($A$2:$A$11,MATCH(F8,$C$2:$C$11,0))
G9	=INDEX($A$2:$A$11,MATCH(F9,$C$2:$C$11,0))
G10	=INDEX($A$2:$A$11,MATCH(F10,$C$2:$C$11,0))
G11	=INDEX($A$2:$A$11,MATCH(F11,$C$2:$C$11,0))

 

[tracktor]

No. I only may need 3 of the items in the list. not all of them. And not sure which ones.

Lets say that I want the data in cell B5, B1, B3 concatenated in that order. The rest of the items I do not need in my concatenation.

So with the data that you have the concatenation would be:

"Item 6, Item10, Item8"

I hope this helps

 

[Michaels]

Hello !

If you want any three items, then you will need 3 helper cells and rows, whcih would have data validation applied like this:

Excel Workbook
	A	B	C	D	E	F	G
1	Item 1	Item 1	Item 1
2	Item 2	Item 2	Item 2	List 1	List 2	List 3
3	Item 3	Item 3	Item 3	Data Validation Applied	Data Validation Applied	Data Validation Applied	Concatenated
4	Item 4	Item 4	Item 4	Item 6	Item 4	Item 7	Item 6, Item 4, Item 7
5	Item 5	Item 5	Item 5	Item 3	Item 3	Item 5	Item 3, Item 3, Item 5
6	Item 6	Item 6	Item 6	Item 6	Item 8	Item 10	Item 6, Item 8, Item 10
7	Item 7	Item 7	Item 7
8	Item 8	Item 8	Item 8
9	Item 9	Item 9	Item 9
10	Item 10	Item 10	Item 10
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G4	=D4&", "&E4&", "&F4
G5	=D5&", "&E5&", "&F5
G6	=D6&", "&E6&", "&F6

 

[Michaels]

Also, if you have just 1 list from which you want to pick your items, you can have data validation for the same list in 3 different cells and the fourth cell will concatenate your selection with comma and space.

Hope this helps.

 

[tracktor]

I got it. Here it is

Excel Workbook
	A	B	C	D	E
1	4	Item 11	1	Item 9	Item 9,
2	*	Item 5	2	Item 7	Item 7,
3	1	Item 9	3	Item 3	Item 3,
4	3	Item 3	4	Item 11	Item 11,
5	*	Item 1	5	*	*
6	*	Item 12	6	*	*
7	*	Item 6	7	*	*
8	*	Item 2	8	*	*
9	2	Item 7	9	*	*
10	*	Item 4	10	*	*
11	*	Item 8	11	*	*
12	*	Item 10	12	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	*	*	*	*	Concantenated
16	*	*	*	*	Item 9, *Item 7, *Item 3, *Item 11, * * * *
Sheet3

 

[tracktor]

Thanks Michaels for your efforts. I really appreciate it.

 

[Michaels]

Its is my pleasure Tracktor. You can also do the final concatenation with CONCATENATE formula but it won't give ", "...part. I dont really mean you can't, you CAN but thats a little bit cumbersome if you have huge amount of data housed in rows.

Anyways, thanks.

 

[tracktor]

Thanks again Shawn I will look at that as well