Sleeper: Shuffling Data onto a new Worksheet

[timculberson]

First, I must admit I am an extreme newbie to VBA. However, I need a solution that I think is probably simple, but I can't seem to find a similar problem despite a few hours of research in the kb and searching the forum.

That said, here is my situation:

I have a pretty large text file that I've imported into two columns, with data that is essentially listed in repeating sets that I need to re-organize into a proper workable format.

Basically, I have this:


1 Heading 1 | Value a
2 Heading 2 | Value b
3 Heading 3 | Value c
4 Heading 1 | value x
5 Heading 2 | value y
6 Heading 3 | value z

etc...

And what I need is this:


1 Heading 1 | Heading 2 | Heading 3
2 value a | value b | value c
3 value x | value y | value z

Any help on a starting point is highly appreciated!

Thanks
~Tim

[Bob Phillips]

I have a pretty large text file that I've imported into two columns, with data that is essentially listed in repeating sets that I need to re-organize into a proper workable format.

Here is a macro that does it

Sub Test()
Dim iLastRow As Long
Dim iLastCol As Long
Dim iRow As Long
Dim iCol
Dim i As Long
Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1").Copy Range("C1")
    Range("B1").Copy Range("C2")
    For i = 2 To iLastRow
        iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        iCol = Application.Match(Cells(i, "A").Value, Range("C1", Cells(1, iLastCol)), 0)
        If IsError(iCol) Then
            Cells(i, "A").Copy Cells(1, iLastCol + 1)
            Cells(i, "B").Copy Cells(2, iLastCol + 1)
        Else
            iRow = Cells(Rows.Count, 2 + iCol).End(xlUp).Row + 1
            Cells(i, "B").Copy Cells(iRow, 2 + iCol)
        End If
    Next i
Columns("A:B").Delete
Application.ScreenUpdating = True
End Sub

[timculberson]

Wow...thanks for the quick reply. This code would have worked perfectly if my repeating sets had been consistent as I thought.

Instead, each "record" contains potentially different column headings.

Every set does start with the heading "type" and "tagname", but the headings listed below that are different, depending on what the value of the "type" field is.

For example, the data sets are something like this:

type | value
tagname | value
heading1 | value
heading2 | value
heading3| value
type | value
tagname | value
heading1 | value
heading4 | value
heading6 | value

So, in other words, each set of data always starts with the "type" field, but may contain different headings, depending on the type. It would be perfectly acceptable to include all possible headings across the top (which your code did), and simply populate whichever columns are appropriate for each individual record.

Does this throw a big curve ball into the scenario?

~Tim

[Bob Phillips]

[QUOTE=timculberson]Does this throw a big curve ball into the scenario?[QUOTE]

Might do, but I don't understand it now.

How should this new data transcribe?

[timculberson]

Sorry to take so long getting back...

essentially, the way I need the data to transpose, is for every time the heading "type" is found in column A, I need to take the matching data in column B and start a new row of data. Each piece of Data in column B after that point should be placed in a new adjacent column, until the heading "type" is once again seen in column A, at which point a new row of data should be started. I can worry about getting the final column headings in place later.

Does this make sense? Would it help if I made the excel file available for download?

[Bob Phillips]

[QUOTE=timculberson]essentially, the way I need the data to transpose, is for every time the heading "type" is found in column A, I need to take the matching data in column B and start a new row of data. Each piece of Data in column B after that point should be placed in a new adjacent column, until the heading "type" is once again seen in column A, at which point a new row of data should be started. I can worry about getting the final column headings in place later.
QUOTE]

Can you expand that data a bit, maybe like

type | value
tagname | value
Heading 1 | Value a
Heading 2 | Value b
Heading 3 | Value c
Heading 1 | value x
type | value
tagname | value
Heading 1 | value x
Heading 2 | value y
Heading 3 | value z

etc, and show what the result should look like

[timculberson]

Here is what the data is:

1 | A
2 | B
3 | C
1 | D
2 | E
3 | F
1 | G
2 | H
3 | I
1 | J
2 | K
3 | L
4 | M
1 | N
2 | O
3 | P

And it should look like:

1 | 2 | 3 | 4
A | B | C |
D | E | F |
G | H | I |
J | K | L | M
N | O | P|

So, in other words, each repeating block of data always starts with column A as a 1, but may include additional headings of 2, 3, sometimes 4, 5, 6, etc.

Is there a better way I can describe this?