Vlookup

tracktor

Board Regular
Joined
Sep 13, 2009
Messages
62
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
AB
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
AB
13Item 1
2*Item 2
31Item 3
4*Item 4
52Item 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?
 
Last edited:

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Michaels

Active Member
Joined
Apr 2, 2009
Messages
404
Hi Tracktor !

Your message in not clear. Anyways, are you looking for something like this:
Excel Workbook
ABCDEFG
1No.ItemsConcatenatedConcantenatedNo.
21Item 101, Item 101, Item 101
32Item 92, Item 92, Item 92
43Item 83, Item 83, Item 83
54Item 74, Item 74, Item 74
65Item 65, Item 65, Item 65
76Item 56, Item 56, Item 56
87Item 47, Item 47, Item 47
98Item 38, Item 38, Item 38
109Item 29, Item 29, Item 29
1110Item 110, Item 110, Item 110
Sheet2
Excel 2007
Cell Formulas
RangeFormula
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

Board Regular
Joined
Sep 13, 2009
Messages
62
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

Active Member
Joined
Apr 2, 2009
Messages
404
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
ABCDEFG
1Item 1Item 1Item 1
2Item 2Item 2Item 2List 1List 2List 3
3Item 3Item 3Item 3Data Validation AppliedData Validation AppliedData Validation AppliedConcatenated
4Item 4Item 4Item 4Item 6Item 4Item 7Item 6, Item 4, Item 7
5Item 5Item 5Item 5Item 3Item 3Item 5Item 3, Item 3, Item 5
6Item 6Item 6Item 6Item 6Item 8Item 10Item 6, Item 8, Item 10
7Item 7Item 7Item 7
8Item 8Item 8Item 8
9Item 9Item 9Item 9
10Item 10Item 10Item 10
Sheet1
Excel 2007
Cell Formulas
RangeFormula
G4=D4&", "&E4&", "&F4
G5=D5&", "&E5&", "&F5
G6=D6&", "&E6&", "&F6
 

Michaels

Active Member
Joined
Apr 2, 2009
Messages
404

ADVERTISEMENT

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

Board Regular
Joined
Sep 13, 2009
Messages
62

ADVERTISEMENT

Thanks Michaels for your efforts. I really appreciate it.
 

Michaels

Active Member
Joined
Apr 2, 2009
Messages
404
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,265
Messages
5,595,181
Members
413,974
Latest member
LB_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top