Forum Discussion
Compare diffrently structured time periods for errors
Dear Excel community,
any help would be greatly appreciated!
I have 2 Excel files (attached) that look like the attachted screenshots which I need to compare for any errors. They are the same but they are structured diffrently. There are special cases coded like this: time period 00:00 - 00:00 which should be ignored. Basically the time periods need to be the same. If they are not the same I need to highlight the mistake. In this example they are no errors.
*Edit:
I am looking for the following type of error: One file has a time period which is not listed in the other file. The sum of hours for one day could be the same even though the time periods are not the same. I am not proficient in Excel but I think that some kind of Macro which restructers file1 to look like file2 (excluding the 00:00-00:00 time periods) would be good.
(File1)
(File2)
Info:
Windows Version: Microsoft Windows 10 Pro
Excel Version: Microsoft Office Standard 2016
Not sure I understood correctly what is required. If the goal is to convert range as in File1 to one which is in File2
(from most left here to most right one), variant could be using formulas:
in F2
=IFERROR(INDEX(B:B, AGGREGATE(15,6,1/ ($B$2:INDEX($B:$B,COUNTA($B:$B))<>$C$1:INDEX($C:$C,COUNTA($B:$B)-1)) / ($B$2:INDEX($B:$B,COUNTA($B:$B)) <> 0) * ROW($B$2:INDEX($B:$B,COUNTA($B:$B))), ROW()-ROW($F$1) )), "")copy and paste this cell to E2.
In G2
=IFERROR(INDEX(C:C, AGGREGATE(15,6,1/ ($C$2:INDEX($C:$C,COUNTA($B:$B))<>$B$3:INDEX($B:$B,COUNTA($B:$B)+1)) / ($B$2:INDEX($B:$B,COUNTA($B:$B)) <> 0) * ROW($B$2:INDEX($B:$B,COUNTA($B:$B))), ROW()-ROW($F$1) )), "")Select E2:G2 and drag down till empty cells appear.
11 Replies
Please read this information. It is of great help to you and everyone who would like to help.
https://techcommunity.microsoft.com/t5/excel/welcome-to-your-excel-discussion-space/m-p/2204395
Then, if I may recommend you, add a file (without sensitive data) and explain exactly what your plan is.
Thank you for your understanding and patience 🙂
Nikolino
- Thanks for the info! I hope its understandable now.
There are always many ways of doing this in Excel.
Here is a small example with pivot, where you can expand the table and continue to calculate all times.
The time has been converted to decimal calculation: (Endtime - Startime) * 24
You can change this as you wish, see the formula in the table.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
