Filling in the blanks

[shanzel]

I have a spread sheet that has SSN and Student courses in the next column, I need it to fill in the ssn for each course.

SSN Course

111111111 ARTS1111
111111111 ARCH1111
111111111 MUSC1111
111111111 MATH1111
222222222 BIOL1111
HIST1111
SOCG1111
AGRI1111
COMP1111
333333333 SOWK1111
RDNG1111
POLC1111

Is there a formula and/or a macro that looks for a blank and fills it in with what is available from above?

 

[Norie]

1. Select the column with the blanks.
2. Goto Edit>Goto...Special and select blanks.
3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
4. Select the column, copy and paste special values.

 

[shanzel]

Ok, I cant't get past number 2, it returns an error saying "No Cells Were Found."

Suggestions?

 

[Norie]

Well basically that's telling you there are no blanks.

Is there anything at all in these cells?

Are they the results of a formula?

 

[shanzel]

Ok, I took the SSN out of text and it work but it would still be more or less a manual entry, do you have anything faster? The problem is there are 37,000 rows.

Thanks

 

[Norie]

Sorry I don't understand.

The method I outlined should be automatic.

Can you please explain further exactly how the data is structured?

I'm assuming you have 2 columns, one for SSN and one for course and that you want to fill in the blank SSN.

Is that correct?

 

[shanzel]

I forgot that they were in text format. I took them out and your way worked like a charm. But this would still be a manual row entry do you have anything faster? The problem is there are 37,000 rows in four different reports.

Thanks for your help!!

 

[shanzel]

Sorry I didn't think that last message went through. Yes you are correct, I do have columns and I want the ssn to match with the course next to it. As it is right now the person ssn is in the first column and the courses that that person is taking is in the next column. That person may be taking five courses and the ssn is only listed once, and the next person maybe taking only two courses etc. I need the blanks ssn filled in so I can run another report on this. Does this make sense? I tried your "Goto" and it only filled in one row (a2), is it suppose to fill in all the way down? Did I miss something?

Please advise,

Thanks

 

[Norie]

Sorry I'm confused.

Did my method work or not?

It should automatically fill in every blank if it worked correctly.

One important thing is that you use CTRL+ENTER.

 

[shanzel]

No, it didn't work, it only fills in one row and that's it. Let me make sure I'm doing this right. I high-lighted the column, click on edit, then goto, special, check blanks, ok, then in the formula bar I type =a1 and press ctrl enter. Is this correct?