List all data from 4 columns into one column without repitition?

[bakarken]

Hi all

I want column F to summarise all text data detailed in columns A, B, C, and D, but only showing one result no matter how many times the same entry occurs in A-D.

EXAMPLE:

A B C D E F
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Mr A[/TD]
[TD="width: 64"]Mr R[/TD]
[TD="width: 64"]Mr Z[/TD]
[TD="width: 64"]Mr L[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Mr A[/TD]
[/TR]
[TR]
[TD]Mr B[/TD]
[TD]Mr Z[/TD]
[TD][/TD]
[TD]Mr Q[/TD]
[TD][/TD]
[TD]Mr B[/TD]
[/TR]
[TR]
[TD]Mr R[/TD]
[TD]Mr A[/TD]
[TD][/TD]
[TD]Mr A[/TD]
[TD][/TD]
[TD]Mr R[/TD]
[/TR]
[TR]
[TD]Mr Z[/TD]
[TD]Mr Q[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr Z[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mr L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr Q[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr L

[/TD]
[/TR]
</tbody>[/TABLE]

Column F shows data from all 4 columns without repetition, even though they are not in the same order.

Is this possible?

 

[Joe4]

I would copy all the data to column F, and then use the Remove Duplicates functionality under the Data menu.
If you do not want to do that manually, we can create a macro to do that, like this:

Sub SummarizeData()

    Dim myCol As Long
    Dim lastRow As Long
    Dim pasteRow As Long
    Dim myRange As Range

    Application.ScreenUpdating = False
    
'   Loop through columns 1-4
    For myCol = 1 To 4
'       Find last row with data in column
        lastRow = Cells(Rows.Count, myCol).End(xlUp).Row
'       Select all data in column
        Set myRange = Range(Cells(1, myCol), Cells(lastRow, myCol))
'       Find row to paste to (in column F/6)
        If Cells(1, 6) = "" Then
            pasteRow = 1
        Else
            pasteRow = Cells(Rows.Count, 6).End(xlUp).Offset(0, 1).Row
        End If
'       Copy data
        myRange.Copy Cells(pasteRow, 6)
    Next myCol
    
'   Remove duplicates from column F
    Range("F:F").RemoveDuplicates Columns:=1, Header:=xlNo
    
    Application.ScreenUpdating = True
    
End Sub

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
    
    For i = 1 To 4
        Lastrow = Cells(Rows.Count, i).End(xlUp).Row
        Lastrowa = Cells(Rows.Count, "F").End(xlUp).Row + 1
        Range(Cells(1, i), Cells(Lastrow, i)).Copy Cells(Lastrowa, "F")
    Next
Lastrowa = Cells(Rows.Count, "F").End(xlUp).Row
Range("F1:F" & Lastrowa).RemoveDuplicates 1, xlNo
Application.ScreenUpdating = True
End Sub

 

[bakarken]

Hi both so sorry not to mention but I was hoping for a formula for this task, anyone know of one? Maybe using the IFERROR in some way .. thank you though

 

[Joe4]

I think that any formula solution would be quite complex, since it involves multiple columns.
Might be a labor intensive array formula.
Anyway, it is beyond my level of knowledge.

Why the resistance to a VBA solution?

 

[Mike Szczesny]

Hi bakarken,

I have a crazy complicated formula. Perhaps it is not perfect for your purposes. But it works. This formula uses the =int and =mod formulas. Inside the formula, mod, then int will list items column by column, etc. Inside the formula, int, then mod will list items row by row, etc. Assume my data range is A1:D4. Here is the formula: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$1:$D$4,MOD(SMALL(IF($A$1:$D$4<>"",(COLUMN($A$1:$D$4)-COLUMN($A$1)+1)*10^9+ROW($A$1:$D$4)-ROW($A$1)+1),ROWS($A$6:D6)),10^9),INT(SMALL(IF($A$1:$D$4<>"",(COLUMN($A$1:$D$4)-COLUMN($A$1)+1)*10^9+ROW($A$1:$D$4)-ROW($A$1)+1),ROWS($A$6:D6))/10^9))," ")

Then highlight range. Then go to Data, remove duplicates. I haven't figured out how to incorporate the frequency function in this formula. This is above my pay grade.
Hope this helps.

Joe4: Can you help me with this?[/TD]
[/TR]
</tbody>[/TABLE]