re extracting duplicate records.

[Gerard.L]

I am fairly new to Excel. Lately have been using a sumproduct formula to compare rows and colums to find duplicate records for genealogy, Name, Date of Birth, Date of Death, however if the text is in all CAPS or Mixed, this formula does not seem to pick out the fact it is the same. Is there any way to cope with this problem? or do i need to paste the column into word and use the change case button to convert all to upper or lower case and then paste back to excel? Any help will be appreciated.

 

[Dryver14]

You have the upper and lower functions to wrap parts of your formula in

so you could lookup upper(A1) and what ever is in the cell will read as uppercase or lowercase depanding on your search values

 

[mgirvin]

In the example here, I am not having problems with matching words that have the same latters, but different cases:

Excel Workbook
	A	B	C	D	E
1				2007 or later	2003 or ealier
2	Name	Date of Birth	Date of Death	Is Record a Duplicate?	Is Record a Duplicate?
3	Name 1	12/7/1932	12/7/2011	FALSE	FALSE
4	Name 2	8/8/1950	12/7/2011	FALSE	FALSE
5	Name 3	1/9/1944	12/7/2011	FALSE	FALSE
6	NAME 1	12/7/1932	12/7/2011	TRUE	TRUE
7	Name 2	8/8/1950	12/7/1999	FALSE	FALSE
8	NaME 1	12/7/1932	12/7/2011	TRUE	TRUE
9	Name 2	8/8/1950	12/7/2011	TRUE	TRUE
Sheet1

D3 has this formula and then copied down column:

=COUNTIFS(A$3:A3,A3,B$3:B3,B3,C$3:C3,C3)>1

E3 has this formula and then copied down column:

=SUMPRODUCT(--(A$3:A3=A3),--(B$3:B3=B3),--(C$3:C3=C3))>1

Maybe there is some other discrepancy that is causing the problem of no match. Maybe extra spaces?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o></o>
As Gerard.L suggests, there is no need to go to Word to "convert all to upper or lower case". There are functions in Excel that could do that: UPPER and LOWER, but you should not need to use these because Excel sees "Name 1" as equivalent to "name 1". Like:<o></o>

Excel Workbook
	A	B	C
1	Name 1	NAME 1	TRUE
2	NaME 1	NAME 2	FALSE
3	Name 1	NaME 1	TRUE
4	nAME 1	NaME 1	TRUE
Sheet2

Cell Formulas
Range	Formula
C1	=B1=A1
C2	=B2=A2
C3	=B3=A3
C4	=B4=A4

 

[Gerard.L]

Thank you both for the kind replies and your wisdom. Am going to try both approaches, the solutions are both excellent and I can use them for other problems scimilar that I need to solve. You are a blessing.

Regards.