I have an extremely large dataset that I'm needing to get sorted and filtered down

Question

EDIT: A well-placed pivot table was able to solve this issue. Thank y'all for the quick repsonses!

Long story short, I have over 211k rows of data that includes a "carrier ID" and a phone number that has been associated with it (with a lot of other, non-important information).
I need to figure out which carrier IDs are associated with which phone numbers. There are often multiple carrier IDs associated with each phone number and vice versa.
I thought I could use an INDEX function, but I had to use LEFT and RIGHT functions to pull the department and phone numbers apart from the single cell they were originally located in.

oliverscheurich · Answer

hvarner94&nbsp;Can you add a screenshot or a file without sensitive data which shows the layout of your dataset along with an example of the expected result?

peterbartholomew1 · Answer

hvarner94&nbsp;What is the desired form for the output?&nbsp; Some possibilities might includeI use 365.&nbsp; Without that, you might be best advised to use Power Query.= LET(
    carrier,   TEXTBEFORE(dataset, ", "),
    phone,     TEXTAFTER(dataset, ", "),
    distinctC, UNIQUE(carrier),
    distinctP, MAP(distinctC, LAMBDA(c,
       LET(
         p, UNIQUE(FILTER(phone, carrier=c)),
         TEXTJOIN(", ", , p)
       )
    )),
    SORT(HSTACK(distinctC, distinctP))
  )

Forum Discussion

I have an extremely large dataset that I'm needing to get sorted and filtered down

Share

Resources