Duplicate Values in Column A using criteria from Column B

[Tgandhi]

Hello all,

I have question related to excel that I have been trying to figure it out. Basically I am looking to produce duplicate from Column A(Account) only if both the criteria met from column B(Match). So the account number in column A should be repeated for both cash and security under column B and produce list of account who has both cash and security match type. Any help would be greatly appreciated.

Thank you.

 

[DanteAmor]

Hi and welcome to MrExcel
You can put the following formula in column C:

varios 27jul2020.xlsm
	A	B	C
1	Account	Mathc	Count
2	1234A	Cash	1
3	1234A	Security	2
4	1678B	Cash	1
5	1653C	Cash	1
6	1653C	Security	2
7	7894A	Cash	1
8	5645B	Security	1
9	9825A	Cash	1
Hoja2

Cell Formulas
Range	Formula
C2:C9	C2	=COUNTIFS($A$2:A2,A2,$B$2:B2,"Cash")+COUNTIFS($A$2:A2,A2,$B$2:B2,"Security")

Then filter column C by the value 2. In column A you will have the data:

varios 27jul2020.xlsm
	A	B	C
1	Account	Mathc	Count
3	1234A	Security	2
6	1653C	Security	2
Hoja2

 

[Tgandhi]

Hi Thank you so much for your response, I was wondering if there is any way that we can make sure that only duplicates are reflected only when that account has both security and cash 'Match type column B', because I just realized the sheet I am working on also has duplicate accounts with 2 times cash as well which is creating issue with the formula that you shared. So in the above excel if Account '1653C' repeated twice or thrice with Match under column B as "Cash' than it creates the issue with the formula, is there any easy way around it to fix it.

 

[DanteAmor]

varios 27jul2020.xlsm
	A	B	C
1	Account	Mathc	Count
2	1234A	Cash	1
3	1234A	Security	2
4	1678B	Cash	1
5	1653C	Cash	1
6	1653C	Security	2
7	1653C	Security	3
8	1653C	Cash	4
9	7894A	Cash	1
10	5645B	Security	1
11	9825A	Cash	1
Hoja2

Cell Formulas
Range	Formula
C2:C11	C2	=COUNTIFS($A$2:A2,A2,$B$2:B2,"Cash")+COUNTIFS($A$2:A2,A2,$B$2:B2,"Security")

In the example above I have the same account 4 times.
But you should filter only those with number 2:

varios 27jul2020.xlsm
	A	B	C
1	Account	Mathc	Count
3	1234A	Security	2
6	1653C	Security	2
Hoja2

 

[Tgandhi]

Hi thanks for the clarification, sorry I just want to find out what if account 1653c is repeated 4 times but with Match as only cash and not security, I tried the formula above in that case it dies jot produce the result.

 

[DanteAmor]

I tried the formula above in that case it dies

I'm sorry about that.
Try the following, here with more cases:

varios 27jul2020.xlsm
	A	B	C
1	Account	Match	Count
2	1234A	Cash	0
3	1234A	Security	1
4	1678B	Cash	0
5	1653C	Security	0
6	1653C	Security	0
7	1653C	Security	0
8	1653C	Security	0
9	7894A	Cash	0
10	5645B	Security	0
11	9825A	Cash	0
12	9825A	Security	1
13	111a	Cash	0
14	111a	Cash	0
15	111a	Cash	0
16	222a	Cash	0
17	222a	Cash	0
18	222a	Security	1
19	333c	Security	0
20	333c	Security	0
21	333c	Cash	1
Hoja2

Cell Formulas
Range	Formula
C2:C21	C2	=(COUNTIFS($A$2:A2,A2,$B$2:B2,"Cash")*COUNTIFS($A$2:A2,A2,$B$2:B2,"Security")>0)*1

Filter by 1

varios 27jul2020.xlsm
	A	B	C
1	Account	Match	Count
3	1234A	Security	1
12	9825A	Security	1
18	222a	Security	1
21	333c	Cash	1
Hoja2