# Filling blanks with different values

#### baseball

##### Board Regular
Excel 97

I know I've seen the answer to this question somewhere but I can't seem to find it now.

Suppose I have a list of names of players and their teams, but the team is only listed for the first player of each team. How to I easily fill in the blanks. Example:<PRE>
Team Player
ANA Mike Holtz
Orlando Palmeiro
Mark Petkovsek
BAL Charles Johnson
BOS Carl Everett
Bryce Florie
Rich Garces
Scott Hatteberg

should be

ANA Mike Holtz
ANA Orlando Palmeiro
ANA Mark Petkovsek
BAL Charles Johnson
BOS Carl Everett
BOS Bryce Florie
BOS Rich Garces
BOS Scott Hatteberg</pre>

Thanks,
Cliff
This message was edited by baseball on 2002-10-23 08:29

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### DonkeyOte

##### MrExcel MVP
quick sidestep around it...

in C1 = copy the team thing across

in C2 = if(a2<>"",a2,c1)

etc...

#### G

##### New Member
You could insert a row nexto to team and enter the following formula:

Column A is Team|Column B is new row

=if(a1>"",a1,if(a1="",b1,"")) and drag your formula down.

Since you already have data in A1 (Team)and A2 has data, you should not have any problems.

#### baseball

##### Board Regular
On 2002-10-23 13:43, G wrote:
You could insert a row nexto to team and enter the following formula:

Column A is Team|Column B is new row

=if(a1>"",a1,if(a1="",b1,"")) and drag your formula down.

Since you already have data in A1 (Team)and A2 has data, you should not have any problems.

Thanks to both of you for this solution that I should have been able to figure out myself. I just got fixated on a method that I had seen, which didn't involve formulas.

Thanks for waking me up,
Cliff

##### MrExcel MVP
On 2002-10-23 17:15, baseball wrote:
On 2002-10-23 13:43, G wrote:
You could insert a row nexto to team and enter the following formula:

Column A is Team|Column B is new row

=if(a1>"",a1,if(a1="",b1,"")) and drag your formula down.

Since you already have data in A1 (Team)and A2 has data, you should not have any problems.

Thanks to both of you for this solution that I should have been able to figure out myself. I just got fixated on a method that I had seen, which didn't involve formulas.

Thanks for waking me up,
Cliff

I believe Mark W. described a non-formula method. Try to find it via Search.

#### kskinne

##### Well-known Member
ASAP Utilities is a free add-in available at:

http://www.asap-utilities.com

it has a useful tool which will copy values to the empty cells below all filled cells in a selected range. i have found this extremely useful when working with very large amounts of data.

kevin

#### baseball

##### Board Regular
I believe Mark W. described a non-formula method. Try to find it via Search.

If he did, I can't find it, but I finally did locate the method on Microsoft's site.

Given, with the data in A2:A9
<PRE>
Team Player
ANA Mike Holtz
Orlando Palmeiro
Mark Petkovsek
BAL Charles Johnson
BOS Carl Everett
Bryce Florie
Rich Garces
Scott Hatteberg
</PRE>

Select A2:A9
Edit/Goto/Special/Blanks >> OK
Type =A2 in the formula bar
Press CTRL + ENTER

Now, you have to Copy and Paste Special/Values >> OK

This is useful...if you can remember it.

Another useful tip from this site is how to fill a range with data:

Select the range
Type your entry in the formula bar
Press CTRL + ENTER

http://support.microsoft.com/default.aspx?scid=/support/excel/content/cooltips/cool.asp

Thanks,
Cliff

#### maxflia10

##### Well-known Member
Why couldn't you just auto fill?

#### GrassHopper

##### New Member
You could also create a macro that looks like this:
Sub TeamName()
For Each c In Range("A1:A8")
If c = "" Then c.Value = c.Offset(-1, 0).Value
Next
End Sub

This assumes the team names are in range A1:A8 but it can be modified to the range you want to fill in the blanks. This macro loops through the range when it reaches a blank cell [c= ""] then it populates that cell with the one above it [c.Value = c.Offset(-1, 0).Value]

1,181,309
Messages
5,929,225
Members
436,656
Latest member
ssims

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back