ExcelBanter - Data manipulation help

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Data manipulation help (https://www.excelbanter.com/excel-worksheet-functions/230644-data-manipulation-help.html)

Brian

Data manipulation help

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

Data manipulation help

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

Brian

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

Dave Peterson

Data manipulation help

The lack of the SSN row will blow it to bits.

It expects 3 lines of headers for each group.

Brian wrote:

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

--

Dave Peterson

Dave Peterson

Data manipulation help

So what does your data really look like?

Does it really have those prefixes in the beginning of each of the header cells:

SSN:
NAME:
DOB:

Any prefix for the other rows (the image file strings?)

Is the SSN the only header that could be missing?

It may be easier to just insert that if it wasn't found, then run the other
code.

You can run this routine before you run the original if SSN is the only one that
could be missing.

Sub RunMeFirst()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = ActiveSheet

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) = LCase("Begin:") Then
If LCase(Cells(iRow + 1, "A").Value) Like LCase("SSN:*") Then
'this group is ok
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = "SSN:"
End If
End If
Next iRow
End With
End Sub

==========
If you wanted to just run a single macro, you can have the original call the
first:

Dim wks As Worksheet

Call RunMeFirst '<-- added

Set wks = ActiveSheet

With wks
With .Columns(1)
'no formulas!
.Value = .Value

Brian wrote:

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

--

Dave Peterson

Brian

Data manipulation help

Dave,

Yes, it appears that occasionally the SSN line is not present.

So this macro would insert SSN if it wasn't present and ignore the header
section if it is present?

Once I figure out how to run a macro, I'll post back.

Thanks,
Brian

"Dave Peterson" wrote in message
...
So what does your data really look like?

Does it really have those prefixes in the beginning of each of the header
cells:

SSN:
NAME:
DOB:

Any prefix for the other rows (the image file strings?)

Is the SSN the only header that could be missing?

It may be easier to just insert that if it wasn't found, then run the
other
code.

You can run this routine before you run the original if SSN is the only
one that
could be missing.

Sub RunMeFirst()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = ActiveSheet

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) = LCase("Begin:") Then
If LCase(Cells(iRow + 1, "A").Value) Like LCase("SSN:*")
Then
'this group is ok
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = "SSN:"
End If
End If
Next iRow
End With
End Sub

==========
If you wanted to just run a single macro, you can have the original call
the
first:

Dim wks As Worksheet

Call RunMeFirst '<-- added

Set wks = ActiveSheet

With wks
With .Columns(1)
'no formulas!
.Value = .Value

Brian wrote:

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

--

Dave Peterson

Dave Peterson

Data manipulation help

Yep.

And read a few of those links included in the original suggestion.

Brian wrote:

Dave,

Yes, it appears that occasionally the SSN line is not present.

So this macro would insert SSN if it wasn't present and ignore the header
section if it is present?

Once I figure out how to run a macro, I'll post back.

Thanks,
Brian

"Dave Peterson" wrote in message
...
So what does your data really look like?

Does it really have those prefixes in the beginning of each of the header
cells:

SSN:
NAME:
DOB:

Any prefix for the other rows (the image file strings?)

Is the SSN the only header that could be missing?

It may be easier to just insert that if it wasn't found, then run the
other
code.

You can run this routine before you run the original if SSN is the only
one that
could be missing.

Sub RunMeFirst()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = ActiveSheet

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) = LCase("Begin:") Then
If LCase(Cells(iRow + 1, "A").Value) Like LCase("SSN:*")
Then
'this group is ok
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = "SSN:"
End If
End If
Next iRow
End With
End Sub

==========
If you wanted to just run a single macro, you can have the original call
the
first:

Dim wks As Worksheet

Call RunMeFirst '<-- added

Set wks = ActiveSheet

With wks
With .Columns(1)
'no formulas!
.Value = .Value

Brian wrote:

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

--

Dave Peterson

--

Dave Peterson

Brian

Data manipulation help

Dave,

I ran both macros on a sample of the data and it worked like a charm!!!
Thanks a million!

Brian

"Dave Peterson" wrote in message
...
Yep.

And read a few of those links included in the original suggestion.

Brian wrote:

Dave,

Yes, it appears that occasionally the SSN line is not present.

So this macro would insert SSN if it wasn't present and ignore the header
section if it is present?

Once I figure out how to run a macro, I'll post back.

Thanks,
Brian

"Dave Peterson" wrote in message
...
So what does your data really look like?

Does it really have those prefixes in the beginning of each of the
header
cells:

SSN:
NAME:
DOB:

Any prefix for the other rows (the image file strings?)

Is the SSN the only header that could be missing?

It may be easier to just insert that if it wasn't found, then run the
other
code.

You can run this routine before you run the original if SSN is the only
one that
could be missing.

Sub RunMeFirst()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = ActiveSheet

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) = LCase("Begin:") Then
If LCase(Cells(iRow + 1, "A").Value) Like LCase("SSN:*")
Then
'this group is ok
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = "SSN:"
End If
End If
Next iRow
End With
End Sub

==========
If you wanted to just run a single macro, you can have the original
call
the
first:

Dim wks As Worksheet

Call RunMeFirst '<-- added

Set wks = ActiveSheet

With wks
With .Columns(1)
'no formulas!
.Value = .Value

Brian wrote:

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

--

Dave Peterson

--

Dave Peterson

Dave Peterson

Data manipulation help

Glad you got it working.

Brian wrote:

Dave,

I ran both macros on a sample of the data and it worked like a charm!!!
Thanks a million!

Brian

"Dave Peterson" wrote in message
...
Yep.

And read a few of those links included in the original suggestion.

Brian wrote:

Dave,

Yes, it appears that occasionally the SSN line is not present.

So this macro would insert SSN if it wasn't present and ignore the header
section if it is present?

Once I figure out how to run a macro, I'll post back.

Thanks,
Brian

"Dave Peterson" wrote in message
...
So what does your data really look like?

Does it really have those prefixes in the beginning of each of the
header
cells:

SSN:
NAME:
DOB:

Any prefix for the other rows (the image file strings?)

Is the SSN the only header that could be missing?

It may be easier to just insert that if it wasn't found, then run the
other
code.

You can run this routine before you run the original if SSN is the only
one that
could be missing.

Sub RunMeFirst()
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = ActiveSheet

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) = LCase("Begin:") Then
If LCase(Cells(iRow + 1, "A").Value) Like LCase("SSN:*")
Then
'this group is ok
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = "SSN:"
End If
End If
Next iRow
End With
End Sub

==========
If you wanted to just run a single macro, you can have the original
call
the
first:

Dim wks As Worksheet

Call RunMeFirst '<-- added

Set wks = ActiveSheet

With wks
With .Columns(1)
'no formulas!
.Value = .Value

Brian wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Brian

Data manipulation help

Dave,

You seem to be the Excel VBA guru.
I posted another VBA question, and was wondering if you could take a look?

Here's the link:

http://groups.google.com/group/micro...cd38be98?hl=en

Thanks,
Brian

"Brian" wrote in message
...
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

All times are GMT +1. The time now is 12:12 PM.