Common items and their Count with a Specific Criteria

[anupambit1797]

Dear Experts,

                   Greetings!

                   In the attached Sample, I have an input- Worksheet, whose data is like this:-

a) Our interest point is Column "C" and "E";

b) Column "C"(subcellId) has values 9 /10/13 and each of these have SNs(Column E, Sequence Numbers which can range from 0~4095)

c) In the Output , sheet, I need to have , the Common SN values and the total count of them in

Column B and C, (by Common I mean if any of the SN value is common to 9 or 10 or 13:-

d) Important logic/criteria here is that for each SubcellId , once the 4095 is reached SNs will restart from 0... 4095 etc... and so on.. so I tried to do something like below:-

for example for subcellId ==9; I tried to break the SNs in sets (  each set has the SNs till 4095, and then another set starts and so on , and then try to make the common SN values for each set per subcellIds and their count.. but it may take more time..

Thanks in Advance,

Br,

Anupam

[#2 · Accepted solution]

@anupambit1797 

Clean up with PQ and then pivot.

In PowerQuery you'll do:

Stepping through this:

-table your source and load into PQ

-remove the second step which converts the SNs to numbers (and removes the comma)

-remove every column except sub cell and SN

-split the SN column by comma - specify 4 columns

-select the subcell column and go to Transform | Unpivot other columns

-remove the attribute column

-filter SN to remove empties

-load to sheet and pivot

-enjoy!