Conditional Concatenation

[RBusiness]

Hey folks,

I'm trying to make a conditional concatenation but am scratching my head on how to do it. Maybe you can help?

Basically, I need a concatenation that I can drag in Column E that looks for the value in column A, then, scans the column for an instance of the same value where there's alphanumeric characters/ has a value two columns over C1) and then a and then concatenates the value in where C1 appends D2, then D3, then D4 in the cell where the formula is.

The goal is to only concatenate when the values in A match and then combine C of the matching row with the content(s) of D in the same row.
I'm trying to NOT "Salty Cars" (D2 and C2) if that makes sense.

I know someone has the wizardry to do this one - but I just don't know how. What do you think?

	A	B	C	D	E
	P VALUE	S VALUE	Text	Text	Concatenation
1​	111​		Cars
2​	111​	113​		Small	Small Car
3​	111​	114​		Medium	Medium Car
4​	111​	115​		Big	Big Car
5​	222​		*******s
6​	222​	223​		Salty	Salty *******s
7​	222​	224​		Plain	Plain *******s
8​	222​	225​		Moldy	Moldy *******s

 

[StephenCrump]

If the column C values always occur above the column D values, then you could do this:

	A	B	C	D	E
1	111		Car
2	111	113		Small	Small Car
3	111	114		Medium	Medium Car
4	111	115		Big	Big Car
5	111	116			Car
6	222		Chips
7	222	223		Salty	Salty Chips
8	222	224		Plain	Plain Chips
9	222	225		Moldy	Moldy Chips
10	333	334	?	Good
11	333	335		Bad	Bad ?
12	333	333	Example
Sheet1

Cell Formulas
Range	Formula
E1:E11	E1	=IF(C1="",D1&" "&VLOOKUP(A1,A$1:C$12,3,),"")

The cells highlighted are where the data is not well-behaved. If this is a possibility, we could build in some additional checks, depending on what you wanted the output to be.

 

[RBusiness]

Thank you - this is great. I can't begin to tell you how helpful this is! I appreciate your beyond comprehension.