If statement that can search for a specific value and textjoin data from another column in a row on another sheet

[bd1985]

Hi,

I am completely new to this so apologies if there is an easy answer or maybe you can point me in the right direction.
Basically I have 2 sheets that have data HW Sheet and Components Sheet. I want to pull data from rows based on a match from Column D of the Components Sheet to Column D of the HW sheet where there is a match and textjoined with line breakups in the same cell.

* HW Sheet - Column A is the main value we want to look for on Column A of the Components Sheet (1.0, 2.0 etc)
* If there are matches (usually multiple) then I want to pull data from Column E where it's row has a match based on column A of the components sheet, broken up line by line using TEXTJOIN(CHAR(10) in Column D of HW Sheet

I hope I have explained this well enough, but have also provided pictures. I have tried various types of textjoin, if and isnumber but completely lost.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See how something like this goes. I have assumed that column A of both sheets is formatted Text.

bd1985.xlsm
	A	B	C	D	E
1
2	1.0				1x SR
3	1.0				1x MP
4	1.0				1x CB
5	1.0				1x TS
6	2.0				3x BP
7	2.0				2x BBQP
8	2.0				1x HC
9	3.0				5x RS
10	3.0				2x HC
11	3.0				4x BW
Components

bd1985.xlsm
	A	B	C	D
1
2	1.0			1x SR 1x MP 1x CB 1x TS
3	1.0.1
4	1.5.1
5	2.0			3x BP 2x BBQP 1x HC
6	3.0			5x RS 2x HC 4x BW
BW

Cell Formulas
Range	Formula
D2:D6	D2	=TEXTJOIN(CHAR(10),,FILTER(Components!E$2:E$100,Components!A$2:A$100=A2,""))

 

[Legacy 143009]

Basically I have 2 sheets that have data HW Sheet and Components Sheet. I want to pull data from rows based on a match from Column D of the Components Sheet to Column D of the HW sheet where there is a match and textjoined with line breakups in the same cell.

Here you say Column D

* If there are matches (usually multiple) then I want to pull data from Column E where it's row has a match based on column A of the components sheet, broken up line by line using TEXTJOIN(CHAR(10) in Column D of HW Sheet

Here you say Column E

I am confused.

 

[Fluff]

Col D is the for the match, col E is where to pull the data from.

 

[Legacy 143009]

I thought Column A to be matched (1.0, 2.0...). My English is getting rusty with age.

 

[Fluff]

My English is getting rusty with age.

Nope I just read what you had quoted, rather the the op & misunderstood what it was saying. It should indeed be From col E To col D.

 

[bd1985]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See how something like this goes. I have assumed that column A of both sheets is formatted Text.

bd1985.xlsm
	A	B	C	D	E
1
2	1.0				1x SR
3	1.0				1x MP
4	1.0				1x CB
5	1.0				1x TS
6	2.0				3x BP
7	2.0				2x BBQP
8	2.0				1x HC
9	3.0				5x RS
10	3.0				2x HC
11	3.0				4x BW
Components

bd1985.xlsm
	A	B	C	D
1
2	1.0			1x SR 1x MP 1x CB 1x TS
3	1.0.1
4	1.5.1
5	2.0			3x BP 2x BBQP 1x HC
6	3.0			5x RS 2x HC 4x BW
BW

Cell Formulas
Range	Formula
D2:D6	D2	=TEXTJOIN(CHAR(10),,FILTER(Components!E$2:E$100,Components!A$2:A$100=A2,""))

Thank you so much Peter_SSs

This works perfectly! I will remember XL2BB for next time to make it easier for everyone. Apologies to others on the thread if my request wasn't clear, I may have over thought my explanation and may have caused confusion.

Thank you again!!!

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.