Finding Duplicate values in column A using 2 criteria's from column B

[Tgandhi]

Hello all,

I have tried several formulas and ways but none works for me. Basically I need to find the list of accounts ( I am dealing with 10000 rows on daily basis) that are duplicate or repeated several times in the spreadsheet matching 2 criteria's from column B. So what I need help is to get a formula that basically highlights or produce unique value like "yes" or "1" so that I can apply maybe color filter and work on the rest of data on the sheet.

For e.g. on the attached excel sheet accounts 1324A, 1653C, 9825A are repeated multiple times, now using the criteria cash and security from column B I need to get a list that shows only accounts that are repeated ( but I need the list in this case 1234A is repeated 3 times so I need something that produces those account list with 1234A showing thrice) so that I can exclude them.

If you look at the snip, accounts: 1678B, 7894A and 5654B are the only accounts I need to work on for the daily basis and the rest accounts repeated multiple times needs to be excluded.

I have included the snip of the way the result should appear. Any help will be greatly appreciated!

 

[Tgandhi]

 

[Tgandhi]

Sorry guys, updated the snip with correct info.

Basically, the formula should capture duplicate accounts only if that account has both cash and security. In case if in column A accounts are repeated several times but in column B all the repeated entries has just cash in that case there should not be any unique value in column C as I can actually include that data. Basically we need isolate accounts that has both cash and security and if that accounts also is repeated multiple times than it produces unique value so only that data can be excluded and we can work on rest of the data.

Currently, I am using this formula =COUNTIFS($A$2:A2,A2,$B$2:B2,"Cash")*COUNTIFS($A$2:A2,A2,$B$2:B2,"Security")>0)*1. The issue with this formula is I have to manually figure out the duplicate rows for each account that are repeated and matches the criteria from column B Cash & security. So what I do know once I have unique value 1 I have to highlight the corresponding accounts that are repeated and then apply color filter and work on the rest of that data and with 10000 rows its taking too much time.

So in the above snip, 1678B, 7894A and 5654B are the only accounts I can work on. 1678B is repeated only 1 time so no issue, 7894A and 5654B are repeated 2 times but it does not have cash and security both so its ok.

Thanks again for your help!

Also asked here Finding Duplicate accounts in column A matching criteria column B

 

[Fluff]

How about

Excel Problem part 2.xlsx
	A	B	C	D
1	Account	Match	Unique Value
2	1234A	Cash	Yes or 1	Y
3	1234A	Security	Yes or 1	Y
4	1678B	Cash
5	1653C	Cash	Yes or 1	Y
6	1653C	Security	Yes or 1	Y
7	7894A	cash
8	7894A	Cash
9	5645B	security		Y
10	5645B	Security		Y
11	9825A	Cash	Yes or 1
12	9825A	Cash	Yes or 1
13	1234A	Security	Yes or 1	Y
14	1234A	Security	Yes or 1	Y
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=IF(COUNTIFS(A:A,A2,B:B,"<>cash")>0,"Y","")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$14	D2:D14

 

[Tgandhi]

Hello,
Thank you so much for all you help! It really means a lot.
The formula is great, I was wondering how to edit this formula further so that even row 9 & 10 (Account: 5645B - has multiple entries but with security only no cash meaning I don't need to have Y in D column just like row 7 & 8 for account# 7894A with cash only in both rows and also like row 11 & 12 for account# 9825A?
Basically in the above screenshot i need to have unique value in row 2,3,5,6,13 and 14 only as only those accounts are repeated multiple times or duplicate and has both cash and security in column B.

Thanks again!

 

[Fluff]

Ok, how about

=IF(COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,"Y","")