Help with file open and data organise macro
 

hi,
I have got some code from a couple of friends. It uses the file dialo
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 followin
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 abov
format style. I am inclduing the file open marco with test data files.
am also includuing my own data.txt file with my macro which currentl
organised the data the way I want. Does anybody else have any option
to edit this marco to make it work the way i want
Regards,
Fran

+-------------------------------------------------------------------
|Filename: Sample Code Files.zip
|Download: http://www.excelforum.com/attachment.php?postid=3598
+-------------------------------------------------------------------

--
frankcas
-----------------------------------------------------------------------
frankcase's Profile: http://www.excelforum.com/member.php...fo&userid=1951
View this thread: http://www.excelforum.com/showthread.php?threadid=38704

Help with file open and data organise macro
 

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