Inserting blank entries for missing values
 

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


--
rbmcclen
------------------------------------------------------------------------
rbmcclen's Profile: http://www.excelforum.com/member.php...o&userid=36367
View this thread: http://www.excelforum.com/showthread...hreadid=561544

Inserting blank entries for missing values
 

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.