Compare diffrently structured time periods for errors

[Change99]

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

[#2]

@Change99 

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

[#3]

Thanks for the info! I hope its understandable now.

[#4]

@Change99 

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.

[#5]

Thank you very much for your quick respond but I think there was a misunderstanding! As I understand it you took the data from file1 and calculated the sum of the time periods and then summarized your findings in the pivot table.
However I need to find the errors when comparing file1 and file2. 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.

[#6]

@Change99 

Have a look at this suggested solution in the inserted file ... although I don't think that's what you are looking for, my knowledge of Pivot ends here.

If maybe Mr. Baklan had time :))

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#7]

Thanks for trying! Unfortunately, as you suspected, it's not what I was looking for.

Your right Mr. Baklan really is the best! 🙂

[#8]

 There are many very good Excel experts here (do not mean me 🙂 like Mr. Sergei Baklan.

But such good insider knowledge, and knowledge of almost all Office versions, makes some people irreplaceable :).

As in the saying, "All are replaceable, only some people take a very, very long time" :).

The very good knowledge of English is always an advantage.

Since you got your help right away, I recommend that you rate Mr. Sergei Baklan's answer as the correct answer, so that other members can also benefit from it.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

@Change99

[#9]

@HansVogelaar is a master too! He helped me a lot!
But I did mark Baklans response.

[#10 · Accepted solution]

@Change99 

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]

Yes thats exactly what was needed! Thank you 🙂

[#12]

@Change99 , you are welcome