Advanced concatenate or similar

[thedeadzeds]

Hi Guys,

Column A contains data based on group. For example, A3 to A8 are all part of New Vehicles. I need to concatenate these if column B shows Y so I can split out these groups.

For example, the formula in C3 to C8 is =IF(B3="Y",CONCATENATE($A$2," ",A3),A3) whilst the formula in C11 to C16 is =IF(B11="Y",CONCATENATE($A$10," ",A11),A11) and so on

At the moment I need to change the formula as per above. The List in column A has 1000 entries so I'm hoping this can be done another way.

Hope this makes sense

Type	Y or No	Concatenate
New Vehicles
Vehicles Sold	y	New Vehicles Vehicles Sold
Vehicles Ordered	y	New Vehicles Vehicles Ordered
Vehicle Sales	y	New Vehicles Vehicle Sales
Vehicles Sold GP (VSB)	y	New Vehicles Vehicles Sold GP (VSB)
Vehicles Sold GP	y	New Vehicles Vehicles Sold GP
Avg Gross Profit/Vehicle	y	New Vehicles Avg Gross Profit/Vehicle
Motability
Vehicles Sold	y	Motability Vehicles Sold
Vehicles Ordered	y	Motability Vehicles Ordered
Vehicles Sold Sales	y	Motability Vehicles Sold Sales
Motability Vehicles Sold GP (VSB)	y	Motability Motability Vehicles Sold GP (VSB)
Vehicles Sold GP	y	Motability Vehicles Sold GP
Avg Gross Profit/Vehicle	y	Motability Avg Gross Profit/Vehicle
Demo/Wholesale
Demo Conv & Wholesale Transf Vehicles Sold	y	Demo/Wholesale Demo Conv & Wholesale Transf Vehicles Sold
Vehicles Ordered	y	Demo/Wholesale Vehicles Ordered
Vehicle Sales	y	Demo/Wholesale Vehicle Sales
Vehicles Sold GP (VSB)	y	Demo/Wholesale Vehicles Sold GP (VSB)
Vehicles Sold GP	y	Demo/Wholesale Vehicles Sold GP
Avg Gross Profit/Vehicle	y	Demo/Wholesale Avg Gross Profit/Vehicle
Total Vehicle Sales	n	Total Vehicle Sales
Total Vehicle Gross Profit	n	Total Vehicle Gross Profit

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Type	Y or No	Concatenate
2	New Vehicles
3	Vehicles Sold	y	New Vehicles Vehicles Sold
4	Vehicles Ordered	y	New Vehicles Vehicles Ordered
5	Vehicle Sales	y	New Vehicles Vehicle Sales
6	Vehicles Sold GP (VSB)	y	New Vehicles Vehicles Sold GP (VSB)
7	Vehicles Sold GP	y	New Vehicles Vehicles Sold GP
8	Avg Gross Profit/Vehicle	y	New Vehicles Avg Gross Profit/Vehicle
9
10	Motability
11	Vehicles Sold	y	Motability Vehicles Sold
12	Vehicles Ordered	y	Motability Vehicles Ordered
13	Vehicles Sold Sales	y	Motability Vehicles Sold Sales
14	Motability Vehicles Sold GP (VSB)	y	Motability Motability Vehicles Sold GP (VSB)
15	Vehicles Sold GP	y	Motability Vehicles Sold GP
16	Avg Gross Profit/Vehicle	y	Motability Avg Gross Profit/Vehicle
17
18	Demo/Wholesale
19	Demo Conv & Wholesale Transf Vehicles Sold	y	Demo/Wholesale Demo Conv & Wholesale Transf Vehicles Sold
20	Vehicles Ordered	y	Demo/Wholesale Vehicles Ordered
21	Vehicle Sales	y	Demo/Wholesale Vehicle Sales
22	Vehicles Sold GP (VSB)	y	Demo/Wholesale Vehicles Sold GP (VSB)
23	Vehicles Sold GP	y	Demo/Wholesale Vehicles Sold GP
24	Avg Gross Profit/Vehicle	y	Demo/Wholesale Avg Gross Profit/Vehicle
25
26	Total Vehicle Sales	n	Total Vehicle Sales
27	Total Vehicle Gross Profit	n	Total Vehicle Gross Profit
28
Main

Cell Formulas
Range	Formula
C2:C27	C2	=IF(B2="","",IF(B2="n",A2,LOOKUP(2,1/(B$1:B1=""),A$1:A1)&" "&A2))

 

[thedeadzeds]

How about

Fluff.xlsm
	A	B	C
1	Type	Y or No	Concatenate
2	New Vehicles
3	Vehicles Sold	y	New Vehicles Vehicles Sold
4	Vehicles Ordered	y	New Vehicles Vehicles Ordered
5	Vehicle Sales	y	New Vehicles Vehicle Sales
6	Vehicles Sold GP (VSB)	y	New Vehicles Vehicles Sold GP (VSB)
7	Vehicles Sold GP	y	New Vehicles Vehicles Sold GP
8	Avg Gross Profit/Vehicle	y	New Vehicles Avg Gross Profit/Vehicle
9
10	Motability
11	Vehicles Sold	y	Motability Vehicles Sold
12	Vehicles Ordered	y	Motability Vehicles Ordered
13	Vehicles Sold Sales	y	Motability Vehicles Sold Sales
14	Motability Vehicles Sold GP (VSB)	y	Motability Motability Vehicles Sold GP (VSB)
15	Vehicles Sold GP	y	Motability Vehicles Sold GP
16	Avg Gross Profit/Vehicle	y	Motability Avg Gross Profit/Vehicle
17
18	Demo/Wholesale
19	Demo Conv & Wholesale Transf Vehicles Sold	y	Demo/Wholesale Demo Conv & Wholesale Transf Vehicles Sold
20	Vehicles Ordered	y	Demo/Wholesale Vehicles Ordered
21	Vehicle Sales	y	Demo/Wholesale Vehicle Sales
22	Vehicles Sold GP (VSB)	y	Demo/Wholesale Vehicles Sold GP (VSB)
23	Vehicles Sold GP	y	Demo/Wholesale Vehicles Sold GP
24	Avg Gross Profit/Vehicle	y	Demo/Wholesale Avg Gross Profit/Vehicle
25
26	Total Vehicle Sales	n	Total Vehicle Sales
27	Total Vehicle Gross Profit	n	Total Vehicle Gross Profit
28
Main

Cell Formulas
Range	Formula
C2:C27	C2	=IF(B2="","",IF(B2="n",A2,LOOKUP(2,1/(B$1:B1=""),A$1:A1)&" "&A2))

Thank you fluff this is amazing. I would love to be able to dissect that and understand it

 

[Fluff]

Glad to help & thanks for the feedback.