Filling blanks with different values

[baseball]

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

 

[DonkeyOte]

quick sidestep around it...

in C1 = copy the team thing across

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

etc...

 

[G]

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]

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

 

[Aladin Akyurek]

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]

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]

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

Cool "Secrets" about Microsoft Excel
http://support.microsoft.com/default.aspx?scid=/support/excel/content/cooltips/cool.asp


Thanks,
Cliff

 

[maxflia10]

Why couldn't you just auto fill?

 

[GrassHopper]

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]