List unique numbers within ranges

[motilulla]

Using Excel 2010
Hello,

I need VBA which find the unique numbers from the list of 1 to 35 numbers. Check within the rows 5 in 5.

Here is an example…

1st starting ranges C6:G10, (list Unique of 1 to 35 numbers) in the Cell J10 to right
2nd starting ranges C7:G11, (list Unique of 1 to 35 numbers) in the Cell J11 to right
3rd starting ranges C8:G12, (list Unique of 1 to 35 numbers) in the Cell J12 to right
And so on till end of the data……

Example sheet attached

Find Unique.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1
2
3
4
5			n1	n2	n3	n4	n5			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35
6			2	7	28	22	13
7			24	20	11	23	12
8			11	18	28	1	29
9			9	6	9	17	8
10			22	11	24	7	26	1st Unique From Range C6:G10-->		1	2	6	7	8	9	11	12	13	17	18	20	22	23	24	26	28	29
11			20	21	9	12	20	2nd Unique From Range C7:G11-->		1	6	7	8	9	11	12	17	18	20	21	22	23	24	26	28	29
12			30	16	25	14	4	3rd Unique From Range C8:G12-->		1	4	6	7	8	9	11	12	14	16	17	18	20	21	22	24	25	26	28	29	30
13			4	17	19	30	9			?
14			22	5	16	25	18			?
15			21	23	21	25	35			?
16			2	12	14	12	20			?
17			7	22	23	29	18			?
18			17	13	4	22	21			?
19			4	9	17	22	5			?
20			2	9	13	26	35			?
21			9	17	7	3	6			?
22
23
Find Unique

Regards,
Moti

 

[dermie_72]

This should do the job, please note that your data appears to be incorrect. In the range C6:G10 you were saying that 7, 9, 11 were unique, however there were more than 1 instance of them in that range.

Sub motilulla()
Dim Strtrng As Integer, edrng As Integer, uniq As Integer, i As Integer, col As Integer
Dim lr As Long
lr = Cells(Rows.count, "G").End(xlUp).Row
Strtrng = 6
edrng = 10
col = 10
For A = edrng To lr
    For i = 1 To 35
        uniq = Application.WorksheetFunction.CountIf(Range("C" & Strtrng & ":G" & edrng), i)
        If uniq = 1 Then
            Cells(edrng, col).Value = i
            col = col + 1
            Else
        End If
    Next i
    col = 10
    Strtrng = Strtrng + 1
    edrng = edrng + 1
Next A
End Sub

 

[motilulla]

This should do the job, please note that your data appears to be incorrect. In the range C6:G10 you were saying that 7, 9, 11 were unique, however there were more than 1 instance of them in that range.

dermie_72, yes you are correct it is my fault I would have said I want to have listed of all numbers are in the range (either they are unique or duplicate) please can you modify it. Thank you for your help.

Kind Regards,
Moti

 

[dermie_72]

if you change this piece of code:
If uniq = 1 Then
to
If uniq <> 0 Then

then it will capture every number in the range and populate the field

 

[motilulla]

if you change this piece of code:
If uniq = 1 Then
to
If uniq <> 0 Then

then it will capture every number in the range and populate the field

dermie_72, thank you, yes changing the code as you suggest it list all numbers correctly.

Have an awesome weekend! And Good Luck.

Kind Regards,
Moti