Reorganizing Messy Data

It appears that you haved cleaned up your data somewhat. Did you remove headings at all, becasue I see that the name in C21 has "Name" above it, but the other names do not?

Also, would you be ok with adding a helper to pull in the names?
I put this in G21, copied down...
=IF(ISERROR(FIND(",",C21,1)),"",C21)

I did not use yout PT, I used this in C4, copied down, to pull in the names...
=IFERROR(INDEX(RawData!$G$21:$G$200,MATCH(0,INDEX(COUNTIF($C$3:C3,RawData!$G$21:$G$200),0,0),0)),"")

Now that you have the names, and assuming that there will always be teh same amount of rows between names, you can use this to pull in your data...
D4=IF($C4="","",INDEX(RawData!$E$21:$E$200,MATCH(Summary!$C4,RawData!$C$21:$C$200,0)+4))
copied down and across

	C	D	E
3		Hours	Overlap Hours
4	DOE, JOHN	35.15	35.15
5	MADISON, JAMES	34.33	34.33
6	WASHINGTON, GEORGE	36.48	36.48
7	JEFFERSON, THOMAS	19.38	19.38
8	MOUSE, MICKEY	32.2	32.2
9	DOE, JANE	31.08	31.08
10	SMITH, DAVID	30.15	30.15
11	JOHNSON, JOE	47.8	47.8
12	RIVERS, PHILLIP	11.88	11.88
13	JAMES, BRIAN	48.5	48.5
14	WHITE, SEAN	40	40
15	DAVIS, MIKE	0.55	0.55
16	WORKMAN, MICHAEL	32.48	32.48
17	JOSEPH, CAMERON	12.2	12.2
18	WRYNN, CODY	35.3	35.3

edit: actually, doing the helper the way I did, it does not matter if headings have been removed

Nevermind. I figured out why I was getting the #N/A error. So the only thing left is to get the Pay Rule to line up. Thanks so much again.

Edit:

Using the formula you used to pull the hours I managed to pull the Pay Rule as well. It seems to work pretty much perfectly. The only issue that arises is when an employee had overtime (and vacation which happens to be none on here) it causes the total hours to go down to 5 or more rows below the name so the formula just catches the overtime number instead of the total hours number.

I would imagine this would be really difficult to fix and since it is only for a fraction of the employees it is not a huge deal.

For the pay rule...
=IF($C4="","",INDEX(RawData!$D$21:$D$200,MATCH(Summary!$C4,RawData!$C$21:$C$200,0)))
(note, I just changed the INDEX range, and removed the +4 at the end

I will take a look at "variable" rows for you

Can you upload a sample with teh extra rows (add as many as you think you will need)

OK, I have changed a few things.

on RawData..
G21=IF(ISERROR(FIND(",",C21,1)),G20,C21) copied down
Added extra helper
H21=IF(G21=G20,H20+1,1) copied down

Then on Summary...
E4=IF($C4="","",INDEX(RawData!$E$21:$E$200,MATCH(Summary!$C4,RawData!$C$21:$C$200,0)+MAX(IF(RawData!$G$21:$G$140=Summary!$C4,RawData!$H$21:$H$140),0)-3))

This an ARRAY formula, and needs to be entered by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You can then copy it down

Hi FDibbins,

I'm having an issue with the ARRAY formula. Whenever I put it in the cell is just blank, no value but the formula is there (it almost looks like the value is hidden).

Thats because it needs the name to work with. Remember this, from post #4?

I put this in G21, copied down...
=IF(ISERROR(FIND(",",C21,1)),"",C21)

I did not use yout PT, I used this in C4, copied down, to pull in the names...
=IFERROR(INDEX(RawData!$G$21:$G$200,MATCH(0,INDEX(COUNTIF($C$3:C3,RawData!$G$21:$G$200),0,0),0)),"")

Yes, I noticed that, I meant to say that it seems that your last data "set" is missing something - actually, there are 2 blank lines between each set...except for the last set?
Try this, instead...
=IF($C4="","",INDEX(RawData!$E$21:$E$200,MATCH(Summary!$C4,RawData!$C$21:$C$200,0)+MAX(IF(RawData!$G$21:$G$140=Summary!$C4,RawData!$H$21:$H$140),0)-IF(MAX(IF(RawData!$G$21:$G$140=Summary!$C4,RawData!$H$21:$H$140),0)=6,1,3)))

I am just happy we managed to resolve your question

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)