Filling in the blanks

shanzel

Board Regular
Joined
Nov 13, 2004
Messages
63
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
  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.
 
Upvote 0
Ok, I cant't get past number 2, it returns an error saying "No Cells Were Found."

Suggestions?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
Sorry I'm confused.:eek:

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.
 
Upvote 0
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?
 
Upvote 0

Similar threads

Forum statistics

Threads
1,222,046
Messages
6,163,588
Members
451,846
Latest member
ajk99

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
Back
Top