Forum Discussion

Brass Contributor

Apr 15, 2021

Solved

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.

File1.xlsx17 KB

Change99

Brass Contributor

Apr 15, 2021

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.

NikolinoDE

Platinum Contributor

Apr 15, 2021

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)

AAAAA_TEST PIVOT.xlsx17 KB

Change99
Brass Contributor
Apr 16, 2021
Thanks for trying! Unfortunately, as you suspected, it's not what I was looking for.

Your right Mr. Baklan really is the best! 🙂
- NikolinoDE
  Platinum Contributor
  Apr 16, 2021
  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
  - Change99
    Brass Contributor
    Apr 16, 2021
    HansVogelaar is a master too! He helped me a lot!
    But I did mark Baklans response.