Forum Discussion

Change99's avatar
Change99
Copper 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...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 17, 2021

    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.

NikolinoDE's avatar
NikolinoDE
Gold 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's avatar
Change99
Copper Contributor
Apr 16, 2021
HansVogelaar is a master too! He helped me a lot!
But I did mark Baklans response.
  • Change99's avatar
    Change99
    Copper Contributor
    Apr 20, 2021
    Yes thats exactly what was needed! Thank you 🙂
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 17, 2021

    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.

Resources