Excel 2010 - Traspose lowest row to upper row

M

mauriziosessa

New Member
Joined
Nov 27, 2012
Messages
13
Hi guys,
I am not able to solve this problem.
I would transpose value from rows with blank CASC. value to the upper row with a not blank CASC. value.
The number of row with blank CASC. value is variable.


i.e. :

<!--EndFragment-->​
[TABLE="width: 1235"]
<tbody>[TR]
[TD="width: 65, align: right"]Column= [/TD]
[TD="width: 65, align: right"]CASC.[/TD]
[TD="width: 65, align: right"]C[/TD]
[TD="width: 65, align: right"]D[/TD]
[TD="width: 65, align: right"]E[/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"][/TD]
[TD="width: 65, align: right"]CASC.[/TD]
[TD="width: 65, align: right"]C[/TD]
[TD="width: 65, align: right"]D[/TD]
[TD="width: 65, align: right"]E[/TD]
[TD="width: 65, align: right"]F[/TD]
[TD="width: 65, align: right"]G[/TD]
[TD="width: 65, align: right"]H[/TD]
[TD="width: 65, align: right"]I[/TD]
[TD="width: 65, align: right"]L[/TD]
[TD="width: 65, align: right"]M[/TD]
[TD="width: 65"] N[/TD]
[TD="width: 65"] O[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"]e1[/TD]
[TD="align: right"]f1[/TD]
[TD="align: right"]g1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"]e1[/TD]
[TD="align: right"]f1[/TD]
[TD="align: right"]g1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e1[/TD]
[TD="align: right"]f1[/TD]
[TD="align: right"]g1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"]e1[/TD]
[TD="align: right"]f1[/TD]
[TD="align: right"]g1[/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]RAW DATA[/TD]
[TD="align: right"][/TD]
[TD="align: right"]e1[/TD]
[TD="align: right"]f1[/TD]
[TD="align: right"]g1[/TD]
[TD="class: xl63, colspan: 2, align: right"]SOLUTION ->[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e1[/TD]
[TD="align: right"]f1[/TD]
[TD="align: right"]g1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]e[/TD]
[TD="align: right"]f[/TD]
[TD="align: right"]g[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


could someone help me?
Thank you very much for your help.

Maurizio
 
Last edited:
Sort by date Sort by votes
Re: FIRST ISSUE SOLVED - Re: Excel 2010 - Traspose lowest row to upper row

I want to apply the macro that you did on this dataset:

Code: 
[B]MACRO[/B]
sub ReorgData()Dim mc As Long, n As Long, sr As Long, er As Long
Dim lr As Long, lc As Long, nc As Long
Dim b As Variant, o As Variant
Dim i As Long, j As Long, c As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "C").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
b = Range(Cells(1, 2), Cells(lr, lc))
sr = 1
For i = 2 To lr Step 1
  If b(i, 1) = "" And i <> lr Then
    er = i + 1
  ElseIf b(i, 1) <> "" Then
    If er - sr > mc Then mc = er - sr
    sr = i
  ElseIf b(i, 1) = "" And i = lr Then
    er = lr + 1
    If er - sr > mc Then mc = er - sr
  End If
Next i
n = Application.Count(Range("B1:B" & lr))
ReDim o(1 To n, 1 To (mc * (lc - 1)))
For i = 1 To lr Step 1
  If b(i, 1) <> "" Then
    nc = 0
     j = j + 1
    For c = 1 To UBound(b, 2)
      nc = nc + 1
      o(j, nc) = b(i, c)
    Next c
  Else
    For c = 2 To UBound(b, 2)
      nc = nc + 1
      o(j, nc) = b(i, c)
    Next c
  End If
Next i
Range(Cells(1, 2), Cells(lr, lc)).ClearContents
Range("B1").Resize(UBound(o, 1), UBound(o, 2)) = o
Columns(2).Resize(, UBound(o, 2)).AutoFit
Application.ScreenUpdating = True
End Sub

DATASET[TABLE="class: grid, width: 901, align: center"]
<tbody>[TR]
[TD="width: 53"]
[/TD]
[TD="width: 53"]A[/TD]
[TD="width: 53"]B[/TD]
[TD="width: 53"]C[/TD]
[TD="width: 53"]D[/TD]
[TD="width: 53"]E[/TD]
[TD="width: 53"]F[/TD]
[TD="width: 53"]G[/TD]
[TD="width: 53"]H[/TD]
[TD="width: 53"]I[/TD]
[TD="width: 53"]J[/TD]
[TD="width: 53"]K[/TD]
[TD="width: 53"]L[/TD]
[TD="width: 53"]M[/TD]
[TD="width: 53"]N[/TD]
[TD="width: 53"]O[/TD]
[TD="width: 53"]P[/TD]
[TD="width: 53"]Q[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD]1[/TD]
[TD]12/10/2010[/TD]
[TD]46 a.[/TD]
[TD]F[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Ileo paralitico[/TD]
[TD]S[/TD]
[TD]VINCRISTINA PFIZER ITALIA[/TD]
[TD]10067070[/TD]
[TD]2 MG TOT.[/TD]
[TD="colspan: 2"]Da 04/10/2010 A 04/10/2010[/TD]
[TD]Vincristina[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]CICLOFOSFAMIDE[/TD]
[TD]10067070[/TD]
[TD][/TD]
[TD="colspan: 2"]Da 04/10/2010 A 04/10/2010[/TD]
[TD]Farmaci antineoplastici e immunomodulatori[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]DOXORUBICINA CLORIDRATO[/TD]
[TD]10067070[/TD]
[TD][/TD]
[TD="colspan: 2"]Da 04/10/2010 A 04/10/2010[/TD]
[TD]Farmaci antineoplastici e immunomodulatori[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]PREDNISONE[/TD]
[TD]10067070[/TD]
[TD][/TD]
[TD="colspan: 2"]Da 04/10/2010 A 08/10/2010[/TD]
[TD]Preparati ormonali sistemici, esclusi gli ormoni sessuali[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]14[/TD]
[TD]1[/TD]
[TD]16/11/2010[/TD]
[TD]77 a.[/TD]
[TD]M[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Follicolite, Eruzione cutanea[/TD]
[TD]S[/TD]
[TD]SUTENT, 30 CAPSULE "30 CAPSULE DA 25 MG"[/TD]
[TD]10007404[/TD]
[TD]50 MG CIC.[/TD]
[TD="colspan: 2"]Da 01/11/2010 A 16/11/2010[/TD]
[TD]SUNITINIB[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]159[/TD]
[TD]1[/TD]
[TD]30/12/2010[/TD]
[TD]61 a.[/TD]
[TD]F[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Dolore lombare, Nausea[/TD]
[TD]S[/TD]
[TD]TAXOTERE[/TD]
[TD]10006187[/TD]
[TD]125 MG CIC.[/TD]
[TD="colspan: 2"]Da 30/12/2010 A 30/12/2010[/TD]
[TD]Docetaxel[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]EUTIROX, "100 MICROGRAMMI COMPRESSE" 50 COMPRESSE[/TD]
[TD][/TD]
[TD]100 MG[/TD]
[TD][/TD]
[TD][/TD]
[TD]Levotiroxina sodica[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]INDERAL, "40 MG COMPRESSE" 30 COMPRESSE[/TD]
[TD][/TD]
[TD]40 MG[/TD]
[TD][/TD]
[TD][/TD]
[TD]Propranololo[/TD]
[/TR]
</tbody>[/TABLE]

 
Last edited:
Upvote 0
Re: FIRST ISSUE SOLVED - Re: Excel 2010 - Traspose lowest row to upper row

mauriziosessa,

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
Re: FIRST ISSUE SOLVED - Re: Excel 2010 - Traspose lowest row to upper row

JeeTD.jpg
Aadll.jpg
 
Upvote 0
Re: FIRST ISSUE SOLVED - Re: Excel 2010 - Traspose lowest row to upper row

Not Solved, i changed the dataset to make it simplest.

Please close the thread.
Thank you very much for your help :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Employee Sorting form to email.
Replies
2
Views
133
Justinjc79
J
A
  • Question Question
Excel Row
Replies
7
Views
221
Cubist
Cubist
R
  • Question Question
Send and email using an Outlook template
Replies
1
Views
665
Razor_Rob
R
M
  • Question Question
Excel entry slowed by Conditional Formatting
Replies
2
Views
54
moore315
M
P
  • Solved
CutList
Replies
3
Views
80
StephenCrump
S

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