Posted to microsoft.public.excel.worksheet.functions
|
|
Data manipulation help
Thanks Dave!
Will this work with varying numbers of image files paths present below the
DOB row?
Also, if the SSN row is NOT present, will this still work?
Thanks again,
Brian
"Dave Peterson" wrote in message
...
Is a macro ok?
If yes...
Try this against a copy of your worksheet--or save before you run this.
It
destroys the original data when it runs. So you'll want to be able to
close
without saving to get things back.
Option Explicit
Sub testme()
Dim myBigRng As Range
Dim mySmallArea As Range
Dim HowManyRows As Long
Dim rCtr As Long
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
With .Columns(1)
'no formulas!
.Value = .Value
'make the the "Begin:" cell empty
.Replace _
What:="Begin:*", _
Replacement:="", _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
Set myBigRng = Nothing
On Error Resume Next
Set myBigRng = .Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If myBigRng Is Nothing Then
MsgBox "Nothing found to convert!"
Exit Sub
End If
End With
For Each mySmallArea In myBigRng.Areas
With mySmallArea
HowManyRows = .Rows.Count - 3
If HowManyRows 0 Then
.Resize(3, 1).Copy
.Cells(1, 1).Offset(0, 1) _
.Resize(HowManyRows, 3).PasteSpecial
Transpose:=True
For rCtr = 4 To 4 + HowManyRows - 1
.Cells(rCtr - 3, 5).Value = .Cells(rCtr, 1).Value
Next rCtr
End If
End With
Next mySmallArea
.Columns(2).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete
.Columns(1).Delete
End With
End Sub
This expects that "Begin:" in column A for each group and 3 rows of
"headers".
If you're new to macros:
Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
Debra Dalgleish has some getstarted instructions for userforms at:
http://contextures.com/xlUserForm01.html
Brian wrote:
Howdy all,
I have employee records with corresponding image files list all in Column
A.
Each employee record is proceeded by the word BEGIN:, then SSN, NAME,
DOB,
and then a varying number of image file paths. There are thousands of
these,
and I want to format them like I've shown below.
Is this possible?
Here is what I have:
A
1 BEGIN:
2 SSN: 111-22-3333
3 NAME: Joe Blow
4 DOB: 01/01/60
5 \v\wst1.jpg
6 \v\wst2.jpg
7 \v\wst3.jpg
8 BEGIN:
9 SSN: 222-33-4444
10 NAME: Henry Ford
11 DOB 02/02/70
12 \v\qwe1.jpg
13 \v\qwe2.jpg
etc, etc.
What I want:
A B C
D
1 SSN: 111-22-3333 NAME: Joe Blow DOB: 01/01/60 \v\wst1.jpg
2 SSN: 111-22-3333 NAME: Joe Blow DOB: 01/01/60 \v\wst2.jpg
3 SSN: 111-22-3333 NAME: Joe Blow DOB: 01/01/60 \v\wst3.jpg
4 SSN: 222-33-4444 NAME: Henry Ford DOB 02/02/70 \v\qwe1.jpg
4 SSN: 222-33-4444 NAME: Henry Ford DOB 02/02/70 \v\qwe2.jpg
Thanks,
Brian
--
Dave Peterson
|