Trying to consolidate lists

[limpinsomm11]

I basically have a table in the format of

1
2
3




A
B
C
D


11
12
13

I want it to read in another column with no spaces:
1
2
3
A
B
C
D
11
12
13

Basically, I would like it to skip to the next list and read everytime there is a space. I can't simply count the spaces as this is a template and there will be different number of items in each list each time. Thanks is advance for your excel skills.

[Trebor76]

Hi limpinsomm11,

Welcome to the forum.

Not sure of a formula solution, but if you run the following macro while on the starting cell for your summarised output (cell D4 in your attached example) you'll get the desired result:

Sub Macro2()

Dim rngSelection, rngCell As Range

    On Error Resume Next
        Application.DisplayAlerts = False
            Set rngSelection = Application.InputBox(Prompt:= _
                "Use your mouse to select the reference range:", _
                    Title:="SPECIFY RANGE", Type:=8)
    On Error GoTo 0
        Application.DisplayAlerts = True

        If IsEmpty(rngSelection) = True Then
            Exit Sub
        Else
            For Each rngCell In rngSelection
                If IsEmpty(rngCell) = False Then
                    ActiveCell.Value = rngCell.Value
                    ActiveCell.Offset(1, 0).Select
                End If
            Next rngCell
        End If
   
End Sub

HTH

Robert