Help with file open and data organise macro

hi,
I have got some code from a couple of friends. It uses the file dialog box to open separate text files into 1 excel in multiple worksheet. THis is great.

My data is in the following format in the file

A 1
B 12
C 128
D 1233
A 1
B 2
C 18
D 133

I need the data in the worksheet to be arranged in the following format:


A B C D
1 12 128 1233
1 2 18 133

The data files are a couple of columns and the data names repeat alot.
I would love to parse the data directly into my worksheets in the above format style. I am inclduing the file open marco with test data files. I am also includuing my own data.txt file with my macro which currently organised the data the way I want. Does anybody else have any options to edit this marco to make it work the way i want
Regards,
Frank

Something like this might work for you:

Sub MoveData()

Dim Rng As Range
Dim Cell As Range
Dim fndCell As Range
Dim endRow As Long
Dim endCol As Integer
Dim thsSht As Worksheet
Dim newSht As Worksheet
Dim Hdr As String
Dim Dta As String

Set thsSht = ActiveSheet
With thsSht
endRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range(.Cells(1, 1), .Cells(endRow, 1))
End With
Set newSht = Sheets.Add
For Each Cell In Rng
Hdr = Cell.Value
Dta = Cell.Offset(0, 1).Value
If newSht.Range("A1").Value = Empty Then
newSht.Cells(1, 1).Value = Hdr
newSht.Cells(2, 1).Value = Dta
Else
Set fndCell = newSht.Rows("1:1") _
.Find(Hdr, LookIn:=xlValues)
If Not fndCell Is Nothing Then
endRow = newSht.Cells _
(Rows.Count, fndCell.Column).End(xlUp).Row + 1
newSht.Cells(endRow, fndCell.Column).Value = Dta
Else
endCol = newSht.Cells(1, Columns.Count) _
.End(xlToLeft).Column + 1
newSht.Cells(1, endCol).Value = Hdr
newSht.Cells(2, endCol).Value = Dta
End If
End If
Next Cell

End Sub

Hope this helps
Rowan

"frankcase" wrote:

>
> hi,
> I have got some code from a couple of friends. It uses the file dialog
> box to open separate text files into 1 excel in multiple worksheet.
> THis is great.
>
> My data is in the following format in the file
>
> A 1
> B 12
> C 128
> D 1233
> A 1
> B 2
> C 18
> D 133
>
> I need the data in the worksheet to be arranged in the following
> format:
>
>
> A B C D
> 1 12 128 1233
> 1 2 18 133
>
> The data files are a couple of columns and the data names repeat alot.
> I would love to parse the data directly into my worksheets in the above
> format style. I am inclduing the file open marco with test data files. I
> am also includuing my own data.txt file with my macro which currently
> organised the data the way I want. Does anybody else have any options
> to edit this marco to make it work the way i want
> Regards,
> Frank
>
>
> +-------------------------------------------------------------------+
> |Filename: Sample Code Files.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=3598 |
> +-------------------------------------------------------------------+
>
> --
> frankcase
> ------------------------------------------------------------------------
> frankcase's Profile: http://www.excelforum.com/member.php...o&userid=19517
> View this thread: http://www.excelforum.com/showthread...hreadid=387049
>
>