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

[micklilley1973]

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

 

[Mackeral]

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

 

[Logit]

.
Where did the number 17 come from ?

 

[micklilley1973]

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?

 

[micklilley1973]

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

 

[micklilley1973]

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.
[TABLE="width: 972"]
<colgroup><col><col span="15"></colgroup><tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1278387
[/TD]
[TD]2348735
[/TD]
[TD]235579
[/TD]
[TD]32940598
[/TD]
[TD]32857325
[/TD]
[TD]329735
[/TD]
[TD]2348732
[/TD]
[TD]2131234
[/TD]
[TD]12423432
[/TD]
[TD]2312313
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]ab78787
[/TD]
[TD]ab68575
[/TD]
[TD]ab343242
[/TD]
[TD]ab234234
[/TD]
[TD]ab23456
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1278387
[/TD]
[TD]2348735
[/TD]
[TD]235579
[/TD]
[TD]32940598
[/TD]
[TD]32857325
[/TD]
[TD]329735
[/TD]
[TD]2348732
[/TD]
[TD]2131234
[/TD]
[TD]12423432
[/TD]
[TD]2312313
[/TD]
[TD]ab78787
[/TD]
[TD]ab68575
[/TD]
[TD]ab343242
[/TD]
[TD]ab234234
[/TD]
[TD]ab23456
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>

 

[mohadin]

Hi
what about

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

 

[mohadin]

Hi
Better Ver.

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

 

[mohadin]

And this is more compact one

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

 

[micklilley1973]

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