Remove duplicate values

P

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi can anyone help?

I have values in cells "E4:K4" sheet1 that change when commandbutton2 is clicked.

Cells "E5:K7" sheet1 also have values that change

If any values in cells "E5:K7" match values in cells "E4:K4" when the command button is clicked then I want to remove these values from the cells "E5:K7" and continue the same process each time the command button is clicked until there are only 7 values left in cells ""E5:K7"

Any help would be appreciated

Regards

pwill
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Do the values in
- E5:E7 need to match E4
- F5:F7 need to match F4
etc??

OR
Does any value found in any cell in E5:K7 match any value found in any cell in E4:K4
 
Last edited:
Upvote 0
Hi Yongle

any cell found in cells E5:K7 that match any value found in any cell in E4:K4. Hope that helps

pwill
 
Upvote 0
test in a copy of your worksheet

Code: 
Private Sub CommandButton1_Click()
    Dim cel As Range
    For Each cel In Range("E5:K7")
        With WorksheetFunction
            If .CountA(Range("E5:K7")) > 7 And .CountIf(Range("E4:K4"), cel) >= 1 Then cel.ClearContents
        End With
    Next cel
End Sub
 
Last edited:
Upvote 0
Thank you Yongle much appreciated,
I can't try it right now as on my way home from work but will give it a try and let you know how I get on as soon as I can.

pwill
 
Upvote 0
Yongle said:
test in a copy of your worksheet

Code: 
Private Sub CommandButton1_Click()
    Dim cel As Range
    For Each cel In Range("E5:K7")
        With WorksheetFunction
            If .CountA(Range("E5:K7")) > 7 And .CountIf(Range("E4:K4"), cel) >= 1 Then cel.ClearContents
        End With
    Next cel
End Sub
Click to expand...

Thanks Yongle, works perfect :)

I am looking to take things a step further with what I need the code for, please could you keep an eye on this thread to see if you can help with further posts.

Regards

pwill
 
Upvote 0
Yongle said:
will do :)
Click to expand...



Hi Yongle, just wondering if you could help with this?

I have changed the range in your code from "E5:K7" to "T5:Z7". The values in "T5:Z7" are now equal to the values in "E5:K7" because the values in these cells "E5:K7" have formulas that update values when the cell values in "E4:K4" change and are needed for the process to work.

After applying your code and have the 7 values left, I highlighted them in red. I now want to continue the process but this time, only remove matching values when the 7 remaining cells that are highlighted in red match any value from "E4:K4" but without removing the 7 values highlighted in red and continue until there are only 2 values remaining with the 7 values highlighted in red.

ie

[TABLE="width: 598"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Finish[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]188[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]360[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]411[/TD]
[TD="align: center"]455[/TD]
[TD="align: center"]488[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]188[/TD]
[TD="align: center"]266[/TD]
[TD="align: center"]299[/TD]
[TD="align: center"]333[/TD]
[TD="align: center"]355[/TD]
[TD="align: center"]399[/TD]
[TD="align: center"]466[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]266[/TD]
[TD="align: center"]299[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]466[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]199[/TD]
[TD="align: center"]277[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"]366[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]477[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]344[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]177[/TD]
[TD="align: center"]255[/TD]
[TD="align: center"]288[/TD]
[TD="align: center"]322[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]455[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]255[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]344[/TD]
[TD="align: center"][/TD]
[TD="align: center"]455[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Continue Process[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]And continue until only 2 values are left with the 7 highlighted in red[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]266[/TD]
[TD="align: center"]366[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"]410[/TD]
[TD="align: center"]455[/TD]
[TD="align: center"]480[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]266[/TD]
[TD="align: center"]299[/TD]
[TD="align: center"]333[/TD]
[TD="align: center"]480[/TD]
[TD="align: center"]399[/TD]
[TD="align: center"]466[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]266[/TD]
[TD="align: center"]299[/TD]
[TD="align: center"]333[/TD]
[TD="align: center"][/TD]
[TD="align: center"]399[/TD]
[TD="align: center"]466[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]199[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"]488[/TD]
[TD="align: center"]455[/TD]
[TD="align: center"]477[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]199[/TD]
[TD="align: center"][/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"]488[/TD]
[TD="align: center"][/TD]
[TD="align: center"]477[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]366[/TD]
[TD="align: center"]255[/TD]
[TD="align: center"]288[/TD]
[TD="align: center"]322[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"]410[/TD]
[TD="align: center"]455[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]255[/TD]
[TD="align: center"]288[/TD]
[TD="align: center"]322[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"][/TD]
[TD="align: center"]455[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Code: 
            For Each cel In Range("T5:Z7")
                With WorksheetFunction
                    If .CountA(Range("T5:Z7")) < 1 Then Range("T5:Z7").Value = Range("E5:K7").Value
                    If .CountA(Range("T5:Z7")) > 7 And .CountIf(Range("E4:K4"), cel) >= 1 Then cel = ""
                End With
            Next cel

Hope that makes sence

pwill
​
 
Last edited:
Upvote 0
I have changed the range in your code from "E5:K7" to "T5:Z7". The values in "T5:Z7" are now equal to the values in "E5:K7" because the values in these cells "E5:K7" have formulas that update values when the cell values in "E4:K4" change and are needed for the process to work.
 
Upvote 0
Had a quick look and cannot grasp what is going on
Perhaps someone else will step in to help you develop this
Good luck :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
575
AndyB86
A
T
  • Question Question
Command Button to change a Cell
Replies
4
Views
350
kevin9999
K
D
  • Solved
Macro that won't run Trying to use DoWhile Loop and Offset to compare cells
Replies
8
Views
435
Micron
M
E
  • Solved
Change format of cell based on value of cell in corresponding row?
2
Replies
12
Views
490
EssKayKay
E
D
  • Solved
VBA change cell value after saving as PDF
Replies
4
Views
236
Joe4
Joe4

Forum statistics

Threads
1,223,969
Messages
6,175,680
Members
452,667
Latest member
vanessavalentino83

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