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)

 

[HeatherF]

Sure, thanks. The first table is my spreadsheet. The second is what happens when I run the vba module above. The third is what I want it to do.

testtable2.xlsm
	A	B				F				J	K	L							S						Y	Z					AE		AG	AH	AI						AO	AP
1	Affiliation	ID#				Pref Name w Suffix				Rating	Home City	Home State							5-yr Giving						Relation Id	Relation Type					Relation Pref Name w Suffix		Relation Rating	Relation Home City	Relation Home State						Relation 5-yr Giving	Relation Jnt LTG
2	CREW	10030321				Mrs. Lana Shen '99				8	Palo Alto	CA							216400						10057207	Sibling					Ms. Nana Shen '11		N	Palo Alto	CA						190	241
3	CREW	10030321				Mrs. Lana Shen '99				8	Palo Alto	CA							216400						10030933	Parent					Mr. Jim and Mrs. Jennifer Shen P'11 '01 '99		U	Palo Alto	CA						0	50
4	CREW	10030321				Mrs. Lana Shen '99				8	Palo Alto	CA							216400						10084002	Spouse/Partner					Mr. James Updike		8	Palo Alto	CA						0	217585
5	CREW	10030321				Mrs. Lana Shen '99				8	Palo Alto	CA							216400						10035814	Sibling					Ms. Rachel Shen '01		10	Palo Alto	CA						180	13505
6	CREW/FYE	10026142				Ms. Jessica Smith '97 P'24													100650.24						10081652	Parent					Mrs. Kathleen McKay P'97 '90		6	Sedona	AZ						0	3442903.56
7	CREW/FYE	10026142				Ms. Jessica Smith '97 P'24													100650.24						10046174	Cousin					Ms. Sara Vessi '06		CW								144	288
8	CREW/FYE	10026142				Ms. Jessica Smith '97 P'24													100650.24						10026132	Spouse/Partner					Mr. Gavin D. Smith '97 P'24		8								35650.25	129800.49
9	CREW/FYE	10026142				Ms. Jessica Smith '97 P'24													100650.24						743	Grandparent					Mr. Peter M. McKay (dec'd) P'71 GP'97 '90		4								0	378100
10	CREW/FYE	10026142				Ms. Jessica Smith '97 P'24													100650.24						10046176	Aunt					Ms. Diana McKay P'06		7								25000	83279.95
11	AGENTS	10026100				Mr. Jeffrey Kong '97				7	San Francisco	CA							75550
12	FYE	10026121				Mr. Jonathan J. Hatch '97				7	Ketchum	ID							57500						6759409	Parent					Mr. Joseph E. Hatch III '67 P'97 '95		U	Denver	CO						500	10785
13	FYE	10026121				Mr. Jonathan J. Hatch '97				7	Ketchum	ID							57500						10022091	Sibling					Jennifer C. Hatch '95		U	Denver	CO						0	5200
14	FYE	10026178				Mr. Guillermo Stevens '97				7									45131.97						10026319	Parent					Mrs. Ines Stevens P'12 '97		U	Arlington	VA						365.15	3915.45
15	FYE	10026178				Mr. Guillermo Stevens '97				7									45131.97						10079908	Parent					Mr. Guillermo Stevens P'12 '97			Arlington	VA						465.12	3915.45
16	FYE	10026178				Mr. Guillermo Stevens '97				7									45131.97						10059330	Sibling					Mr. Rodrigo Stevens '12		U	Arlington	VA						40	160
Sheet1

testtable2.xlsm
	A	B				F				J	K	L							S	T					Y	Z					AE		AG	AH	AI						AO	AP
1	Affiliation	ID#				Pref Name w Suffix				Rating	Home City	Home State							5-yr Giving	Jnt LTG					Relation Id	Relation Type					Relation Pref Name w Suffix		Relation Rating	Relation Home City	Relation Home State						Relation 5-yr Giving	Relation Jnt LTG
2	CREW	10030321				Mrs. Lana Shen '99				8	Palo Alto	CA							216400	217585					10057207	Sibling					Ms. Nana Shen '11		N	Palo Alto	CA						190	241
3																									10030933	Parent					Mr. Jim and Mrs. Jennifer Shen P'11 '01 '99		U	Palo Alto	CA						0	50
4																									10084002	Spouse/Partner					Mr. James Updike		8	Palo Alto	CA						0	217585
5																									10035814	Sibling					Ms. Rachel Shen '01		10	Palo Alto	CA						180	13505
6																									10081652	Parent					Mrs. Kathleen McKay P'97 '90		6	Sedona	AZ						0	3442903.56
7																									10046174	Cousin					Ms. Sara Vessi '06		CW								144	288
8																									10026132	Spouse/Partner					Mr. Gavin D. Smith '97 P'24		8								35650.25	129800.49
9																									743	Grandparent					Mr. Peter M. McKay (dec'd) P'71 GP'97 '90		4								0	378100
10																									10046176	Aunt					Ms. Diana McKay P'06		7								25000	83279.95
11
12																									6759409	Parent					Mr. Joseph E. Hatch III '67 P'97 '95		U	Denver	CO						500	10785
13																									10022091	Sibling					Jennifer C. Hatch '95		U	Denver	CO						0	5200
14																									10026319	Parent					Mrs. Ines Stevens P'12 '97		U	Arlington	VA						365.15	3915.45
15																									10079908	Parent					Mr. Guillermo Stevens P'12 '97			Arlington	VA						465.12	3915.45
16																									10059330	Sibling					Mr. Rodrigo Stevens '12		U	Arlington	VA						40	160
Sheet2

testtable2.xlsm
	A	B				F				J	K	L							S	T					Y	Z					AE		AG	AH	AI						AO	AP
1	Affiliation	ID#				Pref Name w Suffix				Rating	Home City	Home State							5-yr Giving	Jnt LTG					Relation Id	Relation Type					Relation Pref Name w Suffix		Relation Rating	Relation Home City	Relation Home State						Relation 5-yr Giving	Relation Jnt LTG
2	CREW	10030321				Mrs. Lana Shen '99				8	Palo Alto	CA							216400	217585					10057207	Sibling					Ms. Nana Shen '11		N	Palo Alto	CA						190	241
3																									10030933	Parent					Mr. Jim and Mrs. Jennifer Shen P'11 '01 '99		U	Palo Alto	CA						0	50
4																									10084002	Spouse/Partner					Mr. James Updike		8	Palo Alto	CA						0	217585
5																									10035814	Sibling					Ms. Rachel Shen '01		10	Palo Alto	CA						180	13505
6	CREW/FYE	10026142				Ms. Jessica Smith '97 P'24													100650.24	129800.49					10081652	Parent					Mrs. Kathleen McKay P'97 '90		6	Sedona	AZ						0	3442903.56
7																									10046174	Cousin					Ms. Sara Vessi '06		CW								144	288
8																									10026132	Spouse/Partner					Mr. Gavin D. Smith '97 P'24		8								35650.25	129800.49
9																									743	Grandparent					Mr. Peter M. McKay (dec'd) P'71 GP'97 '90		4								0	378100
10																									10046176	Aunt					Ms. Diana McKay P'06		7								25000	83279.95
11	AGENTS	10026100				Mr. Jeffrey Kong '97				7	San Francisco	CA							75550	102079.97
12	FYE	10026121				Mr. Jonathan J. Hatch '97				7	Ketchum	ID							57500	96830					6759409	Parent					Mr. Joseph E. Hatch III '67 P'97 '95		U	Denver	CO						500	10785
13																									10022091	Sibling					Jennifer C. Hatch '95		U	Denver	CO						0	5200
14	FYE	10026178				Mr. Guillermo Stevens '97				7									45131.97	80197.79					10026319	Parent					Mrs. Ines Stevens P'12 '97		U	Arlington	VA						365.15	3915.45
15																									10079908	Parent					Mr. Guillermo Stevens P'12 '97			Arlington	VA						465.12	3915.45
16																									10059330	Sibling					Mr. Rodrigo Stevens '12		U	Arlington	VA						40	160
Sheet3

 

[johnnyL]

Ahhhh. You neglected to make one change to the code. Your original post has the names in the C column, #3. Your last post has the names in the F column, #6.

So you should have changed:

        If SavedArray(ArrayRow, 3) = SavedArray(ArrayRow - 1, 3) Then                       '   If This row value = previous row value then ...

to:

        If SavedArray(ArrayRow, 6) = SavedArray(ArrayRow - 1, 6) Then                       '   If This row value = previous row value then ...

Now that you seemingly understand how the code was working, try the following shortened code:

Sub RemoveSomeCellsV2()
'
    Dim ArrayRow            As Long
    Dim DeleteColumnCell    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 ... 5 wide
'
    For ArrayRow = UBound(SavedArray) To 2 Step -1                                          ' Work backwards through rows because we will be deleting values
        If SavedArray(ArrayRow, 6) = SavedArray(ArrayRow - 1, 6) Then                       '   If This row value = previous row value then ...
            For DeleteColumnCell = 1 To 24                                                   '       Loop to delete values from columns A to X
                SavedArray(ArrayRow, DeleteColumnCell) = vbNullString                       '           Delete the value from the array
            Next
        End If
    Next                                                                                    ' Loop back to do next check
'
    Range("A1:X" & LastRowInSheet) = SavedArray                                             ' Print the saved array back to the sheet
End Sub

 

[HeatherF]

Brilliant! Of course, I should have noticed that. I really appreciate your help.

 

[johnnyL]

It is often the little things that FUBAR everything.