Combining Data from 3 Cells into 1

[jarrodexcel]

Hey guys, bit of a tricky one here and would love some help, will try explain as well as i can.

In the cells below i have A1-C3,

A1 tells me if the action has been completed, No means it hasn't and so no.

B1 tells me the device used

C3 tells me the product

The formula i am after will combine these 3 into the final result(Exhibit B), shown below the table (Exhibit A)

So if A1 Is Yes then i dont want any data for it as it is irrelevant. But if A1 is No i do and i would like this NO to become a "1x".


When the data comes in i need all No's and their corresponding data from the other two cells to combine. If there is a was a way to shorten the SmartDrive etc to just say SD or SW that would be amazing but no worries if not.

Any questions feel free to shoot ! Thankyou


(Exhibit A)
A1 B1 C3

No, Yes, No, Yes

SmartDrive,SmartDrive,SmartWheel,SmartStream

2999-Banana floor,2111-Pear Branch,1235-White apple,1211-Apple tree

(Exhibit B)
Final result -
1x SD2999-Banana floor, 1xSW1235-White apple

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	No, Yes, No, Yes	SmartDrive,SmartDrive,SmartWheel,SmartStream	2999-Banana floor,2111-Pear Branch,1235-White apple,1211-Apple tree
2
3	1x SD2999-Banana floor, 1x SW1235-White apple
Main

Cell Formulas
Range	Formula
A3	A3	="1x "&TEXTJOIN(", 1x ",,IF(TEXTSPLIT(A1,", ")="No",LEFT(REPLACE(TEXTSPLIT(B1,","),2,4,""),2)&TEXTSPLIT(C1,","),""))

 

[jarrodexcel]

How about

Fluff.xlsm
	A	B	C
1	No, Yes, No, Yes	SmartDrive,SmartDrive,SmartWheel,SmartStream	2999-Banana floor,2111-Pear Branch,1235-White apple,1211-Apple tree
2
3	1x SD2999-Banana floor, 1x SW1235-White apple
Main

Cell Formulas
Range	Formula
A3	A3	="1x "&TEXTJOIN(", 1x ",,IF(TEXTSPLIT(A1,", ")="No",LEFT(REPLACE(TEXTSPLIT(B1,","),2,4,""),2)&TEXTSPLIT(C1,","),""))

Hi Fluff !

this is almost perfect ! the only issue I'm having is when i download my data the A1 section has no spaces. So it goes No,Yes,No,Yes etc which causes the formula to fail. Any way to fix this or i will have to enter spaces between them all constantly ?


Update -

Don't worry ! i spotted the space in the formula and all works well now !!

Thank you again Fluff, you are my hero !!!!

 

[Fluff]

Glad to help & thanks for the feedback.