Copying Non-Blank Cell Values In A Column Down to Fill Blank Cells with Same Value Until New Non-Blank Value Appears

[Aimee S.]

Hi Folks. I have a good thousand or two rows of data that looks something like this. The first column is utilizing formula:

=IF(AND(ISERROR(SEARCH("-",D3)),LEN(D3)>6),D3,"")

to pull only names from Column B, otherwise returning blanks.

What I ultimately need is for column A to have, for example (in the first two rows), the name 'Schultz, Paul' populated for the first two rows until a new name (Crawford, Benny) appears, then repeat.

So if you see a 6-digit ID # in the second column, I need the formula to populate in Column A the Last, First name of the person to the immediate left of the 6-digit ID #. For example, the first digit 800187 should show Schultz, Paul to the left in Column A and so forth. / Halp?


Here is what the data originally looks like:

	Schultz, Paul
	800187
	Crawford, Benny
	800638
	Smith, Tim
	800144
	NS-ISP IMPLEMENTATION
	Alt, Michael
	795979
	Faircloth, Larry
	795403
	800769
	800014
	799261
	800763

<tbody>
</tbody>

Here is what I need the data to look like, with Column A populated via formulas.

Schultz, Paul	Schultz, Paul
Schultz, Paul	800187
Crawford, Benny	Crawford, Benny
Crawford, Benny	800638
Smith, Tim	Smith, Tim
Smith, Tim	800144
NS-ISP IMPLEMENTATION	NS-ISP IMPLEMENTATION
Alt, Michael	Alt, Michael
Alt, Michael	795979
Faircloth, Larry	Faircloth, Larry
Faircloth, Larry	795403
Faircloth, Larry	800769
Faircloth, Larry	800014
Faircloth, Larry	799261
Faircloth, Larry	800763

<tbody>
</tbody>

 

[Joe4]

Assuming your data begins in cell B3 (you were using column D in your formula), you could place this formula in cell A3:

=B3

and then enter this formula in cell A4 and copy down for all rows:

=IF(ISNUMBER(B4+0),A3,B4)

 

[Aimee S.]

You are right about the Columns - sorry about that - I made them A and B for the purposes of this post and forgot to update the formula.

Your solution is great! I just had one question - would it be possible to include in the formula you provided:

If value in B3 contains a "-" (a hyphen), make A3 blank, otherwise your formula kicks in? So it would look like this:

	NS-MARKET IMPLEMENTATION
Schultz, Paul	Schultz, Paul
Schultz, Paul	800187
Crawford, Benny	Crawford, Benny
Crawford, Benny	800638
Smith, Tim	Smith, Tim
Smith, Tim	800144
	NS-ISP IMPLEMENTATION
Alt, Michael	Alt, Michael
Alt, Michael	795979

<tbody>
</tbody><colgroup><col><col></colgroup>

Assuming your data begins in cell B3 (you were using column D in your formula), you could place this formula in cell A3:

=B3

and then enter this formula in cell A4 and copy down for all rows:

=IF(ISNUMBER(B4+0),A3,B4)

 

[Joe4]

Just incorporate some of the logic you were using before:

=IF(ISNUMBER(B4+0),A3,IF(ISNUMBER(SEARCH("-",B4)),"",B4))

 

[Aimee S.]

Thanks so much, Joe! Yes, I tried but was getting an error value returned because I didn't include the ISNUMBER in the hyphen SEARCH part of the nested IF. Whoopsy.

You saved me a lot of pain today. SOLVED! Thanks again (and great avatar by the by - I grew up on Calvin and Hobbes and I am proud to say my world view was in part molded by Bill Watterson) ^_^

Just incorporate some of the logic you were using before:

=IF(ISNUMBER(B4+0),A3,IF(ISNUMBER(SEARCH("-",B4)),"",B4))

 

[Joe4]

You are welcome!

Always great to find another C&H fan!