Remove duplicate values

[pwill]

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

 

[Yongle]

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

 

[pwill]

Hi Yongle

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

pwill

 

[Yongle]

test in a copy of your worksheet

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

 

[pwill]

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

 

[pwill]

test in a copy of your worksheet

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

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

 

[Yongle]

will do

 

[pwill]

will do

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

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

<tbody>
</tbody>

            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
​

 

[pwill]

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.

 

[Yongle]

Had a quick look and cannot grasp what is going on
Perhaps someone else will step in to help you develop this
Good luck