PDA

View Full Version : Sleeper: Shuffling Data onto a new Worksheet



timculberson
05-20-2005, 11:00 AM
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
05-20-2005, 11:25 AM
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
05-20-2005, 12:23 PM
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
05-20-2005, 01:23 PM
[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
05-23-2005, 05:12 AM
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
05-23-2005, 05:38 AM
[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
05-23-2005, 07:13 AM
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?