Remove Duplicates From List of Lines.

[serge]

Hello everyone,

I have a list of numbers starting in row CG7:C23, I need a formula starting in cell CG27 that will return the numbers between 1 to 70 with no duplicate ! like it show in the green row 27.

Thank you.

 

[serge]

Hi, I really need this formula to ease my table, can someone help me with it Please.
Thank you.

 

[rlv01]

Have you considered a VBA solution?

Book3
	A	B	C	D	E	F	G	H
1	Col 1	Col 2	Col 3	Col 4
2	6	73	24	77
3	6	72	60	93
4	73	87	95	84
5	90	42	75	95
6	96	15	88	72
7	87	76	6	51
8	98	77	73	88
9	89	69	73	86
10	77	78	81	33
11
12	6	15	24	33	42	51	60	69
13
Sheet1

Sub MakeList()
    Dim WS As Worksheet
    Dim I As Long
    Dim S As String
    Dim SLO As Object
    Dim rng As Range, R As Range

    Set WS = ActiveSheet
    Set rng = WS.Range("A2:D10")

    Set SLO = CreateObject("System.Collections.SortedList")
    S = ","
    For Each R In rng
        If R.Value >= 1 And R.Value <= 70 And InStr(S, "," & CStr(R.Value) & ",") = 0 Then
            I = I + 1
            S = S & R.Value & ","
            SLO.Add R.Value, R.Value
        End If
    Next R

    If I > 0 Then
        With WS.Range("A2").Offset(rng.Rows.Count + 1)
            For I = 0 To SLO.Count - 1
                .Cells(1, I + 1).Value = SLO.GetByIndex(I)
            Next I
        End With
    End If
End Sub

 

[Domenic]

Here's a formula solution...

serge.xlsm
	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX
6	36	27	60
7
8	83	47	27
9
10	50	68	32
11
12	77	48	54
13
14	33	55	34
15
16	31	80	59
17
18	91	35	55
19
20	67	68	63
21
22	72	42	82
23
24
25	18
26	27	31	32	33	34	35	36	42	47	48	50	54	55	59	60	63	67	68
Sheet1

Cell Formulas
Range	Formula
CG25	CG25	=SUM(IF(FREQUENCY(IF(CG6:CI22>=1,IF(CG6:CI22<=70,CG6:CI22)),IF(CG6:CI22>=1,IF(CG6:CI22<=70,CG6:CI22)))>0,1))
CG26:CX26	CG26	=IF(COLUMNS($CG26:CG26)<=$CG$25,SMALL(IF($CG$6:$CI$22>=1,IF($CG$6:$CI$22<=70,IF(ISNA(MATCH($CG$6:$CI$22,$CF26:CF26,0)),$CG$6:$CI$22))),1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope this helps!

 

[serge]

Thank you so much to both of you, I prefer to use the formula because I don't really know how to use code but thank you for proposing it, I really appreciate your help, I really needed it.

 

[rlv01]