Fill in missing sample values

[RRinTetons]

I have a data set with about 10,000 rows of sample data. The system that provided the data doesn't output a 0 row for an instance in which a value wasn't recorded, i jus skips it. I need to fill in the missing sample rows.

Example:

Name Period Value
Fred 0 100
Fred 2 125
Fred 4 115
Fred 6 110
Martha 0 100
Martha 2 125
Martha 6 110
George 0 100
George 2 125
George 4 115

Fred has sample data for 4 periods, numbered 0,2,4,6.
Martha is missing period 4.
George is missing period 6.

Any suggestions on how to get the missing rows filled in?

One thing I've thought of is to extract the unique names and the unique periods and do a cross join containing all possible name/period pairs, then do a lookup into the actual data for data where it exists and supply a 0 where it doesn't. Except that I don't know how to do a cross join in Excel . I can cobble together a quick and dirty SQL db with two tables and do it that way, I guess.

Any other ideas?

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Ruchard,

Why do you need non-existent rows in your SOURCE DATA? That is not a best and accepted practice.

You could use the pivot table wizard and get a REPORT in about 5 seconds, like...
[tt]
Sum of Value Period
Name 0 2 4 6
Fred 100 125 115 110
George 100 125 115
Martha 100 125 110
Grand Total 300 375 230 220
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

So I can do a two way ANOVA with replication.


-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

If you really need that then the SQL wold work. You can do this IN Excel.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

I did it in SQL Server and just copied and pasted from SSMS. It's done now, on to the statistics.

-
Richard Ray
Jackson Hole Mountain Resort

 

[lionelhill]

is filling in zeroes statistically justified?

 

[SkipVought]

My thought, also, but I am a statistical midget.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

On this case, yes. The values aren't unknown, they are observations with a value of zero that the source data system doesn't report. They are actually zero, NOT null.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

That makes sense. But it seemed that they were NOT observations from the description. Your system may need some fine tunings, as how would you know where the actual ZERO data points were versus the absence of data points?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

Not my system to mess with, but in this case a null isn't actually even possible, so there's no confusion.

-
Richard Ray
Jackson Hole Mountain Resort