Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Manipulation. | Excel Discussion (Misc queries) | |||
Help with data manipulation | Excel Discussion (Misc queries) | |||
Need help with some data manipulation | Excel Worksheet Functions | |||
Data manipulation within the same sheet | Setting up and Configuration of Excel | |||
Data manipulation | Excel Discussion (Misc queries) |