If Statment Help

[Huizar]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number
[/TD]
[TD]Fill
[/TD]
[TD]Address
[/TD]
[/TR]
[TR]
[TD]1026
[/TD]
[TD][/TD]
[TD]902 E. Fremont
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]902 E. Fremont
[/TD]
[/TR]
[TR]
[TD]1063
[/TD]
[TD][/TD]
[TD]871 Main Street
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]829 Main
[/TD]
[/TR]
</tbody>[/TABLE]
1263


Hello All, I need help. I'm looking for statement that will help me fill the middle Column. For Example, If the address ha a row number then fill it with the number column number with the number assuming the address Is identical. If No number for the address, then leave it blank. I put a table as an example.

Thank you!!

 

[Eric W]

Is this what you want?

Book1
	A	B	C
1	Number	Fill	Address
2	1026	1026 E. Fremont	902 E. Fremont
3			902 E. Fremont
4	1063	1063 Main Street	871 Main Street
5			829 Main
6
7	1263
8
Sheet7

Cell Formulas
Range	Formula
B2	=IF(A2="","",IF(ISNUMBER(LEFT(C2)+1),A2 &MID(C2,FIND(" ",C2&" "),99),""))

 

[Huizar]

Hey Eric,

Thanks for the response. Did you see the address match? Then can the formula bring down 1026? so in Other words row 2 and 3 should have 1026.

Thanks!!

 

[Eric W]

OK, try this:

B2: =IF(AND(C2=C1,A2=""),B1,IF(A2="","",IF(ISNUMBER(LEFT(C2)+1),A2 &MID(C2,FIND(" ",C2&" "),99),"")))

 

[Scott Huish]

Perhaps:
=IF(AND(A2<>"",C2<>""),A2&" "&REPLACE(C2,1,FIND(" ",C2),""),IF(C2=C1,B1,""))

Excel 2010
	A	B	C
1	Number	Fill	Address
2	1026	1026 E. Fremont	902 E. Fremont
3		1026 E. Fremont	902 E. Fremont
4	1063	1063 Main Street	871 Main Street
5			829 Main
6	1263
Sheet1

Cell Formulas
Range	Formula
B2	=IF(AND(A2<>"",C2<>""),A2&" "&REPLACE(C2,1,FIND(" ",C2),""),IF(C2=C1,B1,""))
B3	=IF(AND(A3<>"",C3<>""),A3&" "&REPLACE(C3,1,FIND(" ",C3),""),IF(C3=C2,B2,""))
B4	=IF(AND(A4<>"",C4<>""),A4&" "&REPLACE(C4,1,FIND(" ",C4),""),IF(C4=C3,B3,""))
B5	=IF(AND(A5<>"",C5<>""),A5&" "&REPLACE(C5,1,FIND(" ",C5),""),IF(C5=C4,B4,""))
B6	=IF(AND(A6<>"",C6<>""),A6&" "&REPLACE(C6,1,FIND(" ",C6),""),IF(C6=C5,B5,""))

 

[Huizar]

Thanks Eric!

 

[Huizar]

Hey Scott, is there a way a just to bring in the Number in Cell B (i.e. only 1026).

Thanks!

 

[Scott Huish]

Like this?

Excel 2010
	A	B	C
1	Number	Fill	Address
2	1026	1026	902 E. Fremont
3		1026	902 E. Fremont
4	1063	1063	871 Main Street
5			829 Main
6	1263
Sheet1

Cell Formulas
Range	Formula
B2	=IF(AND(A2<>"",C2<>""),A2,IF(C2=C1,B1,""))
B3	=IF(AND(A3<>"",C3<>""),A3,IF(C3=C2,B2,""))
B4	=IF(AND(A4<>"",C4<>""),A4,IF(C4=C3,B3,""))
B5	=IF(AND(A5<>"",C5<>""),A5,IF(C5=C4,B4,""))
B6	=IF(AND(A6<>"",C6<>""),A6,IF(C6=C5,B5,""))

 

[Huizar]

Thank you Scott!