formula to find and display only cells with changing data in specified columns

[yourtwizted]

Hey Guys,

i have been doing some searching, and i am not all that great at making excel files but i would love to make my job easier. i have an absenteeism/attendance report and an offline report that i am supposed to run and the initial report provides data to following Screenshot(data changed for work confidentiality).

what i would like to do separate the times into Start and End times and only have the employees that have time under "tallies" section be visible.
i have started to work on this slowly(during my gap time at work) and come up with this so far.

As you can see i have it pulling the data and separating it, my problem is that, for convenience's sake i would like only that people that have times in those specified columns. i.e. the "Extra Hours1" column to only show the 4 employees with time in that column.

i have used INDEX and MATCH to locate the employee ID and used LEFT and RIGHT to split the data. is there a formula that i can use that i can use that will only pull the employee ID when there is data in a specified column?
I have tried a few times to try and use IF statements with INDEX and MATCH but i only seem to get errors
if there is confusion please ask! i am happy to share as much as i am legally allowed to

 

[jasonb75]

Can't see to well from your screen captures, I would go with something like

=IFERROR(INDEX(extra_hours_1_whole_column,AGGREGATE(15,6,ROW(extra_hours_1)/(extra_hours_1<>""),ROWS(A$1:A1)),"")

Used descriptions as images don't show which rows / columns the data is in. The reference to A$1:A1 should be the cell holding the first formula before you fill down.

Note that I've suggested using the entire column in the INDEX range, i.e. B:B. The other ranges should only use the rows with data (you could include extra rows to cover the maximum that you would need if the amount of data is variable, but don't go to far, no point in setting the formula to 10000 rows if you never need more than 100).

You could index all columns that you need to copy over using the same AGGREGATE array, or use a helper column with just the AGGREGATE array, then refer to the helper column with your index formulas.

=IFERROR(AGGREGATE(15,6,ROW(extra_hours_1)/(extra_hours_1<>""),ROWS(A$1:A1),"")

Then, assuming the above is filled down from A2, use the INDEX formula as below, changing the index column as needed.

=IF(A2="","",INDEX(extra_hours_1_whole_column,A2))

Hope that makes sense.

 

[yourtwizted]

Can't see to well from your screen captures, I would go with something like

=IFERROR(INDEX(extra_hours_1_whole_column,AGGREGATE(15,6,ROW(extra_hours_1)/(extra_hours_1<>""),ROWS(A$1:A1)),"")

Used descriptions as images don't show which rows / columns the data is in. The reference to A$1:A1 should be the cell holding the first formula before you fill down.

Note that I've suggested using the entire column in the INDEX range, i.e. B:B. The other ranges should only use the rows with data (you could include extra rows to cover the maximum that you would need if the amount of data is variable, but don't go to far, no point in setting the formula to 10000 rows if you never need more than 100).

You could index all columns that you need to copy over using the same AGGREGATE array, or use a helper column with just the AGGREGATE array, then refer to the helper column with your index formulas.

=IFERROR(AGGREGATE(15,6,ROW(extra_hours_1)/(extra_hours_1<>""),ROWS(A$1:A1),"")

Then, assuming the above is filled down from A2, use the INDEX formula as below, changing the index column as needed.

=IF(A2="","",INDEX(extra_hours_1_whole_column,A2))

Hope that makes sense.

i greatly appreciate this! i do apologize that i didn't include the sheet names or cells,
if we were to call the first Screenshot sheet name "RawData", and the second screenshot sheet name "Main", and for the first columns A:A and first row is 1, what would the formula look like?

 

[jasonb75]

If nobody else steps in and does it, I'll post the corrected formula when I can, my wifi has gone down and I'm useless at writing formulas on my phone.

 

[jasonb75]

The same 3 formulas a earlier, with Z$3:Z3 being the cell that the first formula is going into before you drag / fill as needed. These were based on the first screen capture, the second one is not clear enough to use.

=IFERROR(INDEX(RawData!$J:$J,AGGREGATE(15,6,ROW(RawData!$J$4:$J$21)/(RawData!$J$4:$J$21<>""),ROWS(Z$3:Z3),"")

=IFERROR(AGGREGATE(15,6,ROW(RawData!$J$4:$J$21)/(RawData!$J$4:$J$21<>""),ROWS(Z$3:Z3),"")

=IF(Z3="","",INDEX(RawData!$J:$J,Z3))

It is preferable if you use XL2BB (green button on the reply toolbar) to post samples rather than screenshots, that makes it easier to read, it preserves formatting and we can copy and paste it into excel to test formulas.

 

[yourtwizted]

thanks for the teachable moment! new to this!

the following is what i currently have for what i would like to just view the data.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Make-Up Time1				Make-Up Time2				Extra Hours1				Extra Hours2				VTO1
2	Employee ID	Start	End	Total	Employee ID	Start	End	Total	Employee ID	Start	End	Total	Employee ID	Start	End	Total	Employee ID	Start	End	Total
3	825			0	825			0	825			0	825			0	825			0
4	695			0	695			0	695			0	695			0	695			0
5	405			0	405			0	405			0	405			0	405			0
6	901			0	901			0	901	16:00	17:00	1.00	901			0	901			0
7	880			0	880			0	880	07:00	09:15	2.25	880	18:15	19:00	0.75	880			0
8	886			0	886			0	886			0	886			0	886			0
9	436			0	436			0	436			0	436			0	436			0
10	158			0	158			0	158			0	158			0	158			0
11	781			0	781			0	781			0	781			0	781			0
12	573			0	573			0	573	07:15	08:15	1.00	573			0	573			0
13	685			0	685			0	685			0	685			0	685			0
14	275			0	275			0	275			0	275			0	275			0
15	629			0	629			0	629	07:00	09:15	2.25	629			0	629			0
16	800			0	800			0	800			0	800			0	800			0
17	578			0	578			0	578			0	578			0	578			0
Main

Cell Formulas
Range	Formula
A3:A17,E3:E17,I3:I17,M3:M17,Q3:Q17	A3	=RawData!$A4
B3:B17	B3	=IFNA(LEFT(INDEX(RawData!$H:$H,MATCH(Main!A3,RawData!$A:$A,0)),5),"no data")
C3:C17	C3	=IFNA(RIGHT(INDEX(RawData!$H:$H,MATCH(Main!A3,RawData!$A:$A,0)),5),"no data")
D3:D17,H3:H17,L3:L17,P3:P17,T3:T17	D3	=IFERROR((C3-B3)*24,"0")
F3:F17	F3	=IFNA(LEFT(INDEX(RawData!$I:$I,MATCH(Main!E3,RawData!$A:$A,0)),5),"no data")
G3:G17	G3	=IFNA(RIGHT(INDEX(RawData!$I:$I,MATCH(Main!E3,RawData!$A:$A,0)),5),"no data")
J3:J17	J3	=IFNA(LEFT(INDEX(RawData!$J:$J,MATCH(Main!I3,RawData!$A:$A,0)),5),"no data")
K3:K17	K3	=IFNA(RIGHT(INDEX(RawData!$J:$J,MATCH(Main!I3,RawData!$A:$A,0)),5),"no data")
N3:N17	N3	=IFNA(LEFT(INDEX(RawData!$K:$K,MATCH(Main!M3,RawData!$A:$A,0)),5),"no data")
O3:O17	O3	=IFNA(RIGHT(INDEX(RawData!$K:$K,MATCH(Main!M3,RawData!$A:$A,0)),5),"no data")
R3:R17	R3	=IFNA(LEFT(INDEX(RawData!$L:$L,MATCH(Main!Q3,RawData!$A:$A,0)),5),"no data")
S3:S17	S3	=IFNA(RIGHT(INDEX(RawData!$L:$L,MATCH(Main!Q3,RawData!$A:$A,0)),5),"no data")

this is the raw data that the report gives:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Detail				Remove			Tallies
2	Employee ID	Name(First Last)	DOW	Schedule	Lunch	Exclude1	Exclude2	Make-Up Time1	Make-Up Time2	Extra Hours1	Extra Hours2	VTO1	VTO2	Team Meeting/HR1
3
4	825	John 1		10:00-19:00	14:00-15:00
5	695	John 2	4/19	10:00-19:00	14:00-15:00
6	405	John 3	4/19	08:00-17:00	12:15-13:15
7	901	John 4	4/19	07:00-17:00	10:45-11:45					16:00-17:00
8	880	John 5	4/19	07:00-19:00	13:15-14:15					07:00-09:15	18:15-19:00
9	886	John 6	4/19	07:00-16:00	10:30-11:30	07:00-10:30	11:30-16:00
10	436	John 7	4/19	07:00-16:00	11:00-12:00
11	158	John 8	4/19	07:00-16:00	10:50-11:50	07:00-07:30
12	781	John 9	4/19	10:00-19:00	13:45-14:45
13	573	John 10	4/19	07:15-17:15	12:00-13:00					07:15-08:15
14	685	John 11	4/19	07:00-16:00	10:30-11:30
15	275	John 12	4/19	07:00-16:00	10:45-11:45
16	629	John 13	4/19	07:00-18:15	13:00-14:00					07:00-09:15
17	800	John 14	4/19	07:00-16:00	10:30-11:30
18	578	John 15	4/19	07:00-16:00	10:30-11:30	07:00-08:30
19	90	John 16	4/19	07:00-16:00	10:35-11:35
20	100	John 17	4/19	10:00-19:00	13:45-14:45	10:00-13:45	14:45-19:00
21	894	John 18	4/19	07:00-16:00	11:30-12:30
RawData

This data is normally seen for weeks at a time(sunday to saturday)
i would like to display only current day data in separate columns at least similar to what i have

 

[jasonb75]

Now I'm following what you need, it is a little more complex than I first thought. Bear with me, I'll see what I can do.

 

[jasonb75]

I think that this will do what you need, if you set your sheet up with the first 4 formulas (A3:D3 in the table below) then select all 4 cells and copy them right as a group the column references and formatting will adjust automatically.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Make-Up Time1				Make-Up Time2				Extra Hours1				Extra Hours2				VTO1
2	Employee ID	Start	End	Total	Employee ID	Start	End	Total	Employee ID	Start	End	Total	Employee ID	Start	End	Total	Employee ID	Start	End	Total
3									901	16:00:00	17:00:00	01:00:00	880	18:15:00	19:00:00	00:45:00
4									880	07:00:00	09:15:00	02:15:00
5									573	07:15:00	08:15:00	01:00:00
6									629	07:00:00	09:15:00	02:15:00
7
Sheet2

Cell Formulas
Range	Formula
A3:A7,Q3:Q7,M3:M7,I3:I7,E3:E7	A3	=IFERROR(INDEX(RawData!$A:$A,AGGREGATE(15,6,ROW(RawData!$A$4:$A$21)/(INDEX(RawData!$H$4:$N$21,0,MATCH(LOOKUP("zzz",$A$1:A$1),RawData!$H$2:$N$2,0))<>""),ROWS(A$3:A3))),"")
B3:B7,R3:R7,N3:N7,J3:J7,F3:F7	B3	=IF(A3="","",LEFT(INDEX(RawData!$H:$N,AGGREGATE(15,6,ROW(RawData!$A$4:$A$21)/(INDEX(RawData!$H$4:$N$21,0,MATCH(LOOKUP("zzz",$A$1:B$1),RawData!$H$2:$N$2,0))<>""),ROWS(B$3:B3)),MATCH(LOOKUP("zzz",$A$1:B$1),RawData!$H$2:$N$2,0)),5)+0)
C3:C7,S3:S7,O3:O7,K3:K7,G3:G7	C3	=IF(A3="","",RIGHT(INDEX(RawData!$H:$N,AGGREGATE(15,6,ROW(RawData!$A$4:$A$21)/(INDEX(RawData!$H$4:$N$21,0,MATCH(LOOKUP("zzz",$A$1:C$1),RawData!$H$2:$N$2,0))<>""),ROWS(C$3:C3)),MATCH(LOOKUP("zzz",$A$1:C$1),RawData!$H$2:$N$2,0)),5)+0)
D3:D7,T3:T7,P3:P7,L3:L7,H3:H7	D3	=IF(A3="","",MOD(C3-B3,1))

 

[yourtwizted]

This is awesome!!! it worked beautifully! thank you so much!