Results 1 to 10 of 10

Thread: Display 2 rows with multiple columns as 1 row with multiple columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Display 2 rows with multiple columns as 1 row with multiple columns

    Hi.
    I have a spreadsheet that has two rows of data both with multiple columns. One row has 16 columns of data and the one below has 10 columns of data.

    ABCDEFGHIJKLMNOPQRS

    12345678910111213141516
    12345678910

    Does anyone know the formula to display the data as one row adding the data from the second row to the end of the first row in the next available column

    It should look like

    ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB

    123456789101112131415161712345678910

    Note: the number of columns will vary daily. There will always be two rows and the data must be able to update when the calculation runs automatically

    Thanks for your help

  2. #2
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    What do you mean by the "Next available column"? Or another way to ask the question is what do you want the data to look like. Maybe an easy way to answer this question is to actually show what you want by attaching a program or PDF file of what the output should look like.

    Mac

  3. #3
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,898
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    .
    Where did the number 17 come from ?

  4. #4
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    Quote Originally Posted by Mackeral View Post
    What do you mean by the "Next available column"? Or another way to ask the question is what do you want the data to look like. Maybe an easy way to answer this question is to actually show what you want by attaching a program or PDF file of what the output should look like.

    Mac
    Hi
    The data should look like the second example where the two rows are now one.

    The excel uses vlookup into two different spreadsheets A&B and is set to report by using index small and row the results for same search in both spreadsheet in two rows with a differing number of columns dependant on how many instances it finds each day in both of the spreadsheets
    It is set like this because I do not know how to merge the vlookups so I have them on separate rows reporting spreadsheet A on the first row and Spreadsheet B on the second row.
    What I want is for the results to all be on one row. The results from Spreadsheet A first and then Spreadsheet B following on the same row.
    Hope that makes sense?

  5. #5
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    Itís a random number because the first row has 16 columns so I want the second row data to be transposed to column 17 and then 18,19 and so on depending on how many instances of data there is on each row. The number of columns will change daily

  6. #6
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    Quote Originally Posted by Mackeral View Post
    What do you mean by the "Next available column"? Or another way to ask the question is what do you want the data to look like. Maybe an easy way to answer this question is to actually show what you want by attaching a program or PDF file of what the output should look like.

    Mac
    See below a before and after. The first two rows merged in to 1 row with row 3's data following on at the end of row 2's. Both of row 2&3 come from Vlookups from different spreadsheets that vary in quantity each day.
    1 A B C D E F G H I J K L M N O
    2 1278387 2348735 235579 32940598 32857325 329735 2348732 2131234 12423432 2312313
    3 ab78787 ab68575 ab343242 ab234234 ab23456
    4
    5
    6
    7 1278387 2348735 235579 32940598 32857325 329735 2348732 2131234 12423432 2312313 ab78787 ab68575 ab343242 ab234234 ab23456
    8
    9
    10



  7. #7
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    328
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    Hi
    what about
    Code:
    Sub ter()
        Dim lc1, lc2, i, x
        lc1 = Cells(1, Columns.Count).End(xlToLeft).Column
        lc2 = Cells(2, Columns.Count).End(xlToLeft).Column
        ReDim a(1 To lc1)
        ReDim b(1 To lc2)
        For i = 1 To lc1
            a(i) = Cells(1, i)
        Next
        For i = 1 To lc2
            b(i) = Cells(2, i)
        Next
        x = Split(Join(a, Chr(164)) & Chr(164) & Join(b, Chr(164)), Chr(164))
        Cells(8, 1).Resize(, UBound(x) + 1) = x
    End Sub

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    328
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    Hi
    Better Ver.
    Code:
    Sub ter()
        Dim lc1, lc2, x
        lc1 = Cells(2, Columns.Count).End(xlToLeft).Column
        lc2 = Cells(3, Columns.Count).End(xlToLeft).Column
        a = Application.Transpose(WorksheetFunction.Transpose(Cells(2, 1).Resize(, lc1)))
        b = Application.Transpose(WorksheetFunction.Transpose(Cells(3, 1).Resize(, lc2)))
        x = Split(Join(a, Chr(164)) & Chr(164) & Join(b, Chr(164)), Chr(164))
        Cells(8, 1).Resize(, UBound(x) + 1) = x
    End Sub

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    328
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    And this is more compact one
    Code:
    Sub test()
        Dim x As Variant
        x = Split(Join(Application.Transpose(WorksheetFunction.Transpose(Cells(2, 1).Resize(, Cells(2, Columns.Count).End(xlToLeft).Column))), _
                       Chr(164)) & Chr(164) & Join(Application.Transpose(WorksheetFunction.Transpose(Cells(3, 1) _
                       .Resize(, Cells(3, Columns.Count).End(xlToLeft).Column))), Chr(164)), Chr(164))
        Cells(8, 1).Resize(, UBound(x) + 1) = x
    End Sub

  10. #10
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Display 2 rows with multiple columns as 1 row with multiple columns

    Hi
    Thanks very much it worked a treat. However how do i now adapt this for multiple rows in the same spreadsheet? I have multiple instances of two rows requiring transposing in to one row all on the same sheet. I tried adding another copy of the code underneath the first one and changing the relevant references and it worked fine but is there a quicker method? Thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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