return multiple matching values into one cell based on multiple criteria in Excel

[MMM_84]

Hi all,
Need a way how to return multiple values into one cell based on multiple criteria. I was trying to use textjoin (char 10) and then index match, but it seems smth is wrong....
maybe you could find a solution either the function or vba .....

Based on "Reference" (Column B), "Type" (Column D) and "Criteria" (column F), return matching "ID number", "Period" and "Comment" into one cell in a separate workbook. See the attachments.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	ID number	Reference	Period	Type	Comment	Criteria	etc.1	etc.2	etc.3
2	15645441	B2	06-05-99	Apple	Out of scope	ABC1
3	1545633	B2	07-05-99	Carrot	In Stock	ABC3
4	7863896	B3	08-05-99	Cucumber	Shortage	ABC4
5	741526	B4	09-05-99	Potato	Abundance	ABC5
6	896544	B5	10-05-99	Peach	Out of scope	ABC1
7	9865436	B6	11-05-99	Strawberry	In Stock	ABC2
8	68986546	B1	12-05-99	Blueberry	In Stock	ABC7
9	68986547	B2	13-05-99	Peach	Out of scope	ABC1
10	68986548	B3	14-05-99	Tomato	Out of scope	ABC3
11	68986549	B4	15-05-99	Apple	ABC	ABC4
12	68986550	B5	16-05-99	Carrot	Abundance	ABC5
13	68986551	B1	17-05-99	Apple	In Stock	ABC1
14	68986552	B3	18-05-99	Apple	In Stock	ABC2
15	68986553	B3	19-05-99	Potato	Abundance	ABC7
16	68986554	B4	20-05-99	Cucumber	Out of scope	ABC5
17	68986555	B5	21-05-99	Blueberry	Abundance	ABC4
main

Book1.xlsx
	A	B	C	D	E
1	Type	Apple
2	Reference	B1	B2	B3	B4
3	Criteria
4	ABC1	ID Number: 68986551 Period: 17-05-1999 Comment: In Stock	ID Number: 15645441 Period: 06-05-1999 Comment: Out of scope
5	ABC2			ID Number: 68986552 Period: 18-05-1999 Comment: In Stock
6	ABC3
7	ABC4				ID Number: 68986549 Period: 15-05-1999 Comment: ABC
8	ABC5
9	ABC6
10	ABC7
11	ABC8
12	ABC9
13	ABC10
Apple

 

[etaf]

couple of questions ,

into one cell in a separate workbook.

1) is this a new WORKBOOK or into a new WorkSheet in the same WorkBook ?

I was trying to use textjoin (char 10)

2) are you still using version 2016 as in your profile ?
Textjoin didnt get released until 2019 version according to MS reference site

 

[MMM_84]

couple of questions ,

1) is this a new WORKBOOK or into a new WorkSheet in the same WorkBook ?

2) are you still using version 2016 as in your profile ?
Textjoin didnt get released until 2019 version according to MS reference site

1 - it's a workbook, but if not possible, then worksheet would work as well
2 - MS 365

 

[MMM_84]

Hi all,
Need a way how to return multiple values into one cell based on multiple criteria. I was trying to use textjoin (char 10) and then index match, but it seems smth is wrong....
maybe you could find a solution either the function or vba .....

Based on "Reference" (Column B), "Type" (Column D) and "Criteria" (column F), return matching "ID number", "Period" and "Comment" into one cell in a separate workbook. See the attachments.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	ID number	Reference	Period	Type	Comment	Criteria	etc.1	etc.2	etc.3
2	15645441	B2	06-05-99	Apple	Out of scope	ABC1
3	1545633	B2	07-05-99	Carrot	In Stock	ABC3
4	7863896	B3	08-05-99	Cucumber	Shortage	ABC4
5	741526	B4	09-05-99	Potato	Abundance	ABC5
6	896544	B5	10-05-99	Peach	Out of scope	ABC1
7	9865436	B6	11-05-99	Strawberry	In Stock	ABC2
8	68986546	B1	12-05-99	Blueberry	In Stock	ABC7
9	68986547	B2	13-05-99	Peach	Out of scope	ABC1
10	68986548	B3	14-05-99	Tomato	Out of scope	ABC3
11	68986549	B4	15-05-99	Apple	ABC	ABC4
12	68986550	B5	16-05-99	Carrot	Abundance	ABC5
13	68986551	B1	17-05-99	Apple	In Stock	ABC1
14	68986552	B3	18-05-99	Apple	In Stock	ABC2
15	68986553	B3	19-05-99	Potato	Abundance	ABC7
16	68986554	B4	20-05-99	Cucumber	Out of scope	ABC5
17	68986555	B5	21-05-99	Blueberry	Abundance	ABC4
main

Book1.xlsx
	A	B	C	D	E
1	Type	Apple
2	Reference	B1	B2	B3	B4
3	Criteria
4	ABC1	ID Number: 68986551 Period: 17-05-1999 Comment: In Stock	ID Number: 15645441 Period: 06-05-1999 Comment: Out of scope
5	ABC2			ID Number: 68986552 Period: 18-05-1999 Comment: In Stock
6	ABC3
7	ABC4				ID Number: 68986549 Period: 15-05-1999 Comment: ABC
8	ABC5
9	ABC6
10	ABC7
11	ABC8
12	ABC9
13	ABC10
Apple

I have managed to solve it with textjoin and filter function, but was wondering what would be a VBA code for this