Forum Discussion
Change99
Apr 15, 2021Brass Contributor
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 t...
- Apr 17, 2021
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.
Change99
Apr 15, 2021Brass Contributor
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.
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.
NikolinoDE
Apr 15, 2021Gold Contributor
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)
- Change99Apr 16, 2021Brass ContributorThanks for trying! Unfortunately, as you suspected, it's not what I was looking for.
Your right Mr. Baklan really is the best! 🙂- NikolinoDEApr 16, 2021Gold Contributor
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)
- Change99Apr 16, 2021Brass ContributorHansVogelaar is a master too! He helped me a lot!
But I did mark Baklans response.