Transpose Macro Help...really need help!

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
All,

I have an ammount of data on a daily basis that is copied from Kea! Connect to an Excel sheet,

when pasted it looks like:

PLC_001A 1000
Call Date 19-Feb 2
Member no 123456-1234554321
CONTACT Mr John Smith
POST CODE XX77 7XX
Reg no X123 ABC
Make MN
Model Model1
Mileage 10000
Age of car 2000

PLC_001A 1002
Call Date 19-Feb 2
Member no 123456-1234554322
CONTACT Mr John Smith
POST CODE XX77 7XX
Reg no X123 ABC
Make MN
Model Model2
Mileage 10000
Age of car 2000

PLC_001A 1003
Call Date 19-Feb 2
Member no 123456-1234554323
CONTACT Mr John Smith
POST CODE XX77 7XX
Reg no X123 ABC
Make MN
Model Model3
Mileage 10000
Age of car 2000

PLC_001A 1004
Call Date 19-Feb 2
Member no 123456-1234554324
CONTACT Mr John Smith
POST CODE XX77 7XX
Reg no X123 ABC
Make MN
Model Model4
Mileage 10000
Age of car 2000

what I'm trying to do is tranpose the data into a table:

PLC_001A Call Date Member no CONTACT POST CODE Reg no Make Model Mileage Age of car
1000 19-Feb 123456-1234554321 Mr John Smith XX77 7XX X123 ABC MN Model1 10000 2000
1002 19-Feb 123456-1234554322 Mr John Smith XX77 7XX X123 ABC MN Model2 10000 2000
1003 19-Feb 123456-1234554323 Mr John Smith XX77 7XX X123 ABC MN Model3 10000 2000
1004 19-Feb 123456-1234554324 Mr John Smith XX77 7XX X123 ABC MN Model4 10000 2000

One of the problems I have is that the Date and Postcode flow over 2 columns, the date is OK as the Feb ## is in date format, but Postcode needs to be in one cell.

Any Ideas?

Here the data I have:

={"PLC_001A",1000,0;"Call Date",37306,2;"Member no","123456-1234554321",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model1",0;"Mileage",10000,0;"Age of car",2000,0;0,0,0;"PLC_001A",1002,0;"Call Date",37306,2;"Member no","123456-1234554322",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model2",0;"Mileage",10000,0;"Age of car",2000,0;0,0,0;"PLC_001A",1003,0;"Call Date",37306,2;"Member no","123456-1234554323",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model3",0;"Mileage",10000,0;"Age of car",2000,0;0,0,0;"PLC_001A",1004,0;"Call Date",37306,2;"Member no","123456-1234554324",0;"CONTACT","Mr John Smith",0;"POST CODE","XX77","7XX";"Reg no","X123 ABC",0;"Make","MN",0;"Model","Model4",0;"Mileage",10000,0;"Age of car",2000,0}

And this is the required result:

{"PLC_001A","Call Date","Member no","CONTACT","POST CODE","Reg no","Make","Model","Mileage","Age of car";1000,37306,"123456-1234554321","Mr John Smith","XX77 7XX","X123 ABC","MN","Model1",10000,2000;1002,37306,"123456-1234554322","Mr John Smith","XX77 7XX","X123 ABC","MN","Model2",10000,2000;1003,37306,"123456-1234554323","Mr John Smith","XX77 7XX","X123 ABC","MN","Model3",10000,2000;1004,37306,"123456-1234554324","Mr John Smith","XX77 7XX","X123 ABC","MN","Model4",10000,2000}

Any help on this would fantastic!

many thanks.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Thanks Juan,

I'm trying to adapt it to say copy 1 row and the next 10 then next line then 1 - 10,

if you could let me know what each line is doing exactly I might be able to work it out.

my code (not much differnce, yet)

Sub CopyAcross()

Dim i As Long
Dim NRow As Long
Dim LastRow As Long

Columns("C:C").Select
Application.CutCopyMode = False
Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

NRow = 1 'First Row TO COPY, change this if you want
i = 1 'First Row where the data appears, I assumed it started in A2


LastRow = Range("A65536").End(xlUp).Row


While i <= LastRow
Range(Cells(i, 2), Cells(i, 2).End(xlDown)).Copy
Cells(NRow, 4).PasteSpecial Transpose:=True 'In here, I'm copying to Column 2, Next availabe Row. You can change the 2 if you want.
NRow = NRow + 1
i = Cells(i, 2).End(xlDown).End(xlDown).Row
Wend
End Sub

thanks
 
Upvote 0
Explanation, step by step:

On 2002-02-21 08:21, Ian Mac wrote:
Thanks Juan,

I'm trying to adapt it to say copy 1 row and the next 10 then next line then 1 - 10,

if you could let me know what each line is doing exactly I might be able to work it out.

my code (not much differnce, yet)

Sub CopyAcross()

Dim i As Long
Dim NRow As Long
Dim LastRow As Long

Columns("C:C").Select
Application.CutCopyMode = False
Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

NRow = 1 'First Row TO COPY, change this if you want
i = 1 'First Row where the data appears, I assumed it started in A2


LastRow = Range("A65536").End(xlUp).Row


While i <= LastRow
Range(Cells(i, 2), Cells(i, 2).End(xlDown)).Copy
Cells(NRow, 4).PasteSpecial Transpose:=True 'In here, I'm copying to Column 2, Next availabe Row. You can change the 2 if you want.
NRow = NRow + 1
i = Cells(i, 2).End(xlDown).End(xlDown).Row
Wend
End Sub

thanks

Range(Cells(i, 2), Cells(i, 2).End(xlDown)).Copy 'This goes to cell Row i Column B, and "presses" Control Shift Down, and then Copu it.

Cells(NRow, 4).PasteSpecial Transpose:=True 'Copy to Cell Row NRow, Column D, and transpose it (So it looks like one data row)

NRow = NRow + 1 'Increment NRow in 1, so it won't delete previous data.

i = Cells(i, 2).End(xlDown).End(xlDown).Row 'From Cell Row i, Column B, press Control Down, and again, Control Down (This should be the next "group" of data")

Wend 'Repeat this process until we're in the last row.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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