+ Reply to Thread
Results 1 to 7 of 7

create multi-column list from multiple sheets

Hybrid View

Petros Georgilas create multi-column list from... 02-18-2015, 10:45 AM
stnkynts Re: create multi-column list... 02-18-2015, 11:24 AM
Petros Georgilas Re: create multi-column list... 02-18-2015, 12:43 PM
JOHN H. DAVIS Re: create multi-column list... 02-18-2015, 12:05 PM
Petros Georgilas Re: create multi-column list... 02-18-2015, 12:44 PM
JOHN H. DAVIS Re: create multi-column list... 02-18-2015, 02:42 PM
Petros Georgilas Re: create multi-column list... 02-18-2015, 04:51 PM
  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    30

    create multi-column list from multiple sheets

    create and combine lists.xlsx
    Hello,
    pls see attached file as an example. In sheet "input1" I have a list of country / city pairs (1 column for each). The same for sheet "input2" but this time the columns are separated and not in the same position. I am looking for a way to create a list of the UNIQUE pairs of country / city, i.e. eliminate duplicates.
    Please note the following:
    From time to time the length of each input list may not be the same
    The actual file may be more complicated, i.e. more than 2 columns
    The colors are just to help me explain.
    Thank you

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: create multi-column list from multiple sheets

    Sub RunMe()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("input1")
    Dim ws2 As Worksheet:   Set ws2 = Sheets("input2")
    Dim ws3 As Worksheet:   Set ws3 = Sheets("output")
    Dim arr As Variant, vSplit As Variant
    Dim bDim As Boolean:    bDim = False
    Dim c As Range
    Dim str As String
    Dim i As Integer
    
    For Each c In ws1.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)
        str = ""
        If Not Len(c) = 0 Then
            str = c & "|" & c.Offset(, 1)
            If bDim = False Then
                ReDim arr(0 To 0) As String
                arr(0) = str
                bDim = True
            Else
                If IsError(Application.Match(str, arr, 0)) Then
                    ReDim Preserve arr(0 To UBound(arr) + 1) As String
                    arr(UBound(arr)) = str
                End If
            End If
        End If
    Next c
    
    For Each c In ws2.Range("B2:B" & ws1.Range("B" & Rows.Count).End(xlUp).Row)
        str = ""
        If Not Len(c) = 0 Then
            str = c & "|" & c.Offset(, 2)
            If bDim = False Then
                ReDim arr(0 To 0) As String
                arr(0) = str
                bDim = True
            Else
                If IsError(Application.Match(str, arr, 0)) Then
                    ReDim Preserve arr(0 To UBound(arr) + 1) As String
                    arr(UBound(arr)) = str
                End If
            End If
        End If
    Next c
    
    For i = LBound(arr) To UBound(arr)
        vSplit = Split(arr(i), "|")
        ws3.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = vSplit(0)
        ws3.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = vSplit(1)
    Next i
    
    End Sub

  3. #3
    Registered User
    Join Date
    03-13-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    30

    Re: create multi-column list from multiple sheets

    Thank you stnkynts.
    It took me much more to understand how some of the things work than it took you to write it!
    In addition, I learned some things that I did not know. Some I could not understand but time is on my side.
    What's more important I was able to modify it and deal with more columns.
    A small disadvantage I think is that when a cell in the first column is blank then I will miss the pair...but that's not important in my case (at least for now)
    thanks again!

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: create multi-column list from multiple sheets

    FWIW:

    Sub PetrosGeorgilas()
    Dim ws As Worksheet, ws2 As Worksheet
    Dim w As Long, x As Long, y As Long, z As Long
    Dim rcell As Range, t As Range
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    Set ws = Sheets("input1")
    Set ws2 = Sheets("input2")
    w = ws.Range("A" & Rows.Count).End(3).Row
    x = ws2.Range("A" & Rows.Count).End(3).Row
    ws.Columns(1).Insert
    ws2.Columns(1).Insert
    ws.Activate
    With ws.Range(Cells(2, "A"), Cells(w, "A"))
        .Formula = "=CONCATENATE(B2,C2)"
        .Value = .Value
    End With
    ws2.Activate
    With ws2.Range(Cells(2, "A"), Cells(x, "A"))
        .Formula = "=CONCATENATE(C2,E2)"
        .Value = .Value
    End With
    ws.Activate
    For y = 2 To w
        For z = 2 To x
            If ws.Cells(y, 1) <> ws2.Cells(z, 1) Then
                ws.Cells(y, 2).Copy Sheets("Output").Range("C" & Rows.Count).End(3)(2)
                ws.Cells(y, 3).Copy Sheets("Output").Range("D" & Rows.Count).End(3)(2)
            End If
        Next z
    Next y
    ws2.Activate
    For y = 2 To x
        For z = 2 To w
            If ws2.Cells(y, 1) <> ws.Cells(z, 1) Then
                ws2.Cells(y, 3).Copy Sheets("Output").Range("C" & Rows.Count).End(3)(2)
                ws2.Cells(y, 5).Copy Sheets("Output").Range("D" & Rows.Count).End(3)(2)
            End If
        Next z
    Next y
    ws.Columns(1).Delete
    ws2.Columns(1).Delete
    With Sheets("Output")
    w = .Range("C" & Rows.Count).End(3).Row
        For y = 2 To .Range("C" & Rows.Count).End(3).Row
            For z = y + 1 To .Range("C" & Rows.Count).End(3).Row
                If .Cells(z, 3) = .Cells(y, 3) And .Cells(z, 4) = .Cells(y, 4) Then
                    .Range(.Cells(z, 3), .Cells(w, 3)).Replace .Cells(y, 3).Value, "", xlWhole
                    .Range(.Cells(z, 4), .Cells(w, 4)).Replace .Cells(y, 4).Value, "", xlWhole
                End If
            Next z
        Next y
        For Each rcell In .Range(.Cells(2, "C"), .Cells(w, "C"))
            If rcell.Value <> "" And rcell.Offset(, 1).Value = "" Then
                Set t = ws.Columns(1).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not t Is Nothing Then
                        rcell.Offset(, 1).Value = t.Offset(, 1).Value
                    End If
                    Set t = Nothing
                Set t = ws2.Columns(3).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not t Is Nothing Then
                        rcell.Offset(, 1).Value = t.Offset(, 2).Value
                    End If
                    Set t = Nothing
            End If
        Next rcell
        For Each rcell In .Range(.Cells(2, "D"), .Cells(w, "D"))
            If rcell.Value <> "" And rcell.Offset(, -1).Value = "" Then
                Set t = ws.Columns(2).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not t Is Nothing Then
                        rcell.Offset(, -1).Value = t.Offset(, -1).Value
                    End If
                    Set t = Nothing
                Set t = ws2.Columns(5).Find(rcell.Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not t Is Nothing Then
                        rcell.Offset(, -1).Value = t.Offset(, -2).Value
                    End If
                    Set t = Nothing
            End If
        Next rcell
            
    w = .Range("C" & Rows.Count).End(3).Row
    .Range(.Cells(2, "C"), .Cells(w, "D")).SpecialCells(4).Delete xlUp
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    End Sub

  5. #5
    Registered User
    Join Date
    03-13-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    30

    Re: create multi-column list from multiple sheets

    Thank you JOHN H. DAVIS!
    Unfortunately I get an error towards the end with
    w = .Range("C" & Rows.Count).End(3).Row
    .Range(.Cells(2, "C"), .Cells(w, "D")).SpecialCells(4).Delete xlUp
    End With

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: create multi-column list from multiple sheets

    What's the error?

  7. #7
    Registered User
    Join Date
    03-13-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    30

    Re: create multi-column list from multiple sheets

    It's a bug...it does not work...and when i check...the part I copied is highlited

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. create one list from multiple sheets
    By ishi2344 in forum Excel General
    Replies: 4
    Last Post: 03-10-2014, 10:51 AM
  2. Create multiple workbooks with multiple sheets from a list
    By Angrypirate in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-20-2014, 01:54 PM
  3. Replies: 19
    Last Post: 10-05-2012, 01:03 PM
  4. [SOLVED] VBA to create a list from multiple sheets
    By Jerums in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2012, 03:23 PM
  5. Create a user form with a multi-column list box
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2009, 11:33 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1