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]

If the first row with data is in A1 you type =A1.

If it's a different row then type =AX, where X is the first row with data.

Another way to do it is to either type = in the formula bar, then press the Up arrow.

 

[pbt]

Shanzel

I, too am a little confused as to what you are after. I set up a sheet with the information you provide and hopefully the layout is close(columns are generic and can be changed for your sheet)

I tried Norie's suggestion and it filled in every blank with what is in cell "A3". I assumed that A2 was your first blank cell.

I tried your "Goto" and it only filled in one row (a2),

This is how I setup the sheet based on what was given:

Book1
	A	B	C	D
1	SSN	Course
2
3	111111111	ARTS1111
4	111111111	ARCH1111
5	111111111	MUSC1111
6	111111111	MATH1111
7	222222222	BIOL1111
8		HIST1111
9		SOCG1111
10		AGRI1111
11		COMP1111
12	333333333	SOWK1111
13		RDNG1111
14		POLC1111
Sheet1

And this is how the sheet looks after running Norie's suggestion:

Book1
	A	B	C	D
1	SSN	Course
2	111111111
3	111111111	ARTS1111
4	111111111	ARCH1111
5	111111111	MUSC1111
6	111111111	MATH1111
7	222222222	BIOL1111
8	333333333	HIST1111
9	333333333	SOCG1111
10	333333333	AGRI1111
11	333333333	COMP1111
12	333333333	SOWK1111
13	0	RDNG1111
14	0	POLC1111
Sheet1

I did not do the last step (paste special...values) as suggested so you can see the outcome (formulas) in the previous blank cells.

Now for further discussion on what you are actually after.

That person may be taking five courses and the ssn is only listed once, and the next person maybe taking only two courses etc.

That is not the case with you sample data. Person 1 seems to be taking 4 courses and his SSN is listed 4 times. Persons 2 & 3 seem to be taking 1 course each, SSN listed once.

EDIT: So if person 1 is taking 5 courses and Person 2 is taking 2 couses, how do we know which couses that will be?

I need the blanks ssn filled in so I can run another report on this.

I think this statement is the most confusing.

I'm assuming that your Course column repeats the available courses (you need more than 1 student in ARTS1111, right)

So whith what is provided, it is hard to tell who (or what) should be in your "blank" courses.

I think with a little more info and a sample of your sheet(sensitve data masked) using the HTML maker, and what results you need, I'm sure something can be done.

h.h.

 

[Boller]

If the first row with data is in A1 you type =A1.

If it's a different row then type =AX, where X is the first row with data.

Not exactly. Should be the cell immediately above the first blank cell.

 

[Norie]

Boiler

Which is what I really meant, but perhaps didn't explain too well.

 

[pbt]

Okay, did it again with refence to cell above blank.

Book1
	A	B	C	D
1	SSN	Course
2	SSN
3	111111111	ARTS1111
4	111111111	ARCH1111
5	111111111	MUSC1111
6	111111111	MATH1111
7	222222222	BIOL1111
8	222222222	HIST1111
9	222222222	SOCG1111
10	222222222	AGRI1111
11	222222222	COMP1111
12	333333333	SOWK1111
13	333333333	RDNG1111
14	333333333	POLC1111
Sheet1

But the question to the OP is, does Person 2 belong in the couses that now has his SSN in column A? Same for Person 3.

h.h.