Make a table of tables according to date and time

[Krakas95]

Hello, I am a newbie in excel, I am a C. Sc. graduate and doing my internship now. I have only used a few formulas and varius sort and rules.
I have 2 tables (2 companies) that contain almost the same data and is sorted according to date, then time and then is the date of each. I want to check that the data between them are correct or similar. I brute force it, by checking the difference between the 2 rows. If the diff is 0 or in a range of - 5 to 5 then the data is correct. One problem that occurs is in the 2nd table we have anomalies (random 0s and 1s) which are removed by rules. The biggest problem is that the second table the data might not be ordered perfectly since the first table is ordered according to seconds but the 2nd is just minutes and not seconds, and I have to check each number by it self. We have for example 10 rows for each minute and 1000 for each hour and since it is not order the diffs are messed up.

My programming approach is to break the 2 tables into smaller tables according to date and then according to time and check the diff of each element on the 1st with each on the 2nd. If the results are in the range above then it is correct. Anomalies should be entered in a new table.

This is just an idea but since I never used excel before I am lost, any help? My colleagues are brute forcing there way in this for 5 years and called me to find a way hahahahaha!!

Thank you very much

 

[jkpieterse]

Have you got some sample data perhaps? Given that the sample frequency is not the same, how do you currently decide if samples with seconds interval match with samples with minutes interval?

 

[Krakas95]

Have you got some sample data perhaps? Given that the sample frequency is not the same, how do you currently decide if samples with seconds interval match with samples with minutes interval?

 

[Krakas95]

Have you got some sample data perhaps? Given that the sample frequency is not the same, how do you currently decide if samples with seconds interval match with samples with minutes interval?

I have images but they are too large, I will try to recreate a section of it and upload it

 

[Krakas95]

Hello again, I did some research and found how the record macro and relative reference work, at some extend. The image below is a dummy of my worksheet, you can see that I have 2 tables, and the 3rd one is the difference between the 2. As you can see, the colored cells in table 3 are the false rows that need to be checked. If the diff is between -5 and 5 then the row is correct, for example, I am trying to find how accurate the 2nd table is, relative to the 1st one. The original worksheet is separated in 7 dates(a week) and then in time, increasing one minute at a time. Each minute section might have from 1 data to 20, and so on. The same happens for both tables. The problem here, is the 2nd table is not sorted according to the seconds of each minute, therefore, the order is uncertain. The brute force method is to check each row and find any anomalies and use sorting by color to replace at the bottom of the table. There is one type of anomaly shown in the table, at r16, q16, there is a cell containing 1. This is an anomaly that needs to be removed so the rows below would move up and the table would be fixed, for example, (You can ignore the date column changed for security, you can assume they are all 1/8/2021).
What I found and helped a lot is sorting each time section (for example all data in sub-table 9:19:00) according to the 3rd row of each table, by doing so, if an anomaly exists or if there is an extra data row in either one of the table it will be shown and fixed(the example with the cell containing 1s). Another problem here is that each section has different lengths and I can't use relative reference I think. I need to collect all the data of each section that has for example date 1/8 and time 00:01 for both tables sort them and then if correct with an accuracy of 98-99 then go to next section 1/8 and time 00:02.

I hope my explanation is understandable and sorry if it is hard to understand my problem.

 

[Krakas95]

l

 

[jkpieterse]

OK here is what I did.
- convert both tables to a table (Start tab, format as table). I named the left most one Table1 and the right one Table2.
- Both tables have these headings: Date, Time, s
- Add a column to the left table name it TimeNoSecs
- Insert this formula: =TEXT([@time],"hh:mm")*1
- Add a column to the right table with this formula:

=ABS(AVERAGEIFS(Table1[s],Table1[date],[@date],Table1[TImeNoSecs],[@time])-[@s])>=5

 

[jkpieterse]

NB: change the >=5 to whatever your limit must be

 

[Krakas95]

OK here is what I did.
- convert both tables to a table (Start tab, format as table). I named the left most one Table1 and the right one Table2.
- Both tables have these headings: Date, Time, s
- Add a column to the left table name it TimeNoSecs
- Insert this formula: =TEXT([@time],"hh:mm")*1
- Add a column to the right table with this formula:

=ABS(AVERAGEIFS(Table1[s],Table1[date],[@date],Table1[TImeNoSecs],[@time])-[@s])>=5

View attachment 45098

can you explain the results of rows 2, 16, 17, 21?

 

[jkpieterse]

Well, I'm comparing the individual results of the right-hand table with the averages of the same minute in the left-hand table, so...