How can I get multiple columns of data formatted into a single row? (simple macro?)

  • Thread starter Thread starter LordJezo
  • Start date Start date
L

LordJezo

Say I want to get something that looks like this:

/webtrends/production/wtm_wtx/datfiles/databases/148
LastAnalysisTime = Mon Mar 8 09:14:36 2004
Duration = 543
/webtrends/production/wtm_wtx/datfiles/databases/163
LastAnalysisTime = Mon Mar 8 22:16:56 2004
Duration = 1016
/webtrends/production/wtm_wtx/datfiles/databases/164
LastAnalysisTime = Tue Mar 9 00:29:35 2004
Duration = 874
/webtrends/production/wtm_wtx/datfiles/databases/165
LastAnalysisTime = Tue Mar 9 00:35:56 2004
Duration = 1255

To look like this:

148 | 543
163 | 1016
164 | 874
165 | 1255

(Where | signifies the end of a cell)

I know the first part would be a simple search and replace to eliminat
the extra words and numbers, but how would I shift up the duration t
be next to the number from the directory path
 
Hi!

I once splashed out on an add-in which saves me hours. It's called Th
Spreadsheet Assistant from '' (http://www.add-ins.com)
One thing it provides is a quick means of selecting every nth row.

But VBA would do it cheaper.

Al
 
Hi

Is ' /webtrends . . . . . =543 ' all in a single cell, or is it split into
3 rows?

Andy.
 
If I have understood you correctly eac
"/webtrends/production/wtm_wtx/datfiles/databases/148
LastAnalysisTime = Mon Mar 8 09:14:36 2004
Duration = 543" is curerntly one cell so...

As you say find & replace tex
"/webtrends/production/wtm_wtx/datfiles/databases/" to leave:

148LastAnalysisTime = Mon Mar 8 09:14:36 2004
Duration = 543

Then Find and replace "
LastAnalysisTime = Mon Mar 8 09:14:36 2004
Duration"

This last "=" is imprtant as you can then use the Text to Column
function in the tools menu and specify the data as "Delimited" the
specify delimiter type as other and put "=" in the box. This wil
convert "148= 543" to 148|543 for the range you select.

Dunca
 
Close..

The cell layout would be (for example):

A1:/webtrends/production/wtm_wtx/datfiles/databases/148
A2:LastAnalysisTime = Mon Mar 8 09:14:36 2004
A3:Duration = 543

Each row is it's own cell.. so each line is the next cell down.
 
Better late then never?

If you need to get rid of the blanks you can either sort the data o
use the attached add-in if that is inappropriate. I have assumed th
data is all in one column- This will need to be selected before runnin
the macro.

Duncan


Sub SortData()

'Check correct range has been selected (ie the list of data)
If MsgBox("Has the range to check been selected?", vbYesNo) = vbN
Then End

'Do Find/Replace
Dim DataRange As Range
Set DataRange = Selection

With DataRange
.Replac
What:="/webtrends/production/wtm_wtx/datfiles/databases/", _
Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns
MatchCase:= _
False
.Replace What:="Duration =", Replacement:="", LookAt:=xlPart
_
SearchOrder:=xlByColumns, MatchCase:=False
End With

Dim CurrentCell As Range
Dim PasteCell As Range

'Cycle through selected range and when a cell starting "Last" is foun
cut the cell
'below and paste in the cell one up and one right from there.
For Each CurrentCell In DataRange
If Left(CurrentCell.Value, 4) = "Last" Then
Set PasteCell = CurrentCell.Offset(-1, 1)
CurrentCell.Offset(1, 0).Cut
ActiveSheet.Paste Destination:=PasteCell
CurrentCell.ClearContents
End If
Next

End Su

Attachment filename: remove dups & original.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=47360
 
That macro you worked worked great! I am one step closer to reaching my
goal of automating this data entry so I can cut down the work of manual
data entry by a whole lot.

That addin that you attached.. it is supposed to get rid of the blank
entries? I tried running it (I think I ran it. A little box with your
name popped up) after the data was sorted to get rid of the few banks
between each entry but it didn't do anything. Am I doing something
wrong?
 
That macro you worked worked great! I am one step closer to reaching my
goal of automating this data entry so I can cut down the work of manual
data entry by a whole lot.

That addin that you attached.. it is supposed to get rid of the blank
entries? I tried running it (I think I ran it. A little box with your
name popped up) after the data was sorted to get rid of the few banks
between each entry but it didn't do anything. Am I doing something
wrong?
 
I supposed im stating the obvious but have you tried =right(A1,3) the
copy pastespecial the results
 
Back
Top