Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Jun 09, 2024

switch counts for each item

Dear Experts,                    I have a Data (as attached ), In column "D", we have item name as "rnti" and each rnti can have switch in ueBwpId (from 0>1>2), in sheet2, I want to make a summary...
BI & Data Analysis
Excel
Formulas and Functions
Power BI
anupambit1797's avatar
anupambit1797
Iron Contributor
Jun 09, 2024

Thanks a lot HansVogelaar , this is exactly what I wanted, but could you please educate me on this "helper column"?

What does this Column "F" in sheet1 does? if you can help explain the criteria you used?

 

Thanks again 🙂

Br,

Anupam

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 10, 2024

    anupambit1797 

    The formula in F587 is

    =LET(r, MAX(IF($D$2:$D586=$D587, ROW($D$2:$D586))), p, IF(r=0, "", INDEX($E$2:$E586, r-1)), IF(OR(p="", p=E587), "", p&">"&E587))

     

    r = MAX(IF($D$2:$D586=$D587, ROW($D$2:$D586))) calculates the highest row number of a cell in column D above D587 with the same rnti as D587. If there is no such row, r = 0.

     

    p = IF(r=0, "", INDEX($E$2:$E586, r-1)) returns "" if r = 0, otherwise it returns the ueBpwld of that row, i.e. the previous ueBpwld.

     

    Finally, the formula returns IF(OR(p="", p=E587), "", p&">"&E587).

    If the previous ueBpwld is blank or equal to the current ueBpwld in E587(i.e. no switch), it returns a blank. Otherwise, it concatenates the previous and current ueBpwld with ">" in between, i.e. the switch.

     

    The formulas on the second sheet simply use COUNTIFS to count the number of switches for the rnti in column A.

Resources