VBA code needed - Transpose array of data

S

Sparktracer

New Member
Joined
May 31, 2007
Messages
38
Mr.Excel,
I am in a major time crunch from a customer.
I have csv files that contain over 40k rows of data each. I need to adjust the data to the customer. Example raw data:
Code: 
[TABLE="width: 882"]
<tbody>[TR]
[TD]time[/TD]
[TD]ExternalProductionNumber[/TD]
[TD]InternalProductionNumber[/TD]
[TD]Model[/TD]
[TD]MeasurementPointIndex[/TD]
[TD]Name[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33301[/TD]
[TD]L517[/TD]
[TD="align: right"]7.210414[/TD]
[TD="align: right"]-0.908913[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33401[/TD]
[TD]R517[/TD]
[TD="align: right"]7.301985[/TD]
[TD="align: right"]-1.389439[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33302[/TD]
[TD]L120[/TD]
[TD="align: right"]4.763309[/TD]
[TD="align: right"]-0.564029[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33402[/TD]
[TD]R120[/TD]
[TD="align: right"]3.962694[/TD]
[TD="align: right"]-1.138026[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33303[/TD]
[TD]L126[/TD]
[TD="align: right"]5.348956[/TD]
[TD="align: right"]0.843539[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33403[/TD]
[TD]R126[/TD]
[TD="align: right"]4.065893[/TD]
[TD="align: right"]-1.342114[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33304[/TD]
[TD]L136[/TD]
[TD="align: right"]3.632112[/TD]
[TD="align: right"]1.893941[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33404[/TD]
[TD]R136[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33306[/TD]
[TD]L154[/TD]
[TD="align: right"]4.427203[/TD]
[TD="align: right"]2.76657[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33406[/TD]
[TD]R154[/TD]
[TD="align: right"]4.106397[/TD]
[TD="align: right"]3.364711[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33307[/TD]
[TD]L177[/TD]
[TD="align: right"]4.301213[/TD]
[TD="align: right"]2.986984[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33407[/TD]
[TD]R177[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33308[/TD]
[TD]L173[/TD]
[TD="align: right"]3.505007[/TD]
[TD="align: right"]1.982956[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33408[/TD]
[TD]R173[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33309[/TD]
[TD]L596[/TD]
[TD="align: right"]4.597087[/TD]
[TD="align: right"]-0.701192[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33409[/TD]
[TD]R596[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33313[/TD]
[TD]L236[/TD]
[TD="align: right"]3.648115[/TD]
[TD="align: right"]3.588339[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33413[/TD]
[TD]R236[/TD]
[TD="align: right"]2.957119[/TD]
[TD="align: right"]1.971066[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33314[/TD]
[TD]L222[/TD]
[TD="align: right"]3.633311[/TD]
[TD="align: right"]2.316997[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 17:30[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]33414[/TD]
[TD]R222[/TD]
[TD="align: right"]2.989634[/TD]
[TD="align: right"]2.332784[/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12301[/TD]
[TD]L517[/TD]
[TD="align: right"]6.513954[/TD]
[TD="align: right"]-0.169936[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12401[/TD]
[TD]R517[/TD]
[TD="align: right"]6.466931[/TD]
[TD="align: right"]0.409988[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12302[/TD]
[TD]L120[/TD]
[TD="align: right"]4.054636[/TD]
[TD="align: right"]-0.961532[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12402[/TD]
[TD]R120[/TD]
[TD="align: right"]4.331379[/TD]
[TD="align: right"]0.077887[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12303[/TD]
[TD]L126[/TD]
[TD="align: right"]4.116351[/TD]
[TD="align: right"]-0.060048[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12403[/TD]
[TD]R126[/TD]
[TD="align: right"]4.44392[/TD]
[TD="align: right"]0.331252[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12304[/TD]
[TD]L136[/TD]
[TD="align: right"]5.294203[/TD]
[TD="align: right"]0.477066[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12404[/TD]
[TD]R136[/TD]
[TD="align: right"]4.016003[/TD]
[TD="align: right"]1.949025[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12407[/TD]
[TD]R177[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12408[/TD]
[TD]R173[/TD]
[TD="align: right"]3.283198[/TD]
[TD="align: right"]0.723313[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12307[/TD]
[TD]L177[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12308[/TD]
[TD]L173[/TD]
[TD="align: right"]4.694121[/TD]
[TD="align: right"]1.308713[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12309[/TD]
[TD]L596[/TD]
[TD="align: right"]6.901759[/TD]
[TD="align: right"]-2.048102[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12409[/TD]
[TD]R596[/TD]
[TD="align: right"]5.680728[/TD]
[TD="align: right"]-0.811558[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12310[/TD]
[TD]L209[/TD]
[TD="align: right"]4.894162[/TD]
[TD="align: right"]2.678812[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12410[/TD]
[TD]R209[/TD]
[TD="align: right"]4.807786[/TD]
[TD="align: right"]3.729372[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12411[/TD]
[TD]R205[/TD]
[TD="align: right"]4.934049[/TD]
[TD="align: right"]2.925173[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018 21:38[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12311[/TD]
[TD]L205[/TD]
[TD="align: right"]4.781547[/TD]
[TD="align: right"]0.941445[/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[/TR]
</tbody>[/TABLE]

First 4 columns are identical for a sample set. Number of rows varies per sample set.
I need a single row for each sample set:
Code: 
[TABLE="width: 3097"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[/TR]
[TR]
[TD]date[/TD]
[TD]time[/TD]
[TD]ExternalProductionNumber[/TD]
[TD]InternalProductionNumber[/TD]
[TD]Model[/TD]
[TD="align: right"]33301[/TD]
[TD]L517[/TD]
[TD="align: right"]33401[/TD]
[TD]R517[/TD]
[TD="align: right"]33302[/TD]
[TD]L120[/TD]
[TD="align: right"]33402[/TD]
[TD]R120[/TD]
[TD="align: right"]33303[/TD]
[TD]L126[/TD]
[TD="align: right"]33403[/TD]
[TD]R126[/TD]
[TD="align: right"]33304[/TD]
[TD]L136[/TD]
[TD="align: right"]33404[/TD]
[TD]R136[/TD]
[TD="align: right"]33306[/TD]
[TD]L154[/TD]
[TD="align: right"]33406[/TD]
[TD]R154[/TD]
[TD="align: right"]33307[/TD]
[TD]L177[/TD]
[TD="align: right"]33407[/TD]
[TD]R177[/TD]
[TD="align: right"]33308[/TD]
[TD]L173[/TD]
[TD="align: right"]33408[/TD]
[TD]R173[/TD]
[TD="align: right"]33309[/TD]
[TD]L596[/TD]
[TD="align: right"]33409[/TD]
[TD]R596[/TD]
[TD="align: right"]33313[/TD]
[TD]L236[/TD]
[TD="align: right"]33413[/TD]
[TD]R236[/TD]
[TD="align: right"]33314[/TD]
[TD]L222[/TD]
[TD="align: right"]33414[/TD]
[TD]R222[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018[/TD]
[TD]  5:30:44 PM[/TD]
[TD]1XYZW1EG8JFC99999[/TD]
[TD="align: right"]2062207[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]7.2104[/TD]
[TD="align: right"]-0.90891[/TD]
[TD="align: right"]7.301985[/TD]
[TD="align: right"]-1.38944[/TD]
[TD="align: right"]4.763309[/TD]
[TD="align: right"]-0.56403[/TD]
[TD="align: right"]3.962694[/TD]
[TD="align: right"]-1.13803[/TD]
[TD="align: right"]5.348956[/TD]
[TD="align: right"]0.843539[/TD]
[TD="align: right"]4.065893[/TD]
[TD="align: right"]-1.34211[/TD]
[TD="align: right"]3.632112[/TD]
[TD="align: right"]1.893941[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.427203[/TD]
[TD="align: right"]2.76657[/TD]
[TD="align: right"]4.106397[/TD]
[TD="align: right"]3.364711[/TD]
[TD="align: right"]4.301213[/TD]
[TD="align: right"]2.986984[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.505007[/TD]
[TD="align: right"]1.982956[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.597087[/TD]
[TD="align: right"]-0.70119[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.648115[/TD]
[TD="align: right"]3.588339[/TD]
[TD="align: right"]2.957119[/TD]
[TD="align: right"]1.971066[/TD]
[TD="align: right"]3.633311[/TD]
[TD="align: right"]2.316997[/TD]
[TD="align: right"]2.989634[/TD]
[TD="align: right"]2.332784[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD]Gap[/TD]
[TD]Flush[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12301[/TD]
[TD]L517[/TD]
[TD="align: right"]12401[/TD]
[TD]R517[/TD]
[TD="align: right"]12302[/TD]
[TD]L120[/TD]
[TD="align: right"]12402[/TD]
[TD]R120[/TD]
[TD="align: right"]12303[/TD]
[TD]L126[/TD]
[TD="align: right"]12403[/TD]
[TD]R126[/TD]
[TD="align: right"]12304[/TD]
[TD]L136[/TD]
[TD="align: right"]12404[/TD]
[TD]R136[/TD]
[TD="align: right"]12407[/TD]
[TD]R177[/TD]
[TD="align: right"]12408[/TD]
[TD]R173[/TD]
[TD="align: right"]12307[/TD]
[TD]L177[/TD]
[TD="align: right"]12308[/TD]
[TD]L173[/TD]
[TD="align: right"]12309[/TD]
[TD]L596[/TD]
[TD="align: right"]12409[/TD]
[TD]R596[/TD]
[TD="align: right"]12310[/TD]
[TD]L209[/TD]
[TD="align: right"]12410[/TD]
[TD]R209[/TD]
[TD="align: right"]12411[/TD]
[TD]R205[/TD]
[TD="align: right"]12311[/TD]
[TD]L205[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018[/TD]
[TD="align: right"]9:38:21 PM[/TD]
[TD]1XYZW1EG8JFD55555[/TD]
[TD="align: right"]2062825[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]6.514[/TD]
[TD="align: right"]-0.16994[/TD]
[TD="align: right"]6.466931[/TD]
[TD="align: right"]0.409988[/TD]
[TD="align: right"]4.054636[/TD]
[TD="align: right"]-0.96153[/TD]
[TD="align: right"]4.331379[/TD]
[TD="align: right"]0.077887[/TD]
[TD="align: right"]4.116351[/TD]
[TD="align: right"]-0.06005[/TD]
[TD="align: right"]4.44392[/TD]
[TD="align: right"]0.331252[/TD]
[TD="align: right"]5.294203[/TD]
[TD="align: right"]0.477066[/TD]
[TD="align: right"]4.016003[/TD]
[TD="align: right"]1.949025[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.283198[/TD]
[TD="align: right"]0.723313[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.694121[/TD]
[TD="align: right"]1.308713[/TD]
[TD="align: right"]6.901759[/TD]
[TD="align: right"]-2.0481[/TD]
[TD="align: right"]5.680728[/TD]
[TD="align: right"]-0.81156[/TD]
[TD="align: right"]4.894162[/TD]
[TD="align: right"]2.678812[/TD]
[TD="align: right"]4.807786[/TD]
[TD="align: right"]3.729372[/TD]
[TD="align: right"]4.934049[/TD]
[TD="align: right"]2.925173[/TD]
[TD="align: right"]4.781547[/TD]
[TD="align: right"]0.941445[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I do not necessarily need the blank rows but I do need all data points (GAP / FLUSH) that correspond with the data set all to be on one row.

Any assistance is greatly appreciated. My engineer that I normally work with on these types of needs is not available. Thanks in advance to any assistance provided.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
If your data has blank rows, you'll need to get rid of them for this to work as is.


Code: 
Option Explicit
Sub TransPoseIt()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim r As Long
Dim rTgt As Long
Dim cTgt As Long
Dim rngSource As Range
Dim rngTarget As Range
Dim GapCol As Integer
Dim FlushCol As Integer
GapCol = 6
FlushCol = 7
Set wsSource = Sheets("Sheet1")
Set wsTarget = Sheets("Sheet2")
Set rngSource = wsSource.Range("A2")
Set rngTarget = wsTarget.Range("A2")
r = 0
rTgt = 0

Do While rngSource.Offset(r, 0) <> ""
    rngTarget.Offset(rTgt, 0) = Format(rngSource.Offset(r, 0), "mm/dd/yyyy")
    rngTarget.Offset(rTgt, 1) = Format(rngSource.Offset(r, 0), "hh:mm")
    rngTarget.Offset(rTgt, 2) = rngSource.Offset(r, 1)
    rngTarget.Offset(rTgt, 3) = rngSource.Offset(r, 2)
    rngTarget.Offset(rTgt, 4) = rngSource.Offset(r, 3)
    cTgt = 3
    Do While rngTarget.Offset(rTgt, 2) = rngSource.Offset(r, 1) _
        And rngTarget.Offset(rTgt, 3) = rngSource.Offset(r, 2) _
        And rngTarget.Offset(rTgt, 4) = rngSource.Offset(r, 3)
        cTgt = cTgt + 2
        rngTarget.Offset(rTgt, cTgt) = rngSource.Offset(r, GapCol)
        rngTarget.Offset(rTgt, cTgt + 1) = rngSource.Offset(r, FlushCol)
        r = r + 1
    Loop
    rTgt = rTgt + 1
Loop
End Sub
 
Upvote 0
PatOBrien198,

You are a rock star sir.

I ran your code on one sheet and it looks good.

I will update if I have any issues.

FYI, there are no blank rows in the origin CSV file.

Thanks for the quick supply of Macro Code.
 
Upvote 0
Hello PatOBrien198 or any other guru that can assist,

After running the great code from Pat, we determined we needed to remove the rows of data in Sheet 1 that had NULL value on the Name column and add headers on Sheet 2. We have added to the code to remove NULL data and also sort by the MeasurementPointIndex (Now called ID). Here is the code:
Code: 
Option Explicit

Sub TransPoseIt()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim Lastrow As Long
Dim r, j, k, s, i, n As Long
Dim rTgt, LastColumn, FinalColumn As Long
Dim cTgt As Long
Dim rngSource As Range
Dim rngTarget As Range
Dim GapCol As Integer
Dim FlushCol As Integer
Dim mp As String
GapCol = 6
FlushCol = 7
Set wsSource = Sheets("Sheet1")
Set wsTarget = Sheets("Sheet2")
Set rngSource = wsSource.Range("A2")
Set rngTarget = wsTarget.Range("A2")
r = 0
rTgt = 0
j = 2
k = 2
s = 2
mp = ""


'Delete NULL data rows


Sheets("Sheet1").Activate


Application.ScreenUpdating = False
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For n = Lastrow To 1 Step -1
    If Cells(n, 6).Value = "NULL" Then Cells(n, 6).EntireRow.Delete
Next n
Application.ScreenUpdating = True


' Sort by Date(Col'A') and then 'ID'(Col'E')
'
    Columns("A:BB").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "A2:A99999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "E2:E99999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:BB99999")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    
'Transfer data to sheet2


Do While rngSource.Offset(r, 0) <> ""
    rngTarget.Offset(rTgt, 0) = Format(rngSource.Offset(r, 0), "mm/dd/yyyy")
    rngTarget.Offset(rTgt, 1) = Format(rngSource.Offset(r, 0), "hh:mm")
    rngTarget.Offset(rTgt, 2) = rngSource.Offset(r, 1)
    rngTarget.Offset(rTgt, 3) = rngSource.Offset(r, 2)
    rngTarget.Offset(rTgt, 4) = rngSource.Offset(r, 3)
    cTgt = 3
    Do While rngTarget.Offset(rTgt, 2) = rngSource.Offset(r, 1) _
        And rngTarget.Offset(rTgt, 3) = rngSource.Offset(r, 2) _
        And rngTarget.Offset(rTgt, 4) = rngSource.Offset(r, 3)
        cTgt = cTgt + 2
        rngTarget.Offset(rTgt, cTgt) = rngSource.Offset(r, GapCol)
        rngTarget.Offset(rTgt, cTgt + 1) = rngSource.Offset(r, FlushCol)
        r = r + 1
    Loop
    rTgt = rTgt + 1
Loop


'Headers
Lastrow = wsTarget.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
    LastColumn = wsTarget.Cells(i, Columns.Count).End(xlToLeft).Column
    If LastColumn > FinalColumn Then
        FinalColumn = LastColumn
    End If
Next
wsTarget.Cells(1, 1).Value = "Date"
wsTarget.Cells(1, 2).Value = "Time"
wsTarget.Cells(1, 3).Value = "VIN"
wsTarget.Cells(1, 4).Value = "IPN"
wsTarget.Cells(1, 5).Value = "Model"
Line10:
k = 2
Do While wsSource.Cells(s, 2).Value = wsSource.Cells(j, 2).Value
    mp = CStr(Right(wsSource.Cells(j, 5), 3))
    wsTarget.Cells(1, k + 4) = mp + " - Gap"
    wsTarget.Cells(1, k + 5) = mp + " - Flush"
    k = k + 2
    j = j + 1
Loop
If k <> FinalColumn - 3 Then
    s = j
    GoTo Line10
End If


End Sub

What we now have determined that our database is sometimes missing data and then the data is not in the proper column.

We need an update to the code above that will add headers and then put the data from a row into the proper column.

In the first example: ID 33406 is associated with R154 and has data for Gap and Flush
The headers are currently labeled as 406 - GAP and 406 - FLUSH.
We need all 406 - GAP data in the same column. If data was not saved for an ID then the cells are left blank
I can provide example data also.
 
Upvote 0
Maybe a brute force method would be to replace the line:
cTgt = cTgt + 2
with something like:

Select Case value(right(rngSource.Offset(r, 4),3)) 'if the ID is in the 5th column (offset of 4 columns from A)
Case 301
cTgt = 4
Case 302
cTgt = 6
Case 401
cTgt = 8

' etc
case else
msgbox "Need to define a new column"
End Select
 
Upvote 0
Thanks Pat, but a brute force still does not get data in the correct column. I would like to use a Master Template to transpose the data too.

Code: 
[TABLE="width: 3392"]
 <colgroup><col width="64" span="53" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]Date[/TD]
  [TD="width: 64"]Time[/TD]
  [TD="width: 64"]VIN[/TD]
  [TD="width: 64"]IPN[/TD]
  [TD="width: 64"]Model[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]Hood Nose[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]Hood BC Front[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]Hood Mid[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]Hood BC Rear[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FDR-Aplr Hi[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FDR-Roof Front[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FDR-Roof Rear[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FDR-RDR wind[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]RDR-Roof Front[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]RDR-Roof Rear[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]RDR-COP wind[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FNDR-FDR Hi[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FNDR-FDR Low[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FDR-RDR Low[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FDR-RDR mid[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]FDR-RDR Hi[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]TG Hi[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]TG Low[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]Cab-Box[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]RDR-COP Hi[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]RDR-COP Low[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]RDR hi Trim[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]RDR lo Trim[/TD]
  [TD="class: xl63, width: 128, colspan: 2"]Bump-Box[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]L517[/TD]
  [TD]R517[/TD]
  [TD]L120[/TD]
  [TD]R120[/TD]
  [TD]L126[/TD]
  [TD]R126[/TD]
  [TD]L136[/TD]
  [TD]R136[/TD]
  [TD]L154[/TD]
  [TD]R154[/TD]
  [TD]L177[/TD]
  [TD]R177[/TD]
  [TD]L173[/TD]
  [TD]R173[/TD]
  [TD]L596[/TD]
  [TD]R596[/TD]
  [TD]L236/ L209[/TD]
  [TD]R236 / R209[/TD]
  [TD]L222 / L205[/TD]
  [TD]R222 / R205[/TD]
  [TD]L647 / L623[/TD]
  [TD]R647 / R623[/TD]
  [TD]L557[/TD]
  [TD]R557[/TD]
  [TD]L551[/TD]
  [TD]R551[/TD]
  [TD]L576[/TD]
  [TD]R576[/TD]
  [TD]L578[/TD]
  [TD]R578[/TD]
  [TD]L583[/TD]
  [TD]R583[/TD]
  [TD]L668[/TD]
  [TD]R668[/TD]
  [TD]R680[/TD]
  [TD]L680[/TD]
  [TD]L637[/TD]
  [TD]R637[/TD]
  [TD]L635 / L615 / L589[/TD]
  [TD]R635 / R615 / R589[/TD]
  [TD]L631 / L613 / L581[/TD]
  [TD]R631 / R613 / R581[/TD]
  [TD]L626-A / L609-A / L576-A[/TD]
  [TD]R626-A / R609-A / R576-A[/TD]
  [TD]L626-B / L609-B / L576-B[/TD]
  [TD]R626-B / R609-B / R576-B[/TD]
  [TD]L678[/TD]
  [TD]R678[/TD]
[/TR]
</tbody>[/TABLE]
This would be the layout we need for the data. Then the data can match the columns. I know that multiple data into a single column is tricky, but it would get the customer happy.
The Template could easily be a hidden or deleted sheet once the transpose is done. The headers above are not sorted.
 
Upvote 0
This is a good example of scope creep.;)

Code: 
'near the top of the routine...
Dim rngToFind As Range


'then, replace the do loop with this...

    Do While rngTarget.Offset(rTgt, 2) = rngSource.Offset(r, 1) _
        And rngTarget.Offset(rTgt, 3) = rngSource.Offset(r, 2) _
        And rngTarget.Offset(rTgt, 4) = rngSource.Offset(r, 3)
        
        ' assuming that row 2 of the Target data sheet has the column headers... L120, etc
        Set rngToFind = wsTarget.Rows("2:2").Find(what:=rngSource.Offset(r, 5), LookAt:=xlPart)  '5th offset col is L120, R126, etc.
        If Not rngToFind Is Nothing Then
            cTgt = rngToFind.Column
        Else
            MsgBox "Name not found..." & vbCrLf & rngSource.Offset(r, 5)
        End If
        
        '  So it looks like your template has only one column for each Name L517, R517, but
        '  your data has both a Gap and a Flush value.  It needs some attention...
        
        rngTarget.Offset(rTgt, cTgt) = rngSource.Offset(r, GapCol)
        rngTarget.Offset(rTgt, cTgt + 1) = rngSource.Offset(r, FlushCol)
        r = r + 1
    Loop
 
Last edited:
Upvote 0
Pat,
You still rock. I understand by your scope creep comment and you are correct. Thank you for understanding.
You were also correct in my headers. I totally messed that one up.
I will be trying your update to the DO Loop and let you know what happens.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
293
madi1004
M
S
  • Solved
Need help modifying an Excel VBA coded to add a check for new hires less that 90 days.
Replies
1
Views
393
spittingfire
S
P
  • Question
Copying and transposing data from one worksheet to another
Replies
1
Views
351
jdellasala
J
D
  • Question
VBA Code - the code is not pasting to the column I need it to.
Replies
2
Views
579
dragontbone
D
aba1csims
  • Question
Need a VBA script to pull most recent training dates
Replies
4
Views
594
aba1csims
aba1csims

Forum statistics

Threads
1,215,326
Messages
6,124,256
Members
449,149
Latest member
mwdbActuary

Share this page

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