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
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
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?
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?
Last edited: