Function that will collapse 4 rows of data into one row based on the first two columns information.

[Theneoalchemist]

Hi All,

I am currently working on making a workbook that takes data from a data base and consolidates it down to a easier read. I currently have a system that shows when people call into our system, the problem is that this system creates a row of data for each entry and I would like to take that data and consolidate it into a simpler read. I will create a example below.

I do currently have a Index & Match solution that works but it is taking a lot of resources to run it on every line in the data base so I am hoping for a simpler solution that will not cause my file to freeze up so much. The output file contains hundreds of employees and calls as it is usually pulled for a weeks work on call times.

Example below.

Current System Out Put (cannot change from what the system puts out)

Full Name	Work Date	Scheduled Hours	Worked Hours	Call Time	Call Type
Employee #1	Work Date #1	.	8.20
Employee #1	Work Date #1	8.00	.
Employee #1	Work Date #1	.	.	08:03	ON - Duty
Employee #1	Work Date #1	.	.	16:12	OFF - Off Duty

What I would like end product to be

Employee #1

Work Date #1

Call time - On Duty

Call Time - Off Duty

Here is the Current Codes that I use that seems to absorb a lot of resources to be used. (right now I just have it saying On or Off Duty)

Employee #1	Work Date #1	=IF(N2="","",IF(INDEX($J:$J,MATCH(1,(N2=$E:$E)*($O2=$F:$F),0)+COUNTA($J$1:J$1)+1)=0,"No Call",INDEX($J:$J,MATCH(1,(N2=$E:$E)*($O2=$F:$F),0)+COUNTA($J$1:J$1)+1)))	=IF(N2="","",IF(INDEX($J:$J,MATCH(1,(N2=$E:$E)*($O2=$F:$F),0)+COUNTA($J$1:J$1)+2)=0,"NO CALL",INDEX($J:$J,MATCH(1,(N2=$E:$E)*($O2=$F:$F),0)+COUNTA($J$1:J$1)+2)))
Employee #1	Work Date #2	=IF(N3="","",IF(INDEX(J:J,MATCH(1,($N3=$E:$E)*($O3=$F:$F),0)+COUNTA($J$1:J$1)+1)=0,"No Call",INDEX(J:J,MATCH(1,($N3=$E:$E)*($O3=$F:$F),0)+COUNTA($J$1:J$1)+1)))	=IF(N3="","",IF(INDEX(J:J,MATCH(1,($N3=$E:$E)*($O3=$F:$F),0)+COUNTA($J$1:J$1)+2)=0,"NO CALL",INDEX(J:J,MATCH(1,($N3=$E:$E)*($O3=$F:$F),0)+COUNTA($J$1:J$1)+2)))

In the end I am mostly trying to eliminate the white noise and also make it easier to see when someone missed a call. The Employee Names and Dates in my table are generated by a Unique filter combo that I have used to create a separate table.

Any assistance with this would be greatly appreciated.

 

[James006]

Hi,

To generate your consolidation report, have you already tried to insert a Pivot Table ?