Filling blanks with different values

baseball

Board Regular
Joined
Apr 1, 2002
Messages
153
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
quick sidestep around it...

in C1 = copy the team thing across

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

etc...
 

G

New Member
Joined
Aug 19, 2002
Messages
41
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
Joined
Apr 1, 2002
Messages
153
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 1, 2002
Messages
153

ADVERTISEMENT

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
 

GrassHopper

New Member
Joined
Oct 17, 2002
Messages
9
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]
 

Forum statistics

Threads
1,144,312
Messages
5,723,650
Members
422,508
Latest member
Lordkit1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top