If column has number then return string. If not then skip

[PuntingJawa]

If Column B has a specified number I would like to string together a series of cells into a Cell G through and H. If the value isn't there then I would like it to skip. However, I would like it to not skip cells.
Example.
=IF(B1=2,"TheirP/N "&D5&" Our P/N "&E5&" Serial # "&C5,"")
So, if B1 is equal to 2 then return "TheirP/N " and D5 value and " Our P/N " and E5 Value and "Serial# " and C5 Value.
This is what I have so far. What I am trying to do is make it to when I place this in G1 that it does not leave blank cells. Is this possible?

 

[NateSC]

It appears this should work. What problems or errors are you getting?

 

[PuntingJawa]

It's not so much an error as it is that the list is 400 lines. #1 is in cell G and #2 in H. So the string bounces back and forth between the cells and makes it hard to print out.
I was hoping there was a way to formulate this where it placed all the data on the topmost cell in the column.

Example. If Cells B1 through B5 had #1 and B6 through B10 had #2 then G1 through G5 would show the data string for #1's data and H1 through H5 would show the data string of #2 without skipping cells. This is what I want it to do if it's possible.
Instead, with my formula as is, it currently places #1 data to G1 through G5 an #2's data from H6 through H10.

 

[NateSC]

Can you sort based on increasing numerical values in Column B? That should do what you are asking for if I understand the problem.

 

[PuntingJawa]

Very true, I was just hoping to automate it by having it populate to another sheet.

 

[NateSC]

Assuming your data in on Sheet1 and you want the data on Sheet2, I think this should get you close:

Book1
	A	B	C	D	E	F	G	H
1
2		1	7	12	17			TheirP/N 12 Our P/N 17 Serial # 7
3		2	8	13	18		TheirP/N 13 Our P/N 18 Serial # 8
4		1	9	14	19			TheirP/N 14 Our P/N 19 Serial # 9
5		2	10	15	20		TheirP/N 15 Our P/N 20 Serial # 10
6		2	11	16	21		TheirP/N 16 Our P/N 21 Serial # 11
7		1	12	17	22			TheirP/N 17 Our P/N 22 Serial # 12
8		2	13	18	23		TheirP/N 18 Our P/N 23 Serial # 13
9		1	13	18	23			TheirP/N 18 Our P/N 23 Serial # 13
10
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IF(B2=2,"TheirP/N "&D2&" Our P/N "&E2&" Serial # "&C2,"")
H2:H9	H2	=IF(B2=1,"TheirP/N "&D2&" Our P/N "&E2&" Serial # "&C2,"")

Book1
	G	H
2	TheirP/N 13 Our P/N 18 Serial # 8	TheirP/N 12 Our P/N 17 Serial # 7
3	TheirP/N 15 Our P/N 20 Serial # 10	TheirP/N 14 Our P/N 19 Serial # 9
4	TheirP/N 16 Our P/N 21 Serial # 11	TheirP/N 17 Our P/N 22 Serial # 12
5	TheirP/N 18 Our P/N 23 Serial # 13	TheirP/N 18 Our P/N 23 Serial # 13
6
Sheet2

Cell Formulas
Range	Formula
G2:H5	G2	=FILTER(Sheet1!G:G,Sheet1!G:G<>"")
Dynamic array formulas.

H2 has the formula =FILTER(Sheet1!H:H,Sheet1!H:H<>"")
(not sure why it didn't show up above)

 

[PuntingJawa]

Perfect! Thank you very much!

 

[Peter_SSs]

You could do it directly without the helper columns.

23 05 05.xlsm
	B	C	D	E
1
2	1	7	12	17
3	2	8	13	18
4	1	9	14	19
5	2	10	15	20
6	2	11	16	21
7	1	12	17	22
8	2	13	18	23
9	1	13	18	23
10
Sheet3

23 05 05.xlsm
	G	H
1
2	TheirP/N 13 Our P/N 18 Serial # 8	TheirP/N 12 Our P/N 17 Serial # 7
3	TheirP/N 15 Our P/N 20 Serial # 10	TheirP/N 14 Our P/N 19 Serial # 9
4	TheirP/N 16 Our P/N 21 Serial # 11	TheirP/N 17 Our P/N 22 Serial # 12
5	TheirP/N 18 Our P/N 23 Serial # 13	TheirP/N 18 Our P/N 23 Serial # 13
6
Sheet4

Cell Formulas
Range	Formula
G2:G5	G2	=FILTER("TheirP/N "&Sheet3!D2:D100&" Our P/N "&Sheet3!E2:E100&" Serial # "&Sheet3!C2:C100,Sheet3!B2:B100=2,"")
H2:H5	H2	=FILTER("TheirP/N "&Sheet3!D2:D100&" Our P/N "&Sheet3!E2:E100&" Serial # "&Sheet3!C2:C100,Sheet3!B2:B100=1,"")
Dynamic array formulas.