How many times does a text value from one column appear in another

[alex101]

Hi

I am trying to work out how many times a text value from one column appears in another column. I have a single table with two fields. I want to count how many times the employee appears in the managers column.

For instance for employee 11 it should calculate 0, as it doesn't appear in the managers column at all, whilst employee 77 would calculate as 6 as it appears in the managers column 6 times etc.

Any help is much appreciated - thank you

Employee          Managers

11                      22 | 33 | 44 | 55 | 66 | 77

22                      33 | 44 | 55 | 66 | 77

33                      44| 55 | 66 | 77

44                      55 | 66 | 77

55                      66 | 77

66                      77

77

[Mariusz]

Hi @alex101 

Please see the attached file with a solution using Power Query Editor included

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[alex101]

Thank you Mariusz this does work and therefore I hate to have a however. However as my data is about 30,000 rows before splitting by the delimiter it's taking hours to do that last step - so many that I've not managed to finish all the steps.

If there is another solution that would be appreciated!

Thank you

[Mariusz]

Hi @alex101 

Sure, try the new one on the attached, if it's still too slow, please can you share the file with Id's only?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[alex101]

yes I'm sorry that was also too slow - I've decided it's just much quicker to use powerBI to determine the employees' managers using PATH and then export to excel and use simple countif to do this and then put it back in powerbi. Seems to be much quicker and doesn't take as long.

Thank you so much for taking the time to think up some solutions for me - I really appreciate that.

Alex