Index Match with concatenate

[dazkool]

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!

 

[Special-K99]

This works

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

 

[dazkool]

How do I run this code? If I run it directly from the VBA Module thing, I get a run-time error 9 message.

 

[Special-K99]

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

 

[Peter_SSs]

Welcome to the MrExcel board!

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

Book1
	B	C	D	E	F	G	H
1	Name	Food 1	Food 2	Food 3	Food 4	Food 5	Food 6
2	Name 1	1		1
3	Name 2		1			1	1
4	Name 3			1
5	Name 4	1		1		1	1
6	Name 5		1		1
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
	A	B
1	Munu Item	Who Ordered
2	Food 1	Name 1, Name 4
3	Food 2	Name 2, Name 5
4	Food 3	Name 1, Name 3, Name 4
5	Food 4	Name 5
6	Food 5	Name 2, Name 4
7	Food 6	Name 2, Name 4
Combined

Cell Formulas
Range	Formula
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.

 

[dazkool]

No - I have Excel 2013 here unfortunately...

Ideally I want to do this without an VB code.

 

[dazkool]

Welcome to the MrExcel board!

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

	B	C	D	E	F	G	H
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.

	A	B
Munu Item	Who Ordered
Food 1	Name 1, Name 4
Food 2	Name 2, Name 5
Food 3	Name 1, Name 3, Name 4
Food 4	Name 5
Food 5	Name 2, Name 4
Food 6	Name 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]

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......

 

[dazkool]

So this is the code I found - not sure if it need amending to my needs?

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

 

[dazkool]

Can anyone please help with this?

Otherwise I'll just do it manually with a simple concatenate formula.....