Inserting blank entries for missing values

[rbmcclen]

Hi all,

Google isn't being as helpful as I would like with this problem, I'm
hoping someone here will know what to do, if at all possible.

How can I get excel to take the following information:

Mon..Hours..Tue..Hours..Wed...Hours..Thu...Hours..Fri...Hours
Jane.....3..Mary.....4..Mary......3..Mary......2..Mary......1
Betty....5..Jane.....2..Joan......6..Jane......8..Joan......5
Rob......1..Joan.....2..Rob.......4..Betty.....7..Betty.....7
Kristy...2..Betty....9..Kristy....6..Rob.......5..Liz.......4
Janette..5..Rob......3..Janette...7..Janette...4..
Liz......3..Janette..2..Liz.......3..Barbra....3..
Barbra...1..Liz......6..Barbra....4....
............Barbra...8

And format it to fix the missing entries on each day so it appears like
this:

.......Mon.Tue.Wed.Thu.Fri
Mary.....0...4...3...2...1
Jane.....3...2...0...8...0
Joan.....0...2...6...0...5
Betty....5...9...0...7...7
Rob......1...3...4...5...0
Kristy...2...0...6...0...0
Janette..5...2...7...4...0
Liz......3...6...3...0...4
Barbra...1...8...4...3...0

The above was done manually, I would like a way to automate the
process. The actual data I'm working with is much larger
Sorry for the dots, I couldn't find another way to space everything
correctly
Knowing my luck this is something extreamly trivial

 

[Herbert Seidenberg]

Use Pivot Table. It requires no formulas.
Shift the header row to the right so the day labels
are on top of the hour columns, like this:
Mon Tue Wed Thu Fri
Jane 3 Mary 4 Mary 3 Mary 2 Mary 1
Betty 5 Jane 2 Joan 6 Jane 8 Joan 5
Rob 1 Joan 2 Rob 4 Betty 7 Betty 7
Kristy 2 Betty 9 Kristy 6 Rob 5 Liz 4
Janette 5 Rob 3 Janette 7 Janette 4
Liz 3 Janette 2 Liz 3 Barbra 3
Barbra 1 Liz 6 Barbra 4
Barbra 8
Pivot Table > Multiple Consolidation Ranges
Select the above first two columns, including the header Mon. Hit Add.
Repeat for other days until you have 5 ranges.
Ranges can have different number of rows or be dynamic.
Layout > Sum of Values
Options > Uncheck: Grand Totals
Check: For empty cells, show 0
Hit Refresh whenever you update your original data.