Changing a code

[bakeraj256]

Hi,

Awhile ago a forum member provided me with this code for extracting unique combinations of data entries across three columns.

I was wondering if someone could help me augment it to perform the same task for four columns of data? Thanks in advance.

Here is the code from the program, and I have also attached the original file used to provide me with the code when I just needed the problem solved for three columns.

Sub test()

Dim sh As Worksheet, target_sh As Worksheet
Dim lrow As Long, data, result, i As Long, n As Long, j As Long, mystr As String

Set sh = Sheets("Compiler")

lrow = sh.Cells(Rows.Count, 2).End(xlUp).Row
If lrow = 1 Then Exit Sub

Set target_sh = Sheets("Annual Summary")

data = sh.Range("b1:d" & lrow)

ReDim result(1 To lrow, 1 To 3)

For i = 2 To lrow
    If InStr(mystr, "|" & data(i, 1) & data(i, 2) & data(i, 3) & "|") = 0 Then
        mystr = mystr & "|" & data(i, 1) & data(i, 2) & data(i, 3) & "|"
        j = j + 1
        For n = 1 To 3
            result(j, n) = data(i, n)
        Next
    End If
Next

Application.ScreenUpdating = 0

target_sh.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(j, 3) = result
target_sh.UsedRange.EntireColumn.AutoFit

Application.ScreenUpdating = 1

End Sub

Kind regards,

[nathansav]

data = sh.Range("b1:e" & lrow)

ReDim result(1 To lrow, 1 To 4)

If InStr(mystr, "|" & data(i, 1) & data(i, 2) & data(i, 3) & data(i,4) & "|") = 0 Then


basically amend the arrays to use 4 and the n count to be =1 to 4.

[bakeraj256]

Thank you for the reply.

If you have a moment, why is it that I do not have to add a fourth variable?

In the first case with 3 I had i, n & j. I figured a fourth would have to be added as well. I don't think I am quite as clear on how the internal mechanics of this program work as I thought.

[nathansav]

Sorry you will, basically everything to do with the loops/arrays and the input range being 4 columns