Transpose Macro Help...really need help!

[Ian Mac]

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.

[Juan Pablo González]

This macro should help you.

Tip of the day for Sunday, February 10, 2002
http://www.mrexcel.com/weblog/weblog.shtml

It's not THE EXACT format you're asking for, but it should help.

[Ian Mac]

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

[Juan Pablo González]

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.