Remove values from some cells based on duplicates in one column

H

HeatherF

New Member
Joined
Mar 3, 2020
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJ
1AffiliationID#Pref Name w SuffixRating5-yr GivingRelation IdRelation TypeRelation Pref Name w SuffixRelation RatingRelation 5-yr Giving
2PHYSICS10023868Mr. Doug M. Worman '96CW$ 170.007009006ParentMr. Matthias B. Worman '70 P'05 '966$ 28,000.00
3PHYSICS10023868Mr. Doug M. Worman '96CW$ 170.0010043556SiblingMrs. Suzy Lantsman '05CW$ 450.00
4PHYSICS10023868Mr. Doug M. Worman '96CW$ 170.0010022938ParentMrs. Matthias B. Worman P'05 '966$ -
5CREW10024167Ms. Mickey J. Nesmith '966$ 12,500.0010024647ParentMr. & Mrs. John F. Nesmith P'968$ -
6AGENTS10025692Mr. John Forge '979$ 2,750.0010015288SiblingMs. Kristin J. Forge '928$ 400,000.00
7AGENTS10025692Mr. John Forge '979$ 2,750.0010073058InlawMr. Steve Casey 8$ -
8AGENTS10025692Mr. John Forge '979$ 2,750.006529599ParentDr. H. James Forge (dec'd) '65 P'97 '929$ 100,215.00
9AGENTS10025692Mr. John Forge '979$ 2,750.0010029576ParentMrs. Alison Forge H'65 P'97 '929$ -
10FYE10025906Mr. Mike D. Ransick '97CW$ -10008283InlawMr. Michael J. Swiss '89 P'206$ 280,620.00
11FYE10025906Mr. Mike D. Ransick '97CW$ -10076742InlawMs. Kristen E. Swiss P'206$ 7,500.00
12FYE10025906Mr. Mike D. Ransick '97CW$ -10076736NieceMs. Cecilia A. Swiss '20CW$ -
13CREW10028268Mrs. Laura Douglas '97CW$ 850.0010084011StepparentMs. Laura Baker 6$ -
14CREW10028268Mrs. Laura Douglas '97CW$ 850.006221340ParentMr. Carl B. Douglas '62 P'98 '926$ 4,366.41
15CREW10028268Mrs. Laura Douglas '97CW$ 850.0010015662SiblingMr. Blake T. Douglas '92CW$ 100.00
16CREW10028268Mrs. Laura Douglas '97CW$ 850.003421331GrandparentDr. Carl Douglas, Jr. (dec'd) '34 P'62 GP'98 '92U$ -
17PHYSICS10028413Mrs. Jennifer Maitland '98$ 1,286.90
18CREW10030463Ms. Winter Morgan '99CW$ 45,000.0010030843ParentMr. & Mrs. Joseph Wallis P'997$ -
TABLEA


Book1
ABCDEFGHIJ
1AffiliationID#Pref Name w SuffixRating5-yr GivingRelation IdRelation TypeRelation Pref Name w SuffixRelation RatingRelation 5-yr Giving
2PHYSICS10023868Mr. Doug M. Worman '96CW$ 170.007009006ParentMr. Matthias B. Worman '70 P'05 '966$ 28,000.00
310043556SiblingMrs. Suzy Lantsman '05CW$ 450.00
410022938ParentMrs. Matthias B. Worman P'05 '966$ -
5CREW10024167Ms. Mickey J. Nesmith '966$ 12,500.0010024647ParentMr. & Mrs. John F. Nesmith P'968$ -
6AGENTS10025692Mr. John Forge '979$ 2,750.0010015288SiblingMs. Kristin J. Forge '928$ 400,000.00
710073058InlawMr. Steve Casey 8$ -
86529599ParentDr. H. James Forge (dec'd) '65 P'97 '929$ 100,215.00
910029576ParentMrs. Alison Forge H'65 P'97 '929$ -
10FYE10025906Mr. Mike D. Ransick '97CW$ -10008283InlawMr. Michael J. Swiss '89 P'206$ 280,620.00
1110076742InlawMs. Kristen E. Swiss P'206$ 7,500.00
1210076736NieceMs. Cecilia A. Swiss '20CW$ -
13CREW10028268Mrs. Laura Douglas '97CW$ 850.0010084011StepparentMs. Laura Baker 6$ -
146221340ParentMr. Carl B. Douglas '62 P'98 '926$ 4,366.41
1510015662SiblingMr. Blake T. Douglas '92CW$ 100.00
163421331GrandparentDr. Carl Douglas, Jr. (dec'd) '34 P'62 GP'98 '92U$ -
17PHYSICS10028413Mrs. Jennifer Maitland '98$ 1,286.90
18CREW10030463Ms. Winter Morgan '99CW$ 45,000.0010030843ParentMr. & Mrs. Joseph Wallis P'997$ -
TABLEB


Book1
ABCDEFGHIJ
1AffiliationID#Pref Name w SuffixRating5-yr GivingRelation IdRelation TypeRelation Pref Name w SuffixRelation RatingRelation 5-yr Giving
2 000$ -7009006ParentMr. Matthias B. Worman '70 P'05 '966$ 28,000.00
3 000$ -10043556SiblingMrs. Suzy Lantsman '05CW$ 450.00
4 000$ -10022938ParentMrs. Matthias B. Worman P'05 '966$ -
5 000$ -10024647ParentMr. & Mrs. John F. Nesmith P'968$ -
6 000$ -10015288SiblingMs. Kristin J. Forge '928$ 400,000.00
7 000$ -10073058InlawMr. Steve Casey 8$ -
8 000$ -6529599ParentDr. H. James Forge (dec'd) '65 P'97 '929$ 100,215.00
9 000$ -10029576ParentMrs. Alison Forge H'65 P'97 '929$ -
10 000$ -10008283InlawMr. Michael J. Swiss '89 P'206$ 280,620.00
11 000$ -10076742InlawMs. Kristen E. Swiss P'206$ 7,500.00
12 000$ -10076736NieceMs. Cecilia A. Swiss '20CW$ -
13 000$ -10084011StepparentMs. Laura Baker 6$ -
14 000$ -6221340ParentMr. Carl B. Douglas '62 P'98 '926$ 4,366.41
15 000$ -10015662SiblingMr. Blake T. Douglas '92CW$ 100.00
16 000$ -3421331GrandparentDr. Carl Douglas, Jr. (dec'd) '34 P'62 GP'98 '92U$ -
17 000$ -
18 000$ -10030843ParentMr. & Mrs. Joseph Wallis P'997$ -
TABLEC
Cell Formulas
RangeFormula
A2:A18A2=IF(C2=C3,"",A2)
B2:B18B2=IF(C2=C3,"",B2)
C2:C18C2=IF(C2=C3,"",C2)
D2:D18D2=IF(C2=C3,"",D2)
E2:E18E2=IF(C2=C3,"",E2)
 
Sort by date Sort by votes
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
ABFJKLSYZAEAGAHAIAOAP
1AffiliationID#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
2CREW10030321Mrs. Lana Shen '998Palo AltoCA21640010057207SiblingMs. Nana Shen '11NPalo AltoCA190241
3CREW10030321Mrs. Lana Shen '998Palo AltoCA21640010030933ParentMr. Jim and Mrs. Jennifer Shen P'11 '01 '99UPalo AltoCA050
4CREW10030321Mrs. Lana Shen '998Palo AltoCA21640010084002Spouse/PartnerMr. James Updike 8Palo AltoCA0217585
5CREW10030321Mrs. Lana Shen '998Palo AltoCA21640010035814SiblingMs. Rachel Shen '0110Palo AltoCA18013505
6CREW/FYE10026142Ms. Jessica Smith '97 P'24 100650.2410081652ParentMrs. Kathleen McKay P'97 '906SedonaAZ03442903.56
7CREW/FYE10026142Ms. Jessica Smith '97 P'24 100650.2410046174CousinMs. Sara Vessi '06CW 144288
8CREW/FYE10026142Ms. Jessica Smith '97 P'24 100650.2410026132Spouse/PartnerMr. Gavin D. Smith '97 P'248 35650.25129800.49
9CREW/FYE10026142Ms. Jessica Smith '97 P'24 100650.24743GrandparentMr. Peter M. McKay (dec'd) P'71 GP'97 '904 0378100
10CREW/FYE10026142Ms. Jessica Smith '97 P'24 100650.2410046176AuntMs. Diana McKay P'067 2500083279.95
11AGENTS10026100Mr. Jeffrey Kong '977San FranciscoCA75550
12FYE10026121Mr. Jonathan J. Hatch '977KetchumID575006759409ParentMr. Joseph E. Hatch III '67 P'97 '95UDenverCO50010785
13FYE10026121Mr. Jonathan J. Hatch '977KetchumID5750010022091SiblingJennifer C. Hatch '95UDenverCO05200
14FYE10026178Mr. Guillermo Stevens '977 45131.9710026319ParentMrs. Ines Stevens P'12 '97UArlingtonVA365.153915.45
15FYE10026178Mr. Guillermo Stevens '977 45131.9710079908ParentMr. Guillermo Stevens P'12 '97ArlingtonVA465.123915.45
16FYE10026178Mr. Guillermo Stevens '977 45131.9710059330SiblingMr. Rodrigo Stevens '12UArlingtonVA40160
Sheet1



testtable2.xlsm
ABFJKLSTYZAEAGAHAIAOAP
1AffiliationID#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
2CREW10030321Mrs. Lana Shen '998Palo AltoCA21640021758510057207SiblingMs. Nana Shen '11NPalo AltoCA190241
310030933ParentMr. Jim and Mrs. Jennifer Shen P'11 '01 '99UPalo AltoCA050
410084002Spouse/PartnerMr. James Updike 8Palo AltoCA0217585
510035814SiblingMs. Rachel Shen '0110Palo AltoCA18013505
610081652ParentMrs. Kathleen McKay P'97 '906SedonaAZ03442903.56
710046174CousinMs. Sara Vessi '06CW 144288
810026132Spouse/PartnerMr. Gavin D. Smith '97 P'248 35650.25129800.49
9743GrandparentMr. Peter M. McKay (dec'd) P'71 GP'97 '904 0378100
1010046176AuntMs. Diana McKay P'067 2500083279.95
11
126759409ParentMr. Joseph E. Hatch III '67 P'97 '95UDenverCO50010785
1310022091SiblingJennifer C. Hatch '95UDenverCO05200
1410026319ParentMrs. Ines Stevens P'12 '97UArlingtonVA365.153915.45
1510079908ParentMr. Guillermo Stevens P'12 '97ArlingtonVA465.123915.45
1610059330SiblingMr. Rodrigo Stevens '12UArlingtonVA40160
Sheet2


testtable2.xlsm
ABFJKLSTYZAEAGAHAIAOAP
1AffiliationID#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
2CREW10030321Mrs. Lana Shen '998Palo AltoCA21640021758510057207SiblingMs. Nana Shen '11NPalo AltoCA190241
310030933ParentMr. Jim and Mrs. Jennifer Shen P'11 '01 '99UPalo AltoCA050
410084002Spouse/PartnerMr. James Updike 8Palo AltoCA0217585
510035814SiblingMs. Rachel Shen '0110Palo AltoCA18013505
6CREW/FYE10026142Ms. Jessica Smith '97 P'24 100650.24129800.4910081652ParentMrs. Kathleen McKay P'97 '906SedonaAZ03442903.56
710046174CousinMs. Sara Vessi '06CW 144288
810026132Spouse/PartnerMr. Gavin D. Smith '97 P'248 35650.25129800.49
9743GrandparentMr. Peter M. McKay (dec'd) P'71 GP'97 '904 0378100
1010046176AuntMs. Diana McKay P'067 2500083279.95
11AGENTS10026100Mr. Jeffrey Kong '977San FranciscoCA75550102079.97
12FYE10026121Mr. Jonathan J. Hatch '977KetchumID57500968306759409ParentMr. Joseph E. Hatch III '67 P'97 '95UDenverCO50010785
1310022091SiblingJennifer C. Hatch '95UDenverCO05200
14FYE10026178Mr. Guillermo Stevens '977 45131.9780197.7910026319ParentMrs. Ines Stevens P'12 '97UArlingtonVA365.153915.45
1510079908ParentMr. Guillermo Stevens P'12 '97ArlingtonVA465.123915.45
1610059330SiblingMr. Rodrigo Stevens '12UArlingtonVA40160
Sheet3
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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:

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

to:

VBA Code: 
        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:

VBA 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
 
Upvote 0
Brilliant! Of course, I should have noticed that. I really appreciate your help.
 
Upvote 0
It is often the little things that FUBAR everything. :eek:
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
299
Peter_SSs
Peter_SSs
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
437
johnnyL
J
G
  • Solved
Remove Duplicates leaves some duplicates
Replies
2
Views
497
Sufiyan97
Sufiyan97
M
  • Question Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
1K
JamesCanale
JamesCanale
S
  • Question Question
Help with VB code to copy some values from one sheet to another
Replies
0
Views
971
skeeeter56
S

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top