HI All, I need to pull a report of how many employees are using a single account at present date. Please find the sample table below. If I had to do it on excel the I would put a filter on column Account Number and if the count of rows is >=2 then I would pull that data. Any help will be greatly appreciated. Empl IDAutolinkPay GroupAccount NumberEffective Date100447605Y1K111110/30/201920061386WLQ111107/17/2019300465835Y1K222211/07/2019400325790Y1K222208/30/2019500325790Y1K222209/21/2019600325790Y1K333309/24/2019700325790Y1K333310/11/2019 expected output Empl IDAutolinkPay GroupAccount NumberEffective Date100447605Y1K111110/30/201920061386WLQ111107/17/2019300465835Y1K222211/07/2019400325790Y1K222208/30/2019500325790Y1K222209/21/2019

Find How many Employees are using the same account number

HI All,

I need to pull a report of how many employees are using a single account at present date. Please find the sample table below. If I had to do it on excel the I would put a filter on column Account Number and if the count of rows is >=2 then I would pull that data. Any help will be greatly appreciated.

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019
600	325790	Y1K	3333	09/24/2019
700	325790	Y1K	3333	10/11/2019

expected output

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019

Common Use Cases

Accepted answers

Qiu

@hemant86

So the Empl ID has to be unique.

0204-hemant86.yxmd

All comments

Qiu

@hemant86
why Account#3333 is not included in the output?

Qiu

@hemant86
Assuming there is a typo for account#3333. then we can try this.

0204-hemant86.yxmd

sazit

Hey Hemant,

Based on your question, if you want to filter for account numbers that appear twice or more than twice, the account 3333 should also be filtered through? Do let me know.

But if it is the case that you simply want to filter for records with account numbers appearing more than once then you would use a summarize tool and group by account number while also counting the account number. Then filter for count > 1 and join with the main data stream taking. Find below an example workflow. Hope this helps.

hemant_wf.yxmd

hemant86

Hi Qiu,

Sorry my bad. Let post the sample data again. So there can be duplicate rows as well.

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019
600	325790	Y1K	3333	09/24/2019
600	325790	Y1K	3333	10/11/2019

OutPut

Empl ID	Autolink	Pay Group	Account Number	Effective Date
100	447605	Y1K	1111	10/30/2019
200	61386	WLQ	1111	07/17/2019
300	465835	Y1K	2222	11/07/2019
400	325790	Y1K	2222	08/30/2019
500	325790	Y1K	2222	09/21/2019

hemant86

Thanks sazit for your response. That was a typo.Just responded to Qiu with corrected values

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703