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.
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.