{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["transpose","answer"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"transpose","index":0,"n_rows":87,"n_cols":8,"rows_kept":87,"cut":false,"hidden":false,"merged":[[6,2,12,6]]}
["ZYZ LTD",null,"ZYZ LTD","14-15 Bertam Ward Way","Dereham","Norfolk","NR19 1TD"]
["14-15 Bertam Ward Way",null,"10th Way Ltd","36 Stone Way","St Neots","PE19 2RY",""]
["Dereham",null,"3 Countries Self Drive","Burners Lane North","MK11 4RT","",""]
["Norfolk",null,"3663 XXY Group","Westgate Ind Est","Smith Road","Northampton","Buckinghamshire"," "]
["NR19 1TD"]
[null,null,"Your task:"]
["10th Way Ltd",null,"Write a VBA macro / Power Query script / Excel formulas to transpose data in column A in to the format indicated in the range C:G. Transpose the data in to answer sheet, starting from cell A1. Once done, please post your answers at Chandoo.org"]
["36 Stone Way"]
["St Neots"]
["PE19 2RY"]
[]
["3 Countries Self Drive"]
["Burners Lane North"]
["MK11 4RT"]
[]
["3663 XXY Group"]
["Westgate Ind Est"]
["Smith Road"]
["Northampton"]
["Buckinghamshire"]
["NN5 5NT"]
[]
["ZYZ LTD"]
["14-15 Bertam Ward Way"]
["Dereham"]
["Norfolk"]
["NR19 1TD"]
[]
["10th Way Ltd"]
["36 Stone Way"]
["St Neots"]
["PE19 2RY"]
[]
["3 Countries Self Drive"]
["Burners Lane North"]
["MK11 4RT"]
[]
["3663 XXY Group"]
["Westgate Ind Est"]
["Smith Road"]
["Northampton"]
["Buckinghamshire"]
["NN5 5NT"]
[]
["ZYZ LTD"]
["14-15 Bertam Ward Way"]
["Dereham"]
["Norfolk"]
["NR19 1TD"]
[]
["10th Way Ltd"]
["36 Stone Way"]
["St Neots"]
["PE19 2RY"]
[]
["3 Countries Self Drive"]
["Burners Lane North"]
["MK11 4RT"]
[]
["3663 XXY Group"]
["Westgate Ind Est"]
["Smith Road"]
["Northampton"]
["Buckinghamshire"]
["NN5 5NT"]
[]
["ZYZ LTD"]
["14-15 Bertam Ward Way"]
["Dereham"]
["Norfolk"]
["NR19 1TD"]
[]
["10th Way Ltd"]
["36 Stone Way"]
["St Neots"]
["PE19 2RY"]
[]
["3 Countries Self Drive"]
["Burners Lane North"]
["MK11 4RT"]
[]
["3663 XXY Group"]
["Westgate Ind Est"]
["Smith Road"]
["Northampton"]
["Buckinghamshire"]
["NN5 5NT"]

{"sheet":"answer","index":1,"n_rows":87,"n_cols":12,"rows_kept":87,"cut":false,"hidden":false,"merged":[[14,11,20,15]]}
["ZYZ LTD",{"$f":"IF(A1=\"\",ROW(),\"\")","v":""},{"$f":"SMALL(B:B,ROW())","v":6},{"$f":"IF(C1<>\"\",T(OFFSET($A$1,(COLUMN()-4),0,1,1)),\"\")","v":"ZYZ LTD"},"14-15 Bertam Ward Way","Dereham","Norfolk",{"$f":"IF(G1<>\"\",T(OFFSET($A$1,(COLUMN()-4),0,1,1)),\"\")","v":"NR19 1TD"},{"$f":"IF(H1<>\"\",T(OFFSET($A$1,(COLUMN()-4),0,1,1)),\"\")","v":""},{"$f":"IF(I1<>\"\",T(OFFSET($A$1,(COLUMN()-4),0,1,1)),\"\")","v":""},"",{"$f":"liref(D1)","v":"'=IF(C1<>\"\",T(OFFSET($A$1,(COLUMN()-4),0,1,1)),\"\")"}]
["14-15 Bertam Ward Way",{"$f":"IF(A2=\"\",ROW(),\"\")","v":""},{"$f":"SMALL(B:B,ROW())","v":11},{"$f":"IF(C2<>\"\",T(OFFSET($A$1,$C1+(COLUMN()-4),0,1,1)),\"\")","v":"10th Way Ltd"},{"$f":"IF(D2<>\"\",T(OFFSET($A$1,$C1+(COLUMN()-4),0,1,1)),\"\")","v":"36 Stone Way"},"St Neots","PE19 2RY","","","","",{"$f":"liref(D2)","v":"'=IF(C2<>\"\",T(OFFSET($A$1,$C1+(COLUMN()-4),0,1,1)),\"\")"}]
["Dereham","",{"$f":"SMALL(B:B,ROW())","v":15},{"$f":"IF(C3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":"3 Countries Self Drive"},{"$f":"IF(D3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":"Burners Lane North"},{"$f":"IF(E3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":"MK11 4RT"},{"$f":"IF(F3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":""},{"$f":"IF(G3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":""},{"$f":"IF(H3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":""},{"$f":"IF(I3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":""},{"$f":"IF(J3<>\"\",T(OFFSET($A$1,$C2+(COLUMN()-4),0,1,1)),\"\")","v":""}]
["Norfolk","",22,"3663 XXY Group","Westgate Ind Est","Smith Road","Northampton","Buckinghamshire","NN5 5NT","",""]
["NR19 1TD","",28,"ZYZ LTD","14-15 Bertam Ward Way","Dereham","Norfolk","NR19 1TD","","",""]
[null,6,33,"10th Way Ltd","36 Stone Way","St Neots","PE19 2RY","","","",""]
["10th Way Ltd","",37,"3 Countries Self Drive","Burners Lane North","MK11 4RT","","","","",""]
["36 Stone Way","",44,"3663 XXY Group","Westgate Ind Est","Smith Road","Northampton","Buckinghamshire","NN5 5NT","",""]
["St Neots","",50,"ZYZ LTD","14-15 Bertam Ward Way","Dereham","Norfolk","NR19 1TD","","",""]
["PE19 2RY","",55,"10th Way Ltd","36 Stone Way","St Neots","PE19 2RY","","","",""]
[null,11,59,"3 Countries Self Drive","Burners Lane North","MK11 4RT","","","","",""]
["3 Countries Self Drive","",66,"3663 XXY Group","Westgate Ind Est","Smith Road","Northampton","Buckinghamshire","NN5 5NT","",""]
["Burners Lane North","",72,"ZYZ LTD","14-15 Bertam Ward Way","Dereham","Norfolk","NR19 1TD","","",""]
["MK11 4RT","",77,"10th Way Ltd","36 Stone Way","St Neots","PE19 2RY","","","",""]
[null,15,81,"3 Countries Self Drive","Burners Lane North","MK11 4RT","","","","","","Write a VBA macro / Power Query script / Excel formulas to transpose data in column A in to the format indicated in the range C:G. Transpose the data in to answer sheet, starting from cell A1. Once done, please post your answers at Chandoo.org"]
["3663 XXY Group",""]
["Westgate Ind Est",""]
["Smith Road",""]
["Northampton",""]
["Buckinghamshire",""]
["NN5 5NT","",null,null,"column B formula",{"$f":"liref(B1)","v":"'=IF(A1=\"\",ROW(),\"\")"}]
[null,22,null,null,"Column C formula",{"$f":"liref(C1)","v":"'=SMALL(B:B,ROW())"}]
["ZYZ LTD",""]
["14-15 Bertam Ward Way",""]
["Dereham",""]
["Norfolk",""]
["NR19 1TD",""]
[null,28]
["10th Way Ltd",""]
["36 Stone Way",""]
["St Neots",""]
["PE19 2RY",""]
[null,33]
["3 Countries Self Drive",""]
["Burners Lane North",""]
["MK11 4RT",""]
[null,37]
["3663 XXY Group",""]
["Westgate Ind Est",""]
["Smith Road",""]
["Northampton",""]
["Buckinghamshire",""]
["NN5 5NT",""]
[null,44]
["ZYZ LTD",""]
["14-15 Bertam Ward Way",""]
["Dereham",""]
["Norfolk",""]
["NR19 1TD",""]
[null,50]
["10th Way Ltd",""]
["36 Stone Way",""]
["St Neots",""]
["PE19 2RY",""]
[null,55]
["3 Countries Self Drive",""]
["Burners Lane North",""]
["MK11 4RT",""]
[null,59]
["3663 XXY Group",""]
["Westgate Ind Est",""]
["Smith Road",""]
["Northampton",""]
["Buckinghamshire",""]
["NN5 5NT",""]
[null,{"$f":"IF(A66=\"\",ROW(),\"\")","v":66}]
["ZYZ LTD",""]
["14-15 Bertam Ward Way",""]
["Dereham",""]
["Norfolk",""]
["NR19 1TD",""]
[null,72]
["10th Way Ltd",""]
["36 Stone Way",""]
["St Neots",""]
["PE19 2RY",""]
[null,77]
["3 Countries Self Drive",""]
["Burners Lane North",""]
["MK11 4RT",""]
[null,81]
["3663 XXY Group",""]
["Westgate Ind Est",""]
["Smith Road",""]
["Northampton",""]
["Buckinghamshire",""]
["NN5 5NT",{"$f":"IF(A87=\"\",ROW()+1,\"\")","v":""}]