Help needed to transpose large amount of data

[TNNeto]

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

 

[AlphaFrog]

Hi and welcome...

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

 

[TNNeto]

You Sir, are my new personal hero - it worked flawlessly.

I must definitively learn how to do such wizardry in Excel

My most heartfelt thanks!

- Tiago