Transposing Data

J

Jools

New Member
Joined
Dec 17, 2009
Messages
4
Hi Guys,

I have a bit of a problem transposing some data, it might be really easy, but I cant think of an easy way around it (but i am only a novice excel user) I have lots of data like this, so an simple method would be great (maybe a Macro)

I want my Data to look like this:

<table style="border-collapse: collapse; width: 975pt;" width="1300" border="0" cellpadding="0" cellspacing="0"><col style="width: 75pt;" width="100"> <col style="width: 60pt;" width="80" span="15"> <tbody><tr style="height: 16.5pt;" height="22"> <td class="xl65" style="height: 16.5pt; width: 75pt;" width="100" height="22">Point</td> <td class="xl65" style="width: 60pt;" width="80">ΔX</td> <td class="xl65" style="width: 60pt;" width="80">ΔY</td> <td class="xl65" style="width: 60pt;" width="80">ΔZ</td> <td class="xl65" style="width: 60pt;" width="80">Code</td> <td class="xl65" style="width: 60pt;" width="80">Antenna height</td> <td class="xl65" style="width: 60pt;" width="80">Type</td> <td class="xl65" style="width: 60pt;" width="80">Hz Prec</td> <td class="xl65" style="width: 60pt;" width="80">Vt Prec</td> <td class="xl65" style="width: 60pt;" width="80">Satellites</td> <td class="xl65" style="width: 60pt;" width="80">PDOP</td> <td class="xl65" style="width: 60pt;" width="80">HDOP</td> <td class="xl65" style="width: 60pt;" width="80">VDOP</td> <td class="xl65" style="width: 60pt;" width="80">RMS</td> <td class="xl65" colspan="2" style="width: 120pt;" width="160">Positions used</td> </tr> <tr style="height: 16.5pt;" height="22"> <td style="height: 16.5pt;" height="22">c.wed.pm.j1</td> <td align="right">22.208</td> <td align="right">-112.632</td> <td align="right">-21.588</td> <td align="right">0</td> <td align="right">0</td> <td>Uncorrected</td> <td align="right">0.005</td> <td align="right">0.009</td> <td align="right">10</td> <td align="right">1.8</td> <td align="right">0.9</td> <td align="right">1.6</td> <td align="right">10.15</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td style="height: 16.5pt;" height="22">c.wed.pm.j2</td> <td align="right">21.904</td> <td align="right">-111.82</td> <td align="right">-21.341</td> <td align="right">0</td> <td align="right">0</td> <td>Uncorrected</td> <td align="right">0.006</td> <td align="right">0.011</td> <td align="right">10</td> <td align="right">1.8</td> <td align="right">0.9</td> <td align="right">1.6</td> <td align="right">12.75</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td style="height: 16.5pt;" height="22">c.wed.pm.j3</td> <td align="right">21.697</td> <td align="right">-110.92</td> <td align="right">-21.2</td> <td align="right">0</td> <td align="right">0</td> <td>Uncorrected</td> <td align="right">0.006</td> <td align="right">0.011</td> <td align="right">10</td> <td align="right">1.8</td> <td align="right">0.9</td> <td align="right">1.6</td> <td align="right">12.738</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td style="height: 16.5pt;" height="22">c.wed.pm.j4</td> <td align="right">21.526</td> <td align="right">-109.866</td> <td align="right">-21.015</td> <td align="right">0</td> <td align="right">0</td> <td>Uncorrected</td> <td align="right">0.006</td> <td align="right">0.012</td> <td align="right">11</td> <td align="right">1.7</td> <td align="right">0.8</td> <td align="right">1.5</td> <td align="right">14.25</td> <td align="right">1</td> <td>
</td> </tr> </tbody></table>

At the moment it looks like this (imported from a HTML file from a GPS device)

<table width="100%" border="1" cellpadding="2" rules="cols"><tbody><tr><th width="9%" align="left">Point</th> <td width="11%" align="right">c.wed.pm.j1</td> <th width="9%" align="left">ΔX</th> <td width="11%" align="right">22.208</td> <th width="9%" align="left">ΔY</th> <td width="11%" align="right">-112.632</td> <th width="9%" align="left">ΔZ</th> <td width="11%" align="right">-21.588</td> <th width="9%" align="left">Code</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">Antenna height</th> <td width="11%" align="right">0.000</td> <th width="9%" align="left">Type</th> <td width="11%" align="right">Uncorrected</td> <th width="9%" align="left">Hz Prec</th> <td width="11%" align="right">0.005</td> <th width="9%" align="left">Vt Prec</th> <td width="11%" align="right">0.009</td> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">QC 1</th> <td width="11%" align="right">
</td> <th width="9%" align="left">Satellites</th> <td width="11%" align="right">10</td> <th width="9%" align="left">PDOP</th> <td width="11%" align="right">1.8</td> <th width="9%" align="left">HDOP</th> <td width="11%" align="right">0.9</td> <th width="9%" align="left">VDOP</th> <td width="11%" align="right">1.6</td> </tr> <tr> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> <th width="9%" align="left">RMS</th> <td width="11%" align="right">10.150</td> <th width="9%" align="left">Positions used</th> <td width="11%" align="right">1</td> <th width="9%" align="left">Std. Dev (H)</th> <td width="11%" align="right">?</td> <th width="9%" align="left">Std. Dev (V)</th> <td width="11%" align="right">?</td> </tr></tbody></table><table width="100%" border="1" cellpadding="2" rules="cols"><tbody><tr> <th width="9%" align="left">Point</th> <td width="11%" align="right">c.wed.pm.j2</td> <th width="9%" align="left">ΔX</th> <td width="11%" align="right">21.904</td> <th width="9%" align="left">ΔY</th> <td width="11%" align="right">-111.820</td> <th width="9%" align="left">ΔZ</th> <td width="11%" align="right">-21.341</td> <th width="9%" align="left">Code</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">Antenna height</th> <td width="11%" align="right">0.000</td> <th width="9%" align="left">Type</th> <td width="11%" align="right">Uncorrected</td> <th width="9%" align="left">Hz Prec</th> <td width="11%" align="right">0.006</td> <th width="9%" align="left">Vt Prec</th> <td width="11%" align="right">0.011</td> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">QC 1</th> <td width="11%" align="right">
</td> <th width="9%" align="left">Satellites</th> <td width="11%" align="right">10</td> <th width="9%" align="left">PDOP</th> <td width="11%" align="right">1.8</td> <th width="9%" align="left">HDOP</th> <td width="11%" align="right">0.9</td> <th width="9%" align="left">VDOP</th> <td width="11%" align="right">1.6</td> </tr> <tr> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> <th width="9%" align="left">RMS</th> <td width="11%" align="right">12.750</td> <th width="9%" align="left">Positions used</th> <td width="11%" align="right">1</td> <th width="9%" align="left">Std. Dev (H)</th> <td width="11%" align="right">?</td> <th width="9%" align="left">Std. Dev (V)</th> <td width="11%" align="right">?</td> </tr></tbody></table><table width="100%" border="1" cellpadding="2" rules="cols"><tbody><tr> <th width="9%" align="left">Point</th> <td width="11%" align="right">c.wed.pm.j3</td> <th width="9%" align="left">ΔX</th> <td width="11%" align="right">21.697</td> <th width="9%" align="left">ΔY</th> <td width="11%" align="right">-110.920</td> <th width="9%" align="left">ΔZ</th> <td width="11%" align="right">-21.200</td> <th width="9%" align="left">Code</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">Antenna height</th> <td width="11%" align="right">0.000</td> <th width="9%" align="left">Type</th> <td width="11%" align="right">Uncorrected</td> <th width="9%" align="left">Hz Prec</th> <td width="11%" align="right">0.006</td> <th width="9%" align="left">Vt Prec</th> <td width="11%" align="right">0.011</td> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">QC 1</th> <td width="11%" align="right">
</td> <th width="9%" align="left">Satellites</th> <td width="11%" align="right">10</td> <th width="9%" align="left">PDOP</th> <td width="11%" align="right">1.8</td> <th width="9%" align="left">HDOP</th> <td width="11%" align="right">0.9</td> <th width="9%" align="left">VDOP</th> <td width="11%" align="right">1.6</td> </tr> <tr> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> <th width="9%" align="left">RMS</th> <td width="11%" align="right">12.738</td> <th width="9%" align="left">Positions used</th> <td width="11%" align="right">1</td> <th width="9%" align="left">Std. Dev (H)</th> <td width="11%" align="right">?</td> <th width="9%" align="left">Std. Dev (V)</th> <td width="11%" align="right">?</td> </tr></tbody></table><table width="100%" border="1" cellpadding="2" rules="cols"><tbody><tr> <th width="9%" align="left">Point</th> <td width="11%" align="right">c.wed.pm.j4</td> <th width="9%" align="left">ΔX</th> <td width="11%" align="right">21.526</td> <th width="9%" align="left">ΔY</th> <td width="11%" align="right">-109.866</td> <th width="9%" align="left">ΔZ</th> <td width="11%" align="right">-21.015</td> <th width="9%" align="left">Code</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">Antenna height</th> <td width="11%" align="right">0.000</td> <th width="9%" align="left">Type</th> <td width="11%" align="right">Uncorrected</td> <th width="9%" align="left">Hz Prec</th> <td width="11%" align="right">0.006</td> <th width="9%" align="left">Vt Prec</th> <td width="11%" align="right">0.012</td> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">QC 1</th> <td width="11%" align="right">
</td> <th width="9%" align="left">Satellites</th> <td width="11%" align="right">11</td> <th width="9%" align="left">PDOP</th> <td width="11%" align="right">1.7</td> <th width="9%" align="left">HDOP</th> <td width="11%" align="right">0.8</td> <th width="9%" align="left">VDOP</th> <td width="11%" align="right">1.5</td> </tr> <tr> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> <th width="9%" align="left">RMS</th> <td width="11%" align="right">14.250</td> <th width="9%" align="left">Positions used</th> <td width="11%" align="right">1</td> <th width="9%" align="left">Std. Dev (H)</th> <td width="11%" align="right">?</td> <th width="9%" align="left">Std. Dev (V)</th> <td width="11%" align="right">?</td> </tr></tbody></table><table width="100%" border="1" cellpadding="2" rules="cols"><tbody><tr> <th width="9%" align="left">Point</th> <td width="11%" align="right">c.wed.pm.j5</td> <th width="9%" align="left">ΔX</th> <td width="11%" align="right">21.346</td> <th width="9%" align="left">ΔY</th> <td width="11%" align="right">-108.971</td> <th width="9%" align="left">ΔZ</th> <td width="11%" align="right">-20.867</td> <th width="9%" align="left">Code</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">Antenna height</th> <td width="11%" align="right">0.000</td> <th width="9%" align="left">Type</th> <td width="11%" align="right">Uncorrected</td> <th width="9%" align="left">Hz Prec</th> <td width="11%" align="right">0.005</td> <th width="9%" align="left">Vt Prec</th> <td width="11%" align="right">0.010</td> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">QC 1</th> <td width="11%" align="right">
</td> <th width="9%" align="left">Satellites</th> <td width="11%" align="right">12</td> <th width="9%" align="left">PDOP</th> <td width="11%" align="right">1.6</td> <th width="9%" align="left">HDOP</th> <td width="11%" align="right">0.7</td> <th width="9%" align="left">VDOP</th> <td width="11%" align="right">1.5</td> </tr> <tr> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> <th width="9%" align="left">RMS</th> <td width="11%" align="right">12.506</td> <th width="9%" align="left">Positions used</th> <td width="11%" align="right">1</td> <th width="9%" align="left">Std. Dev (H)</th> <td width="11%" align="right">?</td> <th width="9%" align="left">Std. Dev (V)</th> <td width="11%" align="right">?</td> </tr></tbody></table><table width="100%" border="1" cellpadding="2" rules="cols"><tbody><tr> <th width="9%" align="left">Point</th> <td width="11%" align="right">c.wed.pm.j6</td> <th width="9%" align="left">ΔX</th> <td width="11%" align="right">21.170</td> <th width="9%" align="left">ΔY</th> <td width="11%" align="right">-107.974</td> <th width="9%" align="left">ΔZ</th> <td width="11%" align="right">-20.672</td> <th width="9%" align="left">Code</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">Antenna height</th> <td width="11%" align="right">0.000</td> <th width="9%" align="left">Type</th> <td width="11%" align="right">Uncorrected</td> <th width="9%" align="left">Hz Prec</th> <td width="11%" align="right">0.006</td> <th width="9%" align="left">Vt Prec</th> <td width="11%" align="right">0.012</td> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> </tr> <tr> <th width="9%" align="left">QC 1</th> <td width="11%" align="right">
</td> <th width="9%" align="left">Satellites</th> <td width="11%" align="right">12</td> <th width="9%" align="left">PDOP</th> <td width="11%" align="right">1.6</td> <th width="9%" align="left">HDOP</th> <td width="11%" align="right">0.7</td> <th width="9%" align="left">VDOP</th> <td width="11%" align="right">1.5</td> </tr> <tr> <th width="9%" align="left">
</th> <td width="11%" align="right">
</td> <th width="9%" align="left">RMS</th> <td width="11%" align="right">14.912</td> <th width="9%" align="left">Positions used</th> <td width="11%" align="right">1</td> <th width="9%" align="left">Std. Dev (H)</th> <td width="11%" align="right">?</td> <th width="9%" align="left">Std. Dev (V)</th> <td width="11%" align="right">?</td></tr></tbody></table>
and so on.

Thanks in advance, and sorry if this has already been answered!

Cheers

Jools
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Welcome to the board!

Just to make sure I understand correctly, the imported HTML in the first cell contains the three lines:

Point
Antenna height
QC 1

and the cell to the right of that contains

c.wed.pm.j1
0.000

etc, etc. Correct?
 
Upvote 0
Yep, thats right.

However, I dont need all the data the HTML file spits out.

I only REQUIRE

<table style="border-collapse: collapse; width: 975pt;" width="1300" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 16.5pt;" height="22"><td class="xl65" style="height: 16.5pt; width: 75pt;" width="100" height="22">Point</td> <td class="xl65" style="width: 60pt;" width="80">ΔX</td> <td class="xl65" style="width: 60pt;" width="80">ΔY</td> <td class="xl65" style="width: 60pt;" width="80">ΔZ</td> <td class="xl65" style="width: 60pt;" width="80">Code</td> <td class="xl65" style="width: 60pt;" width="80">Antenna height</td> <td class="xl65" style="width: 60pt;" width="80">Type</td> <td class="xl65" style="width: 60pt;" width="80">Hz Prec</td> <td class="xl65" style="width: 60pt;" width="80">Vt Prec</td> <td class="xl65" style="width: 60pt;" width="80">Satellites</td> <td class="xl65" style="width: 60pt;" width="80">PDOP</td> <td class="xl65" style="width: 60pt;" width="80">HDOP</td> <td class="xl65" style="width: 60pt;" width="80">VDOP</td> <td class="xl65" style="width: 60pt;" width="80">RMS</td> <td class="xl65" colspan="2" style="width: 120pt;" width="160">Positions used</td></tr></tbody></table>
But if it makes it easier I can always delete the various coloums

Cheers

Jools
 
Upvote 0
Hi, If you data starts in "A1" then try this:-
Results in Sheet(2), starts "A1".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG17Dec45
[COLOR="Navy"]Dim[/COLOR] rng1 [COLOR="Navy"]As[/COLOR] Range, ray, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] cl [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Set[/COLOR] rng1 = Range("A1:J4")
ReDim ray(1 To rng1.Count)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cl [COLOR="Navy"]In[/COLOR] rng1
 [COLOR="Navy"]If[/COLOR] Not cl.Column Mod 2 = 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not cl = "QC 1" And Not cl = "Std. Dev (H)" _
        And Not cl = "Std. Dev (V)" And cl <> "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ray(c) = cl
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] cl
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, R, Ac [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Range("A1").Resize(, c) = ray
[COLOR="Navy"]For[/COLOR] Rw = 1 To Rng.Count [COLOR="Navy"]Step[/COLOR] 4
        nRw = nRw + 1
        [COLOR="Navy"]Set[/COLOR] nRng = Range(Cells(Rw, 1), Cells(Rw + 3, 10))
    [COLOR="Navy"]For[/COLOR] R = 1 To UBound(ray)
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] nRng
            [COLOR="Navy"]If[/COLOR] Ac = ray(R) [COLOR="Navy"]Then[/COLOR]
                .Cells(nRw + 1, R) = Ac.Next
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac
     [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Mick, Thats great thanks.

(I had to change J4 to J5)

There is one slight problem though.

The data contains points which are actually 5 rows, and look like this:

<table style="border-collapse: collapse; width: 600pt;" width="800" border="0" cellpadding="0" cellspacing="0" rules="cols"><col style="width: 60pt;" width="80" span="10"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl63" style="height: 30pt; width: 60pt;" width="80" height="40">Point</td> <td class="xl64" style="border-left: medium none; width: 60pt;" width="80">c.wed.pm.j147</td> <td class="xl63" style="border-left: medium none; width: 60pt;" width="80">ΔX</td> <td class="xl64" style="border-left: medium none; width: 60pt;" width="80">143.246</td> <td class="xl63" style="border-left: medium none; width: 60pt;" width="80">ΔY</td> <td class="xl64" style="border-left: medium none; width: 60pt;" width="80">59.825</td> <td class="xl63" style="border-left: medium none; width: 60pt;" width="80">ΔZ</td> <td class="xl64" style="border-left: medium none; width: 60pt;" width="80">-98.656</td> <td class="xl63" style="border-left: medium none; width: 60pt;" width="80">Code</td> <td class="xl64" style="border-left: medium none; width: 60pt;" width="80"> </td> </tr> <tr style="height: 31.5pt;" height="42"> <td class="xl63" style="border-top: medium none; height: 31.5pt; width: 60pt;" width="80" height="42">Antenna height</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">1.758</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Type</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Uncorrected</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Hz Prec</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">0.012</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Vt Prec</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">0.021</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80"> </td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl63" style="border-top: medium none; height: 15.75pt; width: 60pt;" width="80" height="21">QC 1</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Satellites</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">11</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">PDOP</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">1.6</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">HDOP</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">0.8</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">VDOP</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">1.4</td> </tr> <tr style="height: 31.5pt;" height="42"> <td class="xl63" style="border-top: medium none; height: 31.5pt; width: 60pt;" width="80" height="42"> </td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">RMS</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">25.694</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Positions used</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">66</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Std. Dev (H)</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">?</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Std. Dev (V)</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">?</td> </tr> <tr style="height: 31.5pt;" height="42"> <td class="xl63" style="border-top: medium none; height: 31.5pt; width: 60pt;" width="80" height="42">GPS week</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">1561</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Seconds</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">316510</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Initialization type</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">On the fly</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Survey type</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Real-time</td> <td class="xl63" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">Initialization count</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 60pt;" width="80">4</td> </tr> </tbody></table>
This messes up the data somewhat, and requries some moving of data when the macro is complete.

I dont know if this would make the code much more difficult, if it does, then I can live. But would be cool to get it working perfectly as I have loads of files to do.

Cheers

Jools
 
Upvote 0
dear,

This can be done with a macro (see attachment)

Just copy the data in sheet1.
run the macro

How it works:
- Reed the cells in an array
- dump the array in sheet3

Hope this will help you.

br,
 
Upvote 0
dear,

sorry, is there no possebility to add a attachment in MrExcel?


Here is the code for the macro=
=======================================

Type DATA_RECORD
POINT As String
DX As Single
DY As Single
DZ As Single
CODE As String
ANTENNA_HEIGHT As Single
TYPE_DATA As String
HZ_PREC As String
VT_PREC As String
SATELLITES As String
PDOP As Single
HDOP As Single
VDOP As Single
RMS As Single
POSITIONS_USED As Single
End Type
Dim DATA_ARRAY() As DATA_RECORD
Sub Macro_Transpose()
Dim i As Integer
'§ reed the data from sheet1
Worksheets("sheet1").Activate
Range("A1").Activate
ReDim DATA_ARRAY(0)
Do While ActiveCell.Value <> ""
ReDim Preserve DATA_ARRAY(UBound(DATA_ARRAY) + 1)
With DATA_ARRAY(UBound(DATA_ARRAY))
.POINT = ActiveCell.Offset(0, 1).Value
.DX = ActiveCell.Offset(0, 3).Value
.DY = ActiveCell.Offset(0, 5).Value
.DZ = ActiveCell.Offset(0, 7).Value
.CODE = ActiveCell.Offset(0, 9).Value
.ANTENNA_HEIGHT = ActiveCell.Offset(1, 1).Value
.TYPE_DATA = ActiveCell.Offset(1, 3).Value
.HZ_PREC = ActiveCell.Offset(1, 5).Value
.VT_PREC = ActiveCell.Offset(1, 7).Value
.SATELLITES = ActiveCell.Offset(2, 3).Value
.PDOP = ActiveCell.Offset(2, 5).Value
.HDOP = ActiveCell.Offset(2, 7).Value
.VDOP = ActiveCell.Offset(2, 9).Value
.RMS = ActiveCell.Offset(3, 3).Value
.POSITIONS_USED = ActiveCell.Offset(3, 5).Value
End With
ActiveCell.Offset(4, 0).Activate
Loop
'§ dump the data in sheet3
Worksheets("sheet3").Activate
Range("A2").Activate
For i = 1 To UBound(DATA_ARRAY)
ActiveCell.Value = DATA_ARRAY(i).POINT
ActiveCell.Offset(0, 1).Value = DATA_ARRAY(i).DX
ActiveCell.Offset(0, 2).Value = DATA_ARRAY(i).DY
ActiveCell.Offset(0, 3).Value = DATA_ARRAY(i).DZ
ActiveCell.Offset(0, 4).Value = DATA_ARRAY(i).CODE
ActiveCell.Offset(0, 5).Value = DATA_ARRAY(i).ANTENNA_HEIGHT
ActiveCell.Offset(0, 6).Value = DATA_ARRAY(i).TYPE_DATA
ActiveCell.Offset(0, 7).Value = DATA_ARRAY(i).HZ_PREC
ActiveCell.Offset(0, 8).Value = DATA_ARRAY(i).VT_PREC
ActiveCell.Offset(0, 9).Value = DATA_ARRAY(i).SATELLITES
ActiveCell.Offset(0, 10).Value = DATA_ARRAY(i).PDOP
ActiveCell.Offset(0, 11).Value = DATA_ARRAY(i).HDOP
ActiveCell.Offset(0, 12).Value = DATA_ARRAY(i).VDOP
ActiveCell.Offset(0, 13).Value = DATA_ARRAY(i).RMS
ActiveCell.Offset(0, 14).Value = DATA_ARRAY(i).POSITIONS_USED
ActiveCell.Offset(1, 0).Activate
Next
End Sub

=============================================



br,
 
Upvote 0
Hi, If you Changed "J4" to "J5" I believe you would increase the Data in the sheet (2) header. Do you want that??
I have left "J4" as was, but added a row after each "Point" to give a 5 row spacing.
The code now returns the same as when I had the data set at the 4 row spacing, Have a go , see what you find.
Code: 
[COLOR=navy]Sub[/COLOR] MG17Dec20
[COLOR=navy]Dim[/COLOR] rng1 [COLOR=navy]As[/COLOR] Range, Ray, c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] cl [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Set[/COLOR] rng1 = Range("A1:J4")
ReDim Ray(1 To rng1.Count)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] cl [COLOR=navy]In[/COLOR] rng1
 [COLOR=navy]If[/COLOR] Not cl.Column Mod 2 = 0 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] Not cl = "QC 1" And Not cl = "Std. Dev (H)" _
        And Not cl = "Std. Dev (V)" And cl <> "" [COLOR=navy]Then[/COLOR]
            c = c + 1
            Ray(c) = cl
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
 [COLOR=navy]Next[/COLOR] cl
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range, R, Ac [COLOR=navy]As[/COLOR] Range, Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nRw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet2")
    .Range("A1").Resize(, c) = Ray
[COLOR=navy]For[/COLOR] Rw = 1 To Rng.Count [COLOR=navy]Step[/COLOR] 5
        nRw = nRw + 1
        [COLOR=navy]Set[/COLOR] nRng = Range(Cells(Rw, 1), Cells(Rw + 3, 10))
    [COLOR=navy]For[/COLOR] R = 1 To UBound(Ray)
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ac [COLOR=navy]In[/COLOR] nRng
            [COLOR=navy]If[/COLOR] Ac = Ray(R) [COLOR=navy]Then[/COLOR]
                .Cells(nRw + 1, R) = Ac.Next
                [COLOR=navy]Exit[/COLOR] For
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Ac
     [COLOR=navy]Next[/COLOR] R
[COLOR=navy]Next[/COLOR] Rw
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
97
Kaper
Kaper
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
J
  • Question Question
Moving duplicate Column Data to individual rows in VBA
Replies
6
Views
353
Peter_SSs
Peter_SSs
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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