Index Match with concatenate

D

dazkool

New Member
Joined
Oct 30, 2017
Messages
6
Hi there!

First post here, but have been visiting now and again for hints and tips.

I'm responsible for organising our Xmas lunch this year so I have created a spreadsheet. Tab 1 has all the menu choices. It has the names of the attendees going down in Column B. In Columns C to O going across are the food options from the venue's menu.

Then I have populated the rows in these columns (C to O) with a "1" to indicate each attendee's food choice. There will also be blanks in each column where that particular food option has not been chose by an attendee.

What I want to do is create a simple food order sheet (in a separate tab) so I can hand to the venue. Excel will read down each column (C to O) on the Menu choices tab and where there is a 1 in that particular column (i.e. for the starters) to get the value of the attendee's name and paste it into the food order sheet. Obviously many people may choose the same starter, main & dessert - so for each type of starter, main and dessert I would like it to concatenate the values of each attendee who has ordered each type of food.

Does that make sense? I have tried various combinations of VLOOKUP and also Index Match statements, but I can't get it to work.

Please help!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
This works

Code: 
Sub k1()
l = 1
For i = 3 To 15
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
k = ""
For j = 2 To lastrow
If Cells(j, i) = 1 Then
k = k & Worksheets("Sheet1").Cells(j, 2) & ","
End If
Next j
If k <> "" Then
k = Left(k, Len(k) - 1)
Worksheets("Sheet2").Cells(l, 1) = Worksheets("Sheet1").Cells(1, i)
Worksheets("Sheet2").Cells(l, 2) = k
l = l + 1
End If
Next i
End Sub

Output on Sheet2
 
Upvote 0
How do I run this code? If I run it directly from the VBA Module thing, I get a run-time error 9 message.
 
Upvote 0
Subscript out of range could also suggest the worksheet doesnt exist.

I had input on Sheet1, output going to Sheet2

I selected Sheet1 under Sheet Objects instead of Modules
 
Upvote 0
Welcome to the MrExcel board!

If you have Excel 2016 through Office 365, could you use this?


Book1
BCDEFGH
1NameFood 1Food 2Food 3Food 4Food 5Food 6
2Name 111
3Name 2111
4Name 31
5Name 41111
6Name 511
Orders


Formula in B2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.


Book1
AB
1Munu ItemWho Ordered
2Food 1Name 1, Name 4
3Food 2Name 2, Name 5
4Food 3Name 1, Name 3, Name 4
5Food 4Name 5
6Food 5Name 2, Name 4
7Food 6Name 2, Name 4
Combined
Cell Formulas
RangeFormula
B2{=TEXTJOIN(", ",TRUE,IF(INDEX(Orders!$C$2:$H$6,0,ROWS(B$2:B2))=1,Orders!B$2:B$6,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

If you have Excel 2016 through Office 365, could you use this?

BCDEFGH
Name
Name 1
Name 2
Name 3
Name 4
Name 5

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Food 1[/TD]
[TD="align: center"]Food 2[/TD]
[TD="align: center"]Food 3[/TD]
[TD="align: center"]Food 4[/TD]
[TD="align: center"]Food 5[/TD]
[TD="align: center"]Food 6[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Orders​



Formula in B2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

AB
Munu ItemWho Ordered
Food 1Name 1, Name 4
Food 2Name 2, Name 5
Food 3Name 1, Name 3, Name 4
Food 4Name 5
Food 5Name 2, Name 4
Food 6Name 2, Name 4

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>
Combined​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(INDEX(Orders!$C$2:$H$6,0,ROWS(B$2:B2))=1,Orders!B$2:B$6,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Enter without the {} but confirm with Ctrl+Shift+Enter, not just Enter
If entered correctly, Excel will insert the {}[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

OK, so I found a TextJoin VB Code and have saved it as an Excel Add-in.

I've then copied your formula into the Food Orders sheet - obviously I have to amend the sheet names for this to work?

When I press CTRL+SHIFT+Enter, I get #Name being displayed......
 
Upvote 0
So this is the code I found - not sure if it need amending to my needs?
Rich (BB code): 
Public Function TEXTJOIN(Delimiter As String, Ignore_Empty As Boolean, ParamArray Text1() As Variant) As String
'PURPOSE: Replicates The Excel 2016 Function CONCAT
'SOURCE: www.TheSpreadsheetGuru.com

Dim RangeArea As Variant
Dim Cell As Range

'Loop Through Each Cell in Given Input
  For Each RangeArea In Text1
    If TypeName(RangeArea) = "Range" Then
      For Each Cell In RangeArea
        If Len(Cell.Value) <> 0 Or Ignore_Empty = False Then
          TEXTJOIN = TEXTJOIN & Delimiter & Cell.Value
        End If
      Next Cell
    Else
      'Text String was Entered
        If Len(RangeArea) <> 0 Or Ignore_Empty = False Then
          TEXTJOIN = TEXTJOIN & Delimiter & RangeArea
        End If
    End If
  Next RangeArea

TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)

End Function
 
Upvote 0
Can anyone please help with this?

Otherwise I'll just do it manually with a simple concatenate formula.....
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
What should I do in order to solve an inconsistent reference error when using INDEX/MATCH?
Replies
1
Views
355
numbernoodle
N
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
441
Anthony47
A
R
  • Question Question
Help please - COUNTA error
Replies
1
Views
102
StephenCrump
S
N
  • Question Question
Index Match after sorting
Replies
1
Views
648
Eric W
Eric W
M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
437
bobaol
B

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

Share this page

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
Back
Top