Remove values from some cells based on duplicates in one column

[HeatherF]

Hello,
I have a spreadsheet where I would like to remove duplicate values from columns A-E based on duplicates in column C (or B, either will work--not sure if it is easier to use a number or text column). I want to leave the A-E data in the first row that a name appears, but blank it out below, until a row with a new name.

I thought I had a solution using the formulas below, but it's blanking everything:
A1: =IF(C1=C2,"",A1)
B1: =IF(C1=C2,"",B1)
C1: =IF(C1=C2,"",C1)
D1: =IF(C1=C2,"",D1)
E1: =IF(C1=C2,"",E1)

The tables below are TABLEA: What I'm starting with; TABLEB: What I want to achieve; TABLEC: What happens when I apply the formulas.

Thank you for your help!!

Heather

Book1
	A	B	C	D	E	F	G	H	I	J
1	Affiliation	ID#	Pref Name w Suffix	Rating	5-yr Giving	Relation Id	Relation Type	Relation Pref Name w Suffix	Relation Rating	Relation 5-yr Giving
2	PHYSICS	10023868	Mr. Doug M. Worman '96	CW	$ 170.00	7009006	Parent	Mr. Matthias B. Worman '70 P'05 '96	6	$ 28,000.00
3	PHYSICS	10023868	Mr. Doug M. Worman '96	CW	$ 170.00	10043556	Sibling	Mrs. Suzy Lantsman '05	CW	$ 450.00
4	PHYSICS	10023868	Mr. Doug M. Worman '96	CW	$ 170.00	10022938	Parent	Mrs. Matthias B. Worman P'05 '96	6	$ -
5	CREW	10024167	Ms. Mickey J. Nesmith '96	6	$ 12,500.00	10024647	Parent	Mr. & Mrs. John F. Nesmith P'96	8	$ -
6	AGENTS	10025692	Mr. John Forge '97	9	$ 2,750.00	10015288	Sibling	Ms. Kristin J. Forge '92	8	$ 400,000.00
7	AGENTS	10025692	Mr. John Forge '97	9	$ 2,750.00	10073058	Inlaw	Mr. Steve Casey	8	$ -
8	AGENTS	10025692	Mr. John Forge '97	9	$ 2,750.00	6529599	Parent	Dr. H. James Forge (dec'd) '65 P'97 '92	9	$ 100,215.00
9	AGENTS	10025692	Mr. John Forge '97	9	$ 2,750.00	10029576	Parent	Mrs. Alison Forge H'65 P'97 '92	9	$ -
10	FYE	10025906	Mr. Mike D. Ransick '97	CW	$ -	10008283	Inlaw	Mr. Michael J. Swiss '89 P'20	6	$ 280,620.00
11	FYE	10025906	Mr. Mike D. Ransick '97	CW	$ -	10076742	Inlaw	Ms. Kristen E. Swiss P'20	6	$ 7,500.00
12	FYE	10025906	Mr. Mike D. Ransick '97	CW	$ -	10076736	Niece	Ms. Cecilia A. Swiss '20	CW	$ -
13	CREW	10028268	Mrs. Laura Douglas '97	CW	$ 850.00	10084011	Stepparent	Ms. Laura Baker	6	$ -
14	CREW	10028268	Mrs. Laura Douglas '97	CW	$ 850.00	6221340	Parent	Mr. Carl B. Douglas '62 P'98 '92	6	$ 4,366.41
15	CREW	10028268	Mrs. Laura Douglas '97	CW	$ 850.00	10015662	Sibling	Mr. Blake T. Douglas '92	CW	$ 100.00
16	CREW	10028268	Mrs. Laura Douglas '97	CW	$ 850.00	3421331	Grandparent	Dr. Carl Douglas, Jr. (dec'd) '34 P'62 GP'98 '92	U	$ -
17	PHYSICS	10028413	Mrs. Jennifer Maitland '98		$ 1,286.90
18	CREW	10030463	Ms. Winter Morgan '99	CW	$ 45,000.00	10030843	Parent	Mr. & Mrs. Joseph Wallis P'99	7	$ -
TABLEA

Book1
	A	B	C	D	E	F	G	H	I	J
1	Affiliation	ID#	Pref Name w Suffix	Rating	5-yr Giving	Relation Id	Relation Type	Relation Pref Name w Suffix	Relation Rating	Relation 5-yr Giving
2	PHYSICS	10023868	Mr. Doug M. Worman '96	CW	$ 170.00	7009006	Parent	Mr. Matthias B. Worman '70 P'05 '96	6	$ 28,000.00
3						10043556	Sibling	Mrs. Suzy Lantsman '05	CW	$ 450.00
4						10022938	Parent	Mrs. Matthias B. Worman P'05 '96	6	$ -
5	CREW	10024167	Ms. Mickey J. Nesmith '96	6	$ 12,500.00	10024647	Parent	Mr. & Mrs. John F. Nesmith P'96	8	$ -
6	AGENTS	10025692	Mr. John Forge '97	9	$ 2,750.00	10015288	Sibling	Ms. Kristin J. Forge '92	8	$ 400,000.00
7						10073058	Inlaw	Mr. Steve Casey	8	$ -
8						6529599	Parent	Dr. H. James Forge (dec'd) '65 P'97 '92	9	$ 100,215.00
9						10029576	Parent	Mrs. Alison Forge H'65 P'97 '92	9	$ -
10	FYE	10025906	Mr. Mike D. Ransick '97	CW	$ -	10008283	Inlaw	Mr. Michael J. Swiss '89 P'20	6	$ 280,620.00
11						10076742	Inlaw	Ms. Kristen E. Swiss P'20	6	$ 7,500.00
12						10076736	Niece	Ms. Cecilia A. Swiss '20	CW	$ -
13	CREW	10028268	Mrs. Laura Douglas '97	CW	$ 850.00	10084011	Stepparent	Ms. Laura Baker	6	$ -
14						6221340	Parent	Mr. Carl B. Douglas '62 P'98 '92	6	$ 4,366.41
15						10015662	Sibling	Mr. Blake T. Douglas '92	CW	$ 100.00
16						3421331	Grandparent	Dr. Carl Douglas, Jr. (dec'd) '34 P'62 GP'98 '92	U	$ -
17	PHYSICS	10028413	Mrs. Jennifer Maitland '98		$ 1,286.90
18	CREW	10030463	Ms. Winter Morgan '99	CW	$ 45,000.00	10030843	Parent	Mr. & Mrs. Joseph Wallis P'99	7	$ -
TABLEB

Book1
	A	B	C	D	E	F	G	H	I	J
1	Affiliation	ID#	Pref Name w Suffix	Rating	5-yr Giving	Relation Id	Relation Type	Relation Pref Name w Suffix	Relation Rating	Relation 5-yr Giving
2		0	0	0	$ -	7009006	Parent	Mr. Matthias B. Worman '70 P'05 '96	6	$ 28,000.00
3		0	0	0	$ -	10043556	Sibling	Mrs. Suzy Lantsman '05	CW	$ 450.00
4		0	0	0	$ -	10022938	Parent	Mrs. Matthias B. Worman P'05 '96	6	$ -
5		0	0	0	$ -	10024647	Parent	Mr. & Mrs. John F. Nesmith P'96	8	$ -
6		0	0	0	$ -	10015288	Sibling	Ms. Kristin J. Forge '92	8	$ 400,000.00
7		0	0	0	$ -	10073058	Inlaw	Mr. Steve Casey	8	$ -
8		0	0	0	$ -	6529599	Parent	Dr. H. James Forge (dec'd) '65 P'97 '92	9	$ 100,215.00
9		0	0	0	$ -	10029576	Parent	Mrs. Alison Forge H'65 P'97 '92	9	$ -
10		0	0	0	$ -	10008283	Inlaw	Mr. Michael J. Swiss '89 P'20	6	$ 280,620.00
11		0	0	0	$ -	10076742	Inlaw	Ms. Kristen E. Swiss P'20	6	$ 7,500.00
12		0	0	0	$ -	10076736	Niece	Ms. Cecilia A. Swiss '20	CW	$ -
13		0	0	0	$ -	10084011	Stepparent	Ms. Laura Baker	6	$ -
14		0	0	0	$ -	6221340	Parent	Mr. Carl B. Douglas '62 P'98 '92	6	$ 4,366.41
15		0	0	0	$ -	10015662	Sibling	Mr. Blake T. Douglas '92	CW	$ 100.00
16		0	0	0	$ -	3421331	Grandparent	Dr. Carl Douglas, Jr. (dec'd) '34 P'62 GP'98 '92	U	$ -
17		0	0	0	$ -
18		0	0	0	$ -	10030843	Parent	Mr. & Mrs. Joseph Wallis P'99	7	$ -
TABLEC

Cell Formulas
Range	Formula
A2:A18	A2	=IF(C2=C3,"",A2)
B2:B18	B2	=IF(C2=C3,"",B2)
C2:C18	C2	=IF(C2=C3,"",C2)
D2:D18	D2	=IF(C2=C3,"",D2)
E2:E18	E2	=IF(C2=C3,"",E2)

 

[johnnyL]

Reduce your C values by 1 in your formulas.

ie. =IF(C1=C2,"",A2) etc.

 

[HeatherF]

Reduce your C values by 1 in your formulas.

ie. =IF(C1=C2,"",A2) etc.

Thank you, of course, that makes sense. Can I ask something really dumb? I can't seem to add formulas to cells without deleting the cell values. When I click fx it brings up the formula builder dialog but I just want to paste a formula. I swear it used to just insert an = . Did I mess up a setting?

 

[johnnyL]

If you just want to paste a formula, paste it into the long box to the right of the 'fx'

You can't use the formulas in the manner that you are trying. As you noticed, when you put a formula in a cell it will replace the current value of the cell.

I believe you need to use vba macro code to accomplish your goal.

 

[HeatherF]

I have tried to figure out a VBA macro. That would be ideal. I entered the one below, but it removes the entire row with duplicate data, and I only want to remove the values.

Sub removeDuplicate()
'removeDuplicate Macro
Columns("C:C").Select
ActiveSheet.Range("$A$1:$J$18").RemoveDuplicates Columns:=Array(1), _
Header:=xlNo
Range("A1").Select
End Sub

 

[johnnyL]

How about this:

Sub RemoveSomeCells()
'
    Dim ArrayRow        As Long
    Dim LastRowInSheet  As Long
    Dim SavedArray      As Variant
'
    LastRowInSheet = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row              ' Returns a Row Number
'
    SavedArray = Range("A1:E" & LastRowInSheet)                                             ' Save values into 2 dimensional 1 based Array ... 5 wide
'
    For ArrayRow = UBound(SavedArray) To 2 Step -1                                          ' Work backwards through rows because we will be deleting values
        If SavedArray(ArrayRow, 3) = SavedArray(ArrayRow - 1, 3) Then                       '   If This row value = previous row value then ...
            SavedArray(ArrayRow, 1) = vbNullString                                          '       Delete the value from the A column
            SavedArray(ArrayRow, 2) = vbNullString                                          '       Delete the value from the B column
            SavedArray(ArrayRow, 3) = vbNullString                                          '       Delete the value from the C column
            SavedArray(ArrayRow, 4) = vbNullString                                          '       Delete the value from the D column
            SavedArray(ArrayRow, 5) = vbNullString                                          '       Delete the value from the E column
        End If
    Next                                                                                    ' Loop back to do next check
'
    Range("A1:E" & LastRowInSheet) = SavedArray                                             ' Print the saved array back to the sheet

End Sub

I left the code a bit long to make it easier to understand.

 

[HeatherF]

Hi again, I am trying to adjust this to my real spreadsheet, and the columns where I'd like to delete dupe values is A-X. Since I edited the code, it's just deleting columns A-X after row 2. What am I doing wrong? Thanks so much for your help!

Sub RemoveSomeCells()
'
    Dim ArrayRow        As Long
    Dim LastRowInSheet  As Long
    Dim SavedArray      As Variant
'
    LastRowInSheet = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row              ' Returns a Row Number
'
    SavedArray = Range("A1:X" & LastRowInSheet)                                             ' Save values into 2 dimensional 1 based Array ... 24 wide
'
    For ArrayRow = UBound(SavedArray) To 2 Step -1                                          ' Work backwards through rows because we will be deleting values
        If SavedArray(ArrayRow, 3) = SavedArray(ArrayRow - 1, 3) Then                       '   If This row value = previous row value then ...
            SavedArray(ArrayRow, 1) = vbNullString                                          '       Delete the value from the A column
            SavedArray(ArrayRow, 2) = vbNullString                                          '       Delete the value from the B column
            SavedArray(ArrayRow, 3) = vbNullString                                          '       Delete the value from the C column
            SavedArray(ArrayRow, 4) = vbNullString                                          '       Delete the value from the D column
            SavedArray(ArrayRow, 5) = vbNullString                                          '       Delete the value from the E column
            SavedArray(ArrayRow, 6) = vbNullString                                          '       Delete the value from the F column
            SavedArray(ArrayRow, 7) = vbNullString                                          '       Delete the value from the G column
            SavedArray(ArrayRow, 8) = vbNullString                                          '       Delete the value from the H column
            SavedArray(ArrayRow, 9) = vbNullString                                          '       Delete the value from the I column
            SavedArray(ArrayRow, 10) = vbNullString                                          '       Delete the value from the J column
            SavedArray(ArrayRow, 11) = vbNullString                                          '       Delete the value from the K column
            SavedArray(ArrayRow, 12) = vbNullString                                          '       Delete the value from the L column
            SavedArray(ArrayRow, 13) = vbNullString                                          '       Delete the value from the M column
            SavedArray(ArrayRow, 14) = vbNullString                                          '       Delete the value from the N column
            SavedArray(ArrayRow, 15) = vbNullString                                          '       Delete the value from the O column
            SavedArray(ArrayRow, 16) = vbNullString                                          '       Delete the value from the P column
            SavedArray(ArrayRow, 17) = vbNullString                                          '       Delete the value from the Q column
            SavedArray(ArrayRow, 18) = vbNullString                                          '       Delete the value from the R column
            SavedArray(ArrayRow, 19) = vbNullString                                          '       Delete the value from the S column
            SavedArray(ArrayRow, 20) = vbNullString                                          '       Delete the value from the T column
            SavedArray(ArrayRow, 21) = vbNullString                                          '       Delete the value from the U column
            SavedArray(ArrayRow, 22) = vbNullString                                          '       Delete the value from the V column
            SavedArray(ArrayRow, 23) = vbNullString                                          '       Delete the value from the W column
            SavedArray(ArrayRow, 24) = vbNullString                                          '       Delete the value from the X column
        End If
    Next                                                                                    ' Loop back to do next check
'
    Range("A1:X" & LastRowInSheet) = SavedArray                                             ' Print the saved array back to the sheet

End Sub

 

[johnnyL]

Your question confuses me. Row 2 is the first row of data, at least it was in your original post.

If it is the first row of data, it can't be a duplicate to anything above it, therefore it won't be deleted.

Perhaps you can post some more of what you are expecting vs what you are getting.

 

[HeatherF]

Sorry, that was unclear. The code you gave me worked perfectly—deleting duplicate values in columns A-E while preserving the first instance of those values. So when A-E repeats in rows 2,3,4 but new values are in row 5, A-E are deleted in rows 3,4 but preserved in rows 2 & 5.

When I tried to expand the code to include columns A-X, now it is preserving row2, but deleting the values in A-X from row 3 onwards, whether it’s a duplicate of the row above it or not.

 

[johnnyL]

I don't see an issue with the code you posted. Have you tried a reboot of the computer and then test it?

If you could post the data like you did in post 1 so we can test it to see if we can duplicate the problem, that would be my suggestion.