I have a document that looks like this?


Table of students who have chosen physics as a primary choice 1.

ID Primary Choice Secondary Choice Programs
515 1 2 math
3 english
6 spanish


I am trying to create a pivot table that breaks out the primary choice
(first column), them the primary choice(second column) and then progrm
name - the count will be on student ID.

As it is, I first have to fill down the records so that I end up with
this:
ID Primary Choice Secondary Choice Programs
515 1 2 math
515 1 3 english
515 1 6 spanish

Then create the pivot table.

The file has about 10000 student IDs and this is very time consuming,
is there a better way to do this?

Thanks

Hi KW -

Here is a "gap-filling" procedure that has been posted in this Discussion
Group before. Copy it to a standard module, select your Primary Choice
column, and run the procedure.

Sub fill_in_the_blanks()
Dim r As Range
For Each r In Selection
If IsEmpty(r.Value) Then
r.Value = r.Offset(-1, 0).Value
End If
Next
End Sub

--
Jay


"KW" wrote:

> I have a document that looks like this?
>
>
> Table of students who have chosen physics as a primary choice 1.
>
> ID Primary Choice Secondary Choice Programs
> 515 1 2 math
> 3 english
> 6 spanish
>
>
> I am trying to create a pivot table that breaks out the primary choice
> (first column), them the primary choice(second column) and then progrm
> name - the count will be on student ID.
>
> As it is, I first have to fill down the records so that I end up with
> this:
> ID Primary Choice Secondary Choice Programs
> 515 1 2 math
> 515 1 3 english
> 515 1 6 spanish
>
> Then create the pivot table.
>
> The file has about 10000 student IDs and this is very time consuming,
> is there a better way to do this?
>
> Thanks
>
>