Vlookup and Concatenate

[Johnnymartin]

Hi All,

Would greatly appreciate your help on a project I'm doing to help out a charity, I'm trying to upload their countywide Fire Risk assessments / training certs and other info but to be able to get it in a system I need the data in one cell (Column I in sheet1)

Current Structure:

Sheet1:

One line for each entry with an identifiable unique code, column I is where I need the solution to put the text into one cell:

Data structure:

Identifier	B	C	D	E	F	G	H	I
CP 1	N/A	N/A	N/A	N/A	N/A	N/A	N/A	FRA John Smith FRA James Bland FRA Jim Smith
CP 2	N/A	N/A	N/A	N/A	N/A	N/A	N/A	MHT John Smith MHT James Bland MHT Jim Smith

Sheet 2:

Identifier	B	C	D	E	F	G
CP 1	N/A	N/A	N/A	N/A	N/A	FRA John Smith
CP 1	N/A	N/A	N/A	N/A	N/A	FRA James Bland
CP 1	N/A	N/A	N/A	N/A	N/A	FRA Jim Smith
CP 2	N/A	N/A	N/A	N/A	N/A	MHT John Smith
CP 2	N/A	N/A	N/A	N/A	N/A	MHT James Bland
CP 2	N/A	N/A	N/A	N/A	N/A	MHT Jim Smith

I just need the solution to Vlookup the data from Sheet 2 in Cell G for each unique identifier into Sheet 1 in column I for the same identifier if this makes sense?!

Many thanks in advance

Jonathan

 

[Fluff]

For 365 how about

=TEXTJOIN(", ",,FILTER(Sheet2!$G$2:$G$1000,Sheet2!$A$2:$A$1000=A2))

 

[Johnnymartin]

For 365 how about

=TEXTJOIN(", ",,FILTER(Sheet2!$G$2:$G$1000,Sheet2!$A$2:$A$1000=A2))

That isn't working for me it's saying external connections are blocked on this laptop.

 

[Fluff]

What is the name of the sheet? If not Sheet2 did you change the sheet name in the formula?

 

[Johnnymartin]

Thank you so much Fluff, my apologies for the late response, I'd changed the sheet without realising I'd done it!

That works perfectly, really appreciate your help with that they're so happy!

Have a great rest of the week

Jonathan

 

[Fluff]

You're welcome & thanks for the feedback.