I recommend you learn VBA. Throughout my career I found myself accumulating data relevant to the task at hand. The ability to manipulate that data with VBA was a lifesaver on more than one occasion. VBA as a language is not particularly difficult to learn. The most difficult part for me was the Excel Object Model: Excel managing multiple workbooks each with multiple worksheets each with rows, columns, ranges and cells and each of them having properties. If you are an experienced Excel user, you may find you are already familiar with much of the Excel Object Model although you probably don’t know that name.
Search for “Excel VBA tutorial”. There are many to choose from so pick one that matching your learning style. I prefer books so I visited a good library, reviewed their Excel VBA Primers, and borrowed the ones I liked the best to try at home. Finally I bought the one I preferred as a permanent reference which I still look at from time-to-time. I found the time to learn VBA and the Excel Object Model quickly repaid itself.
You say “Yes, the sequence of datasets (i.e. Switch Name, Switch Type, LATA and Tandem) located as the 2nd through 5th row of each cell are always in specific manner.” I am sure you believe that but I don’t.
At one time during my working life I was involved in transforming and merging workbooks. We would get workbooks every week or month from various sources and create consolidated workbooks containing the data of interest to us in a format convenient for our data analysts to process. Each new source workbook were supposed to be the same format as its predecessor but time and again we would find an extra column or an extra row type or an obsolete column or row type removed. If the consolidate macro simply assumed the data was correct and merged it blindly, it might work but create a corrupt workbook. Fortunately not on my watch, there was one case where it was some months before a minor change was noticed. It was a massive amount of work to locate the last uncorrupted, consolidated workbook and all the source workbooks and then build a new workbook. I am probably paranoid but I check every dataset I did not create.
My macro checks every row of your input worksheet to be one of the specified formats and will stop if a row is not as expected. The macro cannot know how to fix the problem but at least the user is warned that a problem exists. I recommend this approach which Microsoft calls “defensive programming”.
I think everything else I want to say is within the macro.
Create a new, macro-enabled version of your workbook, add the code below and try it. Be ready to report any problems.
Come back with questions as necessary.
Option Explicit
Sub MoveSubLinesToMain()
' I do not know from your question if this is a one-off tranformation or if you will need
' to use the macro repeatedly as new worksheets in the initial format are created by some
' other process. For a one-off macro, brief documentation may be acceptable. But any macro
' that is used repeatedly will also most certainly need updating. Trying to decipher an
' inadequately documented macro that you wrote six months ago or which some one else wrote
' is a nightmare.
' Do not run this macro against the master copy of the data since it transforms the data in
' situ. The macro is designed to carry on following an error but you must have a master copy
' so you can start again if the macro cannot carry on after an error.
' This macro updates worksheet "Data". If you worksheet has a different name, change
' the statement:
' With Worksheets("Data").
' Ths macro expects to find:
' * Row 1: Header row which is ignored
' * Row 2: First data row. If there are more header rows change the statement:
' Const ColRowDataFirst As Long = 2
' * The first data row must be what is named here as a main row. That is a row starting with
' an NPA-NXX number. A main row is recognised by the first character of the NPA-NXX column
' being numeric.
' * A main row may be followed by several row which are named here as a sub rows. The macro
' allows for there being no sub rows so the macro can be restarted on a partially processed
' worksheet.
' * The sub rows are recognised by their leading characters:
' "Switch Name: "
' "Switch Type: "
' "LATA: "
' "Tandem: "
' * There may also be blank lines which are ignored.
' * If a sub row is encountered that does not match one of those listed above, the macro will
' stop to allow an examination of the error situation and, when restarted, will terminate
' itself. You will have to decide how to update the macro to handle the error situation.
' Once the macro has been updated, it should be possible to restart the macro which will
' step over the already processed rows and continue with the unprocessed row. If this fails
' you will have to overwrite the partially processed worksheet with the master copy of the
' original data.
' * The block, main row and zero or more sub rows, may be repeated an indefinite number of
' times.
' * For each block, the macro copies the values from the sub rows to specified columns within
' their main row and then the sub rows.
' The statements to access a cell need a row and column number. You can use literals but with
' larger number of columns or special rows it can all become very confusing. A const (constant)
' statement allows you to define a name to replace the literal which makes your code more
' readable. More importantly, what happens if a new sub row is introduced and the Lata and
' Tandem columns are to be moved. This is a tiny macro and finding all the 11s and 12s which are
' column numbers and replacing them will not be be too difficult. This is not true of a large
' macro. But updating the const statements defining ColLata and ColTandem updates every
' reference to these columns through the module.
Const ColNpa As Long = 1
Const ColName As Long = 9
Const ColType As Long = 10
Const ColLata As Long = 11
Const ColTandem As Long = 12
Const RowDataFirst As Long = 2
Dim NpaValue As String
Dim NumRowsToDelete As Long
Dim RowCrnt As Long
Dim RowCrntMain As Long
Dim RowLast As Long
' Without this statement, the screen is repainted for every change. Since I am deleting
' rows this will substantially increase the run time for no advantage.
Application.ScreenUpdating = False
' As stated above replace "Data" with the name of your worksheet.
With Worksheets("Data")
' This is the easiest way of locating the last row with data if you know that column ColNpa
' will have a value on every row.
RowLast = .Cells(Rows.Count, ColNpa).End(xlUp).Row
RowCrnt = RowDataFirst
RowCrntMain = 0 ' No current main row
' I would normally use a For Loop: For RowCrnt = RowDataFirst To RowLast
' But I am deleting rows which will require RowCrnt and RowLast to be
' changed within the loop. This is not permitted for a For Loop
Do While RowCrnt <= RowLast
NpaValue = .Cells(RowCrnt, ColNpa).Value
' This If..IfElse...IfElse statements tests for each known row type
' ans actions them as appropiate. The final Else allows for an
' unknown row type.
If NpaValue = "" Then
' Blank line
ElseIf IsNumeric(Left$(NpaValue, 1)) Then
' Main row
If RowCrntMain <> 0 Then
' There is a previous main row whose sub rows must be deleted
NumRowsToDelete = RowCrnt - RowCrntMain - 1
If NumRowsToDelete > 0 Then
.Rows(RowCrntMain + 1 & ":" & RowCrnt - 1).Delete
RowCrnt = RowCrnt - NumRowsToDelete
RowLast = RowLast - NumRowsToDelete
End If
End If
RowCrntMain = RowCrnt
ElseIf Left$(NpaValue, 13) = "Switch Name: " Then
' Copy the value of the Switch Name row to column ColName on the main row.
' Do the same for all the other sub rows.
.Cells(RowCrntMain, ColName).Value = Trim(Mid$(NpaValue, 14))
ElseIf Left$(NpaValue, 13) = "Switch Type: " Then
.Cells(RowCrntMain, ColType).Value = Trim(Mid$(NpaValue, 14))
ElseIf Left$(NpaValue, 6) = "LATA: " Then
.Cells(RowCrntMain, ColLata).Value = Trim(Mid$(NpaValue, 7))
ElseIf Left$(NpaValue, 8) = "Tandem: " Then
.Cells(RowCrntMain, ColTandem).Value = Trim(Mid$(NpaValue, 9))
Else
' Row not recognised
' If code stops here try to identify why. Terminate the macro
' or press F5 and it will terminate itself.
Debug.Assert False
Exit Sub
End If
RowCrnt = RowCrnt + 1
Loop
' Delete final block of sub-lines, if any
If RowCrntMain <> 0 Then
' There is a previous main row whose sub rows must be deleted
NumRowsToDelete = RowCrnt - RowCrntMain - 1
If NumRowsToDelete > 0 Then
.Rows(RowCrntMain + 1 & ":" & RowCrnt - 1).Delete
End If
End If
End With
End Sub
