SOLVED

switch counts for each item

Iron Contributor

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 for each rnti how many switches happened example as below

for rnti = [28559] in column "E", should have count of all the switches from ueBwpId from 2->1 ,

anupambit1797_0-1717953445129.png

Thanks in Advance,

Br,

Anupam

 

3 Replies
best response confirmed by anupambit1797 (Iron Contributor)
Solution

@anupambit1797 

I interpreted your request slightly differently. The attached version uses a helper column on Sheet1.

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

anupambit1797_0-1717972598631.png

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

 

Thanks again :)

Br,

Anupam

@anupambit1797 

HansVogelaar_0-1718010180660.png

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.

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

@anupambit1797 

I interpreted your request slightly differently. The attached version uses a helper column on Sheet1.

View solution in original post