Count formula with multiple criteria

[shdawson]

Good Morning.


Need to do a logic evaluation. Thinking the COUNT function is the way to get there, but not certain.

Have this test data.

Excel Workbook
	C	D	E
1	Multiple	Dude	Team
2	N	Bob	Network
3	N	Jim	Database
4	Y	John	Database
5	Y	John	Dev
6	N	Tom	Security
Sheet1

Excel 2003

Need to find what columns in column D have more than 1 entry. Flag those in column C.

Any "simple" way to evaluate multiple columns in a counting action?


Thanks,
S

 

[prajul89]

I think id107's formula is better as there is no need for concatenate.

 

[shdawson]

OK, nice trick.

However, I need to flag all of the rows as duplicate, so we can decide other criteria (not listed in this test data) to determine what records to purge.

Current Test Data

Excel Workbook
	A	B	C	D	E
1	DudeTeam	FORMULA	Multiple Entry	Dude	Team
2	BobNetwork	N	N	Bob	Network
3	JimDatabase	N	N	Jim	Database
4	JohnDev	N	N	John	Dev
5	John A.Database	N	Y	John A.	Database
6	John A.Database	Y	Y	John A.	Database
7	John B.Database	Y	N	John B.	Database
8	TomSecurity	N	N	Tom	Security
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A1	=D1&E1
A2	=D2&E2
A3	=D3&E3
A4	=D4&E4
A5	=D5&E5
A6	=D6&E6
A7	=D7&E7
A8	=D8&E8
B2	=IF(COUNTIF(A:A,A1)=1,"N","Y")
B3	=IF(COUNTIF(A:A,A2)=1,"N","Y")
B4	=IF(COUNTIF(A:A,A3)=1,"N","Y")
B5	=IF(COUNTIF(A:A,A4)=1,"N","Y")
B6	=IF(COUNTIF(A:A,A5)=1,"N","Y")
B7	=IF(COUNTIF(A:A,A6)=1,"N","Y")
B8	=IF(COUNTIF(A:A,A7)=1,"N","Y")

Need to flag B5 as a Y as well. How can your logic get us there?

Thank you very much!!!


S

 

[prajul89]

Its actually because you have place Formulas incorrectly.
In Formulas of B columns make sure the formula has reference to the same row.
In yours it is giving the reference of the above row.
So the formula in B2 should be
=IF(COUNTIF(A:A,A2)=1,"N","Y")

 

[id107]

B2 should be =IF(COUNTIF(A:A,A2)=1,"N","Y")

 

[shdawson]

Got it. The formula now works as preferred.

Thank you very, very much.



Kindly,
SHD