Excel Formula to fill out a form

[mindmage7]

Hello, I have created a workbook and have been tinkering with it for a while. The spreadsheet I have been working on uses one sheet to enter in information for fire alarm systems. I put in the address and the number of the different devices and there are multiple sheets in the workbook that do the calculations. The issue I am having is with the form. The form requires a summary of the installed devices. What I am trying to do is check a cell for a value greater than 0. If it is true then get the amount and the name of the device. If it is false then skip it. My issue is I need the formula to continue for each Device. I set up a carriage return and spacing to get the list you see on the form below. I need to not include items with a 0 count. I believe this is simple but I am not sure how to do it or even what to look for. I was trying If statements and was looking for an and, or but I do not know how to use them. There may be other better ways to do it but I am at a loss. I read on this forum that someone suggested just ask first so I am sure this will be easy for some.

I am including a picture but I can create a mini example if needed.

Thanks,

Tom

 

[Alex Blakenburg]

I am not clear if you had multiple criteria or not, so see if this gives you any ideas.
I have included a criteria to only Pick up Product Codes starting with "O".

Book1
	A	B	C
19	Code Begins with	Result
20	O	1 Smoke Detector 9 Smoke Heat 12 Smoke Heat CO Detector
21
22
23
24
25
26
27
28	Device	Code	Quantity
29	Pull Station	HMS-D
30	Pull Station	XMS-D	4
31	Smoke Detector	OP921	1
32	Heat Detector	HI921	6
33	Smoke Heat	OOH941	9
34	Smoke Heat CO Detector	OOHC941	12
35	Single Input Module-Isolator	XTRI-S/M	1
36	Smoke Other Item	OOX-XX
Sheet1

Cell Formulas
Range	Formula
B20	B20	=TEXTJOIN(CHAR(10),TRUE, (FILTER($C$29:$C$36,(LEFT($B$29:$B$36,LEN(A20))=A20)*($C$29:$C$36<>0),"") & " " &FILTER($A$29:$A$36,(LEFT($B$29:$B$36,LEN(A20))=A20)*($C$29:$C$36<>0),"")))

 

[mindmage7]

Sorry I didn't reply sooner. I have finally gotten a chance to look at this. I have been looking at how the solution you provided works and it definitely will work. You even took it farther than necessary. The form I am using does not separate items based on the starting part number so that was a bonus. With what you provided I am setting up a sheet that totals the devices together and uses that to fill out the form. The area for my data is 3x2, that is I have two cells side by side and each can hold 3 lines. I am playing around with what you provided so I can understand exactly how it works.

Thanks,

Tom

 

[Alex Blakenburg]

No problem. Glad you found it helpful.
If you want to read up on filtering on part of a word you might find this helpful.
How To Use Wildcards/Partial Match With Excel's Filter Function
If you have need me to explain it a bit more then just let me know.