Forum Discussion

SP4NU7CH's avatar
SP4NU7CH
Frequent Visitor
8 years ago

Find multiple strings in 1 row, even duplicates

I'm struggling with to come up with an working formula. that will count/find the given set of string in a row. I either get no result, or 1 result.

 

e.g. row

 

Data A, Data B, Data A, Data C, John, Doe, Data E

 

the given set of strings what should be counted. ( I have these in a separate column, however these are fixed so it doesn't matter if I use the column or use plain text in the formula (so I have 5 formulas) )

Data A

Data B

Data C

Data D

Data E

 

So in the given example, I should have 2x A, 1x B, 1x C, 1x E. Rest does'nt matter. 

They are always comma separated, and you don't know how many strings there can be (so that rules out split column based on comma?)

 

Tried several formula setups. But can't seem to get it working. Any ideas? 

Need Help

4 Replies

  • SjoerdV's avatar
    SjoerdV
    Frequent Visitor
    8 years ago

    This should be fairly easy in Power Query. As fas as I understand, you have one field with the text-string in it. Split this into columns, based on the comma seperator. Then pivot your data and group by where you count each Data X.

     

    Let me know if it works!

    • SP4NU7CH's avatar
      SP4NU7CH
      Frequent Visitor
      8 years ago

      Forgot to mention, it's 1 column row, of many columns. Wouldn't your way create a-lot of duplicates? 

      • SjoerdV's avatar
        SjoerdV
        Frequent Visitor
        8 years ago

        Remove duplicates before splitting then :)

         

        Do you have a screenshot or a simplified exampje of your data?