Help needed to transpose large amount of data

TNNeto

New Member
Joined
Mar 24, 2011
Messages
2
Hello all

I'm quite sorry to introduce myself by asking for help right away, but I am quite stuck with a problem at the moment.

I have to analyze some data wich is extractable from a database only by PDF lists (a genius must have been at work when he drew the specs for it).
I have three PDF lists to extract data from in order to make them workable on a PC, and to select data using filters. I have been able to extract them onto Excel with quite some hassle, but the data is coherent.

I ended up with a data structure such as this (field names in Portuguese, sorry), with these 3 fields in collumn A, respective data in collumn B

Local actividade
Morada
Localidade


The mess is what follows. The next two rows are in "proper" Excel form, with field names distributed in a row and the data for those fields in the following row, such as this:
COLLUMN A COLLUMN B ... AND SO ON
ROW 4 Licenca Autorização
ROW 5 PT100003 Transportador

Which goes on until row H, with only rows A, B and D always containing data, which is not evenly uniformely distributed

For every block of 5 rows X 8 collumns, the relevant data is located at A1:B5 and D3:D5.

This 5 row structure is repeated ad nauseum throughout the whole mess of a file. One spreadsheet is 7 pages long (320 rows) the other two are 84 pages long (ZOMG)

To add insult to injury not all the cells with a "field name" have data, and quite a few have data with no identifying field name, but always in the position cell references I've mentioned.

In short, how can I transpose every set of 3 rows X 2 collumns, then have it immediately followed in what would become collumn D (the 4th) by the data in every fifth row's without transposition?

By analysis of each block of data, here's where I'd like every cell in every block to go (rows 1:5 as example)

A1 -> A1
B1 -> A2
A2 -> B1
B2 -> B2
A3 -> C1
B3 -> C2

A4 -> D1
A5 -> D2
B4 -> E1
B5 -> E2
C4 -> F1
C5 -> F2
D4 -> G1
D5 -> G2
F4 -> H1
F5 -> H2
G4 -> I1
G5 -> I2


It would also be nice if the data in D3:F3 would be concatenated to the end of the contents of the data in A2 before it is sent to cell B1

I hope I made myself kind of clear, it is quite a mess.

Many thanks!

- Tiago
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi and welcome...

Code:
Sub T_Poser()

    Dim Lastrow As Long, r As Long, vTemp As Variant
    
    ActiveSheet.Copy After:=ActiveSheet
    
    Lastrow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For r = 1 To Lastrow Step 5
        With Range("A" & r)
        
            ' Swap A2 and B1
            vTemp = .Range("A2").Value
            .Range("A2").Value = .Range("B1").Value
            .Range("B1").Value = vTemp

            ' Transpose C1:C2 to A3:B3
            .Range("A3").Value = .Range("C1").Value
            .Range("B3").Value = .Range("C2").Value
            .Range("C1:C2").ClearContents
            
            ' Move A4:D5 to D1:G2
            .Range("D1:G2").Value = .Range("A4:D5").Value
            .Range("A4:D5").ClearContents
            ' Move F4:G5 to H1:I2
            .Range("H1:I2").Value = .Range("F4:G5").Value
            .Range("F4:G5").ClearContents
            
            ' Concatenate D3:F3 to the end of B1
            .Range("B1").Value = Trim(.Range("B1").Value) & " " & .Range("D3").Value
            .Range("B1").Value = Trim(.Range("B1").Value) & " " & .Range("E3").Value
            .Range("B1").Value = Trim(.Range("B1").Value) & " " & .Range("F3").Value
            .Range("D3:F3").ClearContents
            
        End With
    Next r
    
    ' Delete Blank rows?
'    Range("A1:A" & Lastrow).AutoFilter Field:=1, Criteria1:="="
'    Range("A2:A" & Lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'    ActiveSheet.AutoFilterMode = False
    
    Application.ScreenUpdating = True
    
End Sub
 
Last edited:
Upvote 0
You Sir, are my new personal hero - it worked flawlessly.

I must definitively learn how to do such wizardry in Excel :biggrin::biggrin::biggrin::biggrin:

My most heartfelt thanks!

- Tiago
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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