Forum Discussion

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...

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

NikolinoDE

Gold 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

Copper 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
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
  Copper Contributor
  Apr 16, 2021
  HansVogelaar is a master too! He helped me a lot!
  But I did mark Baklans response.
  - 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.
    File1.xlsx17 KB