Extract data from sheet with different data locations.

[dtexter]

Howdy,

I would typically give this a shot before asking for assistance however, I am not even sure if what I am trying to accomplish is possible.

All of the details are on the right in the attached spreadsheet.

Tell me I'm crazy?

I currently copy the numbers in question to another workbook where I keep track of the employee's hours weekly. There are 150 Employees to do this for. Just hoping to save some time.

Thanks All.

[FDibbins]

Are you willing to use a helper column for this?

[FDibbins]

You need to try and avoid using merged cells like you are in that file, it causes all sorts of problems with formulas

Assuming you are OK with a helper column...
These are all based on you unmerging cells.
U13=IF(E13="",U12,E13
copied down

Then for the summary...
Z15=SUMIF(U:U,Y15,J:J)
copied down

For the 2nd part...
AB22=SUMIFS(J:J,U:U,Y22,L:L,Z22)
copied down.

Points to note -
1. E28 has a trailing space - remove that
2. in the 2nd table, copy the names into the blank spaces (or use the approach I used to pull in all teh names)

[Pete_UK]

Put this formula in cell V13:

=IF(E13<>"",MAX(V$12:V12)+1,IF(O13="Total:",ROUND(MAX(V$12:V12)-0.5,1),"-"))

and copy down beyond the extent of your data (it might look better if you centre the values). Then in A2 of the other sheet you can use this formula:

=IF(ROWS($1:1)>MAX(AttendanceWagesNoBreaks_JJ!V:V),"",INDEX(AttendanceWagesNoBreaks_JJ!E:E,MATCH(ROWS($1:1),AttendanceWagesNoBreaks_JJ!V:V,0)))

and this one in B2:

=IF(A2="","",ROUND(INDEX(AttendanceWagesNoBreaks_JJ!M:M, MATCH(ROUND(ROWS($1:1)-0.5,1),AttendanceWagesNoBreaks_JJ!V:V,0)-1),2))

then copy these down as far as you need.

Hope this helps.

Pete

[MarvinP]

Hi dtexter,

Excel has a new tool called Power Query that can make short work of your problem.

You need to*install it as an Add-In for 2010 and 2013*Excel but it is built into 2016.

After pulling in your worksheet into power query, you would "Fill Down" the names and*filter to keep the largest *** hours. *After*building a Power Query, you can do the same thing to each day or week files.

[dtexter]

Put this formula in cell V13:

=IF(E13<>"",MAX(V$12:V12)+1,IF(O13="Total:",ROUND(MAX(V$12:V12)-0.5,1),"-"))

and copy down beyond the extent of your data (it might look better if you centre the values). Then in A2 of the other sheet you can use this formula:

=IF(ROWS($1:1)>MAX(AttendanceWagesNoBreaks_JJ!V:V),"",INDEX(AttendanceWagesNoBreaks_JJ!E:E,MATCH(ROWS($1:1),AttendanceWagesNoBreaks_JJ!V:V,0)))

and this one in B2:

=IF(A2="","",ROUND(INDEX(AttendanceWagesNoBreaks_JJ!M:M, MATCH(ROUND(ROWS($1:1)-0.5,1),AttendanceWagesNoBreaks_JJ!V:V,0)-1),2))

then copy these down as far as you need.

Hope this helps.

Pete

This did work Pete!

Any thoughts on the second part of the problem? Separating the results by job title?

Thanks!

[FDibbins]

Did you check post #3?

[dtexter]

Did you check post #3?

Yes, I just started working with it now. Will play more tomorrow.

[FDibbins]

ok. keep me posted