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

micklilley1973

New Member
Joined
Oct 13, 2019
Messages
5
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
















<colgroup><col><col span="15"></colgroup><tbody>
</tbody>


<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-ignore:padding; 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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top