Hello,
We recently switched to a shiny new database at my work, and I am trying to develop a report that will show me data discrepancies between the entries in both databases. We've been having our employees enter all data into both systems so that we have all of 2018's data in the old system (our "go live" date with the new database was mid-October 2018).
However, this requires some "translation" on my part because things are set up a bit differently in the new system. I can pull data from both systems, and I know how to translate them; the issue I am having is figuring out how to sum two dates (formatted mm/dd/yyyy hh:mm:ss) so that the row I use for comparison (the subtotal) has the start date/time of the first event, and the end date/time of the second, with a sum of the two events as the total elapsed time. (Essentially, in our old database we used only a single code. In our new database we are using two codes so we can be more accurate in our data reporting to the agencies that contract with us).
So, I'm going to try to give you a mock-up of the data I am trying to compare; hopefully it makes sense.
This is what the data from our old database looks like:
[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Client Name[/TD]
[TD]Service[/TD]
[TD]Location[/TD]
[TD]Event[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Elapsed Time[/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD]Last name, First name[/TD]
[TD]Crisis[/TD]
[TD]Main Campus[/TD]
[TD]JRC Time[/TD]
[TD]01/14/2019 10:05[/TD]
[TD]01/14/2019 13:30[/TD]
[TD]3.41[/TD]
[/TR]
</tbody>[/TABLE]
This is what this same information looks like when recorded using the new database:
[TABLE="class: grid, width: 1200, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Client Name[/TD]
[TD]Service[/TD]
[TD]Location[/TD]
[TD]Event[/TD]
[TD][Possible original DB translation column][/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Elapsed Time[/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD]Last name, First name[/TD]
[TD]Crisis[/TD]
[TD]Main Campus[/TD]
[TD]Awaiting Plan[/TD]
[TD]JRC Time[/TD]
[TD]01/14/2019 10:05[/TD]
[TD]01/14/2019 11:05[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD]Last name, First name[/TD]
[TD]Crisis[/TD]
[TD]Main Campus[/TD]
[TD]Crisis Time Out[/TD]
[TD]JRC Time[/TD]
[TD]01/14/2019 11:05[/TD]
[TD]01/14/2019 13:30[/TD]
[TD]2.41[/TD]
[/TR]
</tbody>[/TABLE]
So, I'm hoping that there is a way to utilize subtotals (or some Excel function) so that I can cross check these two bits of data.
With regards to start date and end date, the most important thing is that the calendar date matches and that the overall elapsed time matches. So, when cross checking, I just need to ensure that the day that the event began and and the day that the event ended, along with the total time, matches between the two systems.
(One thing to note: We are a 24 hour facility, so occasionally [as in maybe 2 out of every 50 clients] we will have data that will start on one calendar date, and end on another. So, client comes in at 2350 on 01/13/19, and is picked up at 1030 on 01/14/19. Start date for event in original system would be 01/13/2019 23:50. End date for event in original system would be 01/14/2019 10:30.
In new system event would be split into two events: The first one would run from 01/13/2019 23:50 - 01/14/2019 00:30. The second one would run from 01/14/2019 00:30 - 01/14/2019 10:30. I felt the need to mention this because I didn't know if it would complicate things).
Normally, when doing this type of data cross-checking, I would set up my workbook so that the data from one database is on one spreadsheet, and the data from the other database is on another spreadsheet within the same workbook. After I've done all of my formatting and added my "translation columns", I would then add two columns to each spreadsheet. In one column (titled "Key"), I would use concatenate to create a string of text that excel can use to cross-check the two sets of data.
In the second column, I would set up my cross check formula using IFERROR and VLOOKUP.
=IFERROR(VLOOKUP([Original DB Key column],[New DB Spreadsheet]![New DB Key column range],1,FALSE),"No Match")
In the opposite spreadsheet I would do the same, but reference the other spreadsheet:
=IFERROR(VLOOKUP([New DB Key column],[Original DB Spreadsheet]![Original DB Key column range],1,FALSE),"No Match")
Suggestions are welcome. I'm at a loss on how to modify this concept to account for this new split in data.
Please let me know if you have any questions! I'm happy to elaborate on anything that might be confusing.
Thanks,
BrigitteR
We recently switched to a shiny new database at my work, and I am trying to develop a report that will show me data discrepancies between the entries in both databases. We've been having our employees enter all data into both systems so that we have all of 2018's data in the old system (our "go live" date with the new database was mid-October 2018).
However, this requires some "translation" on my part because things are set up a bit differently in the new system. I can pull data from both systems, and I know how to translate them; the issue I am having is figuring out how to sum two dates (formatted mm/dd/yyyy hh:mm:ss) so that the row I use for comparison (the subtotal) has the start date/time of the first event, and the end date/time of the second, with a sum of the two events as the total elapsed time. (Essentially, in our old database we used only a single code. In our new database we are using two codes so we can be more accurate in our data reporting to the agencies that contract with us).
So, I'm going to try to give you a mock-up of the data I am trying to compare; hopefully it makes sense.
This is what the data from our old database looks like:
[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Client Name[/TD]
[TD]Service[/TD]
[TD]Location[/TD]
[TD]Event[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Elapsed Time[/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD]Last name, First name[/TD]
[TD]Crisis[/TD]
[TD]Main Campus[/TD]
[TD]JRC Time[/TD]
[TD]01/14/2019 10:05[/TD]
[TD]01/14/2019 13:30[/TD]
[TD]3.41[/TD]
[/TR]
</tbody>[/TABLE]
This is what this same information looks like when recorded using the new database:
[TABLE="class: grid, width: 1200, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Client Name[/TD]
[TD]Service[/TD]
[TD]Location[/TD]
[TD]Event[/TD]
[TD][Possible original DB translation column][/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Elapsed Time[/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD]Last name, First name[/TD]
[TD]Crisis[/TD]
[TD]Main Campus[/TD]
[TD]Awaiting Plan[/TD]
[TD]JRC Time[/TD]
[TD]01/14/2019 10:05[/TD]
[TD]01/14/2019 11:05[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD]Last name, First name[/TD]
[TD]Crisis[/TD]
[TD]Main Campus[/TD]
[TD]Crisis Time Out[/TD]
[TD]JRC Time[/TD]
[TD]01/14/2019 11:05[/TD]
[TD]01/14/2019 13:30[/TD]
[TD]2.41[/TD]
[/TR]
</tbody>[/TABLE]
So, I'm hoping that there is a way to utilize subtotals (or some Excel function) so that I can cross check these two bits of data.
With regards to start date and end date, the most important thing is that the calendar date matches and that the overall elapsed time matches. So, when cross checking, I just need to ensure that the day that the event began and and the day that the event ended, along with the total time, matches between the two systems.
(One thing to note: We are a 24 hour facility, so occasionally [as in maybe 2 out of every 50 clients] we will have data that will start on one calendar date, and end on another. So, client comes in at 2350 on 01/13/19, and is picked up at 1030 on 01/14/19. Start date for event in original system would be 01/13/2019 23:50. End date for event in original system would be 01/14/2019 10:30.
In new system event would be split into two events: The first one would run from 01/13/2019 23:50 - 01/14/2019 00:30. The second one would run from 01/14/2019 00:30 - 01/14/2019 10:30. I felt the need to mention this because I didn't know if it would complicate things).
Normally, when doing this type of data cross-checking, I would set up my workbook so that the data from one database is on one spreadsheet, and the data from the other database is on another spreadsheet within the same workbook. After I've done all of my formatting and added my "translation columns", I would then add two columns to each spreadsheet. In one column (titled "Key"), I would use concatenate to create a string of text that excel can use to cross-check the two sets of data.
In the second column, I would set up my cross check formula using IFERROR and VLOOKUP.
=IFERROR(VLOOKUP([Original DB Key column],[New DB Spreadsheet]![New DB Key column range],1,FALSE),"No Match")
In the opposite spreadsheet I would do the same, but reference the other spreadsheet:
=IFERROR(VLOOKUP([New DB Key column],[Original DB Spreadsheet]![Original DB Key column range],1,FALSE),"No Match")
Suggestions are welcome. I'm at a loss on how to modify this concept to account for this new split in data.
Please let me know if you have any questions! I'm happy to elaborate on anything that might be confusing.
Thanks,
BrigitteR
