How can I unique records that have multiple locations. See example below

Record	State
532930	TX
533028	CA
533223	TX
532376	CA
532376	TX
532376	FL

In this scenario I'd like to be able to single out 523376 and its multiple states. I want to be able to report to team that 523376 has 3 states toed top it. How can I tackle this? Cheers and Happy NEW YEAR!

Common Use Cases

Accepted answers

FinnCharlton

Hello, I have attached one way to do this

Group By and Count Distinct.yxmd

gautiergodard

Hey @mustangs00

I believe you would be able to solve this using a summarize, see example attached.

Hope this helps! Please accept as solution is this solved your problem!

sum.yxmd

All comments

FinnCharlton

Hello, I have attached one way to do this

Group By and Count Distinct.yxmd

gautiergodard

Hey @mustangs00

I believe you would be able to solve this using a summarize, see example attached.

Hope this helps! Please accept as solution is this solved your problem!

sum.yxmd

mustangs00

This also solved my solution.Like the ability to see the multiple states as well. Thank you!

mustangs00

Came across an issue after analyzing data. What if record 532930 has multiple lines for one state? I really just need to isolate records with multiple different states.

mustangs00

Came across an issue after analyzing data. What if record 532930 has multiple lines for one state? I really just need to isolate records with multiple different states.

gautiergodard

@mustangs00 is this what you are looking for? to ID multiple line for one state, you would have to group by the ID and the State prior to performing a count.

In the attached, I used 532376 as an example with a duplicate FL line.

Hope this helps!

sum.yxmd

mustangs00

I think we are close. I just want to isolate the records with multiple unique states. In the example we are using, Is there a way to quickly be able to call out that record 532376 has 3 unique states associated with it?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703