Re-formating data in tables. (excel 2000)

Topic

New Reply

Hello all,

I am trying to create a macro that will allow me to re-order a table thats in row form into a table in column form so that I can create another macro that will allow me to mismatch any data to other data within the whole table. See attachment for a better sense of what I’m trying to do.

Thanks in advance – LaMont

Reply | Quote

Replies

LaMont

OK I could not open the workbook for some reason. I downloaded it 3 times, and I could not open any of them.

But if you mean Transpose when you say: “re-order a table thats in row form into a table in column form” well check the PasteSpecial Transpose = True in the Online help.

Also you may want to check the workbook you uploaded, I am using XL 2003 and it will not open. Maybe its on my end, I will check.

Wassim

Reply | Quote

Don’t know why it will not open. Does anyone else have this problem?

Reply | Quote

I opened it fine.

Steve

Reply | Quote

I opened it fine.

Steve

Reply | Quote

LaMont

Got it open, finally!!! Don’t ask what I did, but it worked.

OK the transpose will help you. Here is what I recorded for you:

Sub TransposeMyData()
Range(“A27:C31”).Copy
Sheets(“Sheet2”).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Cells.EntireColumn.AutoFit
End Sub

Now this is based on the Range that I could see, what is going to happen is that its going to out the output in this fashion

Contingency ‘1-ADAMS-120 to BUNCE-120 1’
Open branch from bus 336 to bus 373 ckt 1
Open branch from bus 353 to bus 373 ckt 1

Then you need to test where it ends, the first one in the first column, and place your end.

Then move over to the second column and cut and paste the contents and place them in Column A

To test how many columns you have you can do Range.Columns.Count or Range(“A1”).end(xlToRight).column.

Hope this helps.

Wassim

Reply | Quote

LaMont

Got it open, finally!!! Don’t ask what I did, but it worked.

OK the transpose will help you. Here is what I recorded for you:

Sub TransposeMyData()
Range(“A27:C31”).Copy
Sheets(“Sheet2”).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Cells.EntireColumn.AutoFit
End Sub

Now this is based on the Range that I could see, what is going to happen is that its going to out the output in this fashion

Contingency ‘1-ADAMS-120 to BUNCE-120 1’
Open branch from bus 336 to bus 373 ckt 1
Open branch from bus 353 to bus 373 ckt 1

Then you need to test where it ends, the first one in the first column, and place your end.

Then move over to the second column and cut and paste the contents and place them in Column A

To test how many columns you have you can do Range.Columns.Count or Range(“A1”).end(xlToRight).column.

Hope this helps.

Wassim

Reply | Quote

Don’t know why it will not open. Does anyone else have this problem?

Reply | Quote

LaMont

OK I could not open the workbook for some reason. I downloaded it 3 times, and I could not open any of them.

But if you mean Transpose when you say: “re-order a table thats in row form into a table in column form” well check the PasteSpecial Transpose = True in the Online help.

Also you may want to check the workbook you uploaded, I am using XL 2003 and it will not open. Maybe its on my end, I will check.

Wassim

Reply | Quote

Try this code. Change the input, output sheet as appropriate and the starting ranges for the input cell and output cell.
I did not “trim” the data (except to check for END) and I also used a ucase incase it is not always lowercase. though you could trim it if desired.

I don’t understand your “implied question” for the next stage of the macro or even what the “goal” of the project is, so I won’t comment on anything further.

Steve

Option Explicit
Sub Frogmand()
    Dim wksInput As Worksheet
    Dim wksOutput As Worksheet
    Dim rngStartIn As Range
    Dim rngStartOut As Range
    Dim lOffset As Long
    Dim sEnd As String
    Dim lRowOffset As Long
    Dim iColOffset As Integer
    
'Change these as appropriate
    sEnd = "END"
    Set wksInput = Worksheets("Sheet1")
    Set rngStartIn = wksInput.Range("A6")
    Set wksOutput = Worksheets("Sheet2")
    Set rngStartOut = wksOutput.Range("A1")
    
    wksOutput.Cells.ClearContents
    lOffset = 0
    lRowOffset = 0
    Do While rngStartIn.Offset(lOffset, 0)  ""
        iColOffset = 0
        Do While UCase(Trim(rngStartIn.Offset(lOffset, 0)))  sEnd
           rngStartOut.Offset(lRowOffset, iColOffset) = _
                rngStartIn.Offset(lOffset, 0)
            iColOffset = iColOffset + 1
            lOffset = lOffset + 1
        Loop
        lOffset = lOffset + 1
        lRowOffset = lRowOffset + 1
    Loop
End Sub

Reply | Quote

Thanks Steve, that works perfectly. All that I am trying to do with the otner macro is have an ability to combine the contingency data fields (the open branch data that was put in its own column by the first macro) with other contingency data fields. So all I would have to do is select mulitple contigencies from a list of the contigencies (probably from a validation list) and have the macro put the the selected contingency’s data back into its previous form (the way they were set before the first macro), but in its combined form. The way this data is formed (the form of it before the first macro and after the second being the same) serve the purpose of being a programming language for an engineering tool I use. I’m just trying to manipulate or change the data around easily using macros rather than programming it word for word. I’ll try to do what I can with the second macro and I’ll probably be on here again concerning how I can do some things with that macro to accomplish what I’m trying to accomplish, but if you or anyone else understand what I am trying to do please feel free to give me any suggetions.

Much thanks in advance, LaMont

Reply | Quote

Thanks Steve, that works perfectly. All that I am trying to do with the otner macro is have an ability to combine the contingency data fields (the open branch data that was put in its own column by the first macro) with other contingency data fields. So all I would have to do is select mulitple contigencies from a list of the contigencies (probably from a validation list) and have the macro put the the selected contingency’s data back into its previous form (the way they were set before the first macro), but in its combined form. The way this data is formed (the form of it before the first macro and after the second being the same) serve the purpose of being a programming language for an engineering tool I use. I’m just trying to manipulate or change the data around easily using macros rather than programming it word for word. I’ll try to do what I can with the second macro and I’ll probably be on here again concerning how I can do some things with that macro to accomplish what I’m trying to accomplish, but if you or anyone else understand what I am trying to do please feel free to give me any suggetions.

Much thanks in advance, LaMont

Reply | Quote

Try this code. Change the input, output sheet as appropriate and the starting ranges for the input cell and output cell.
I did not “trim” the data (except to check for END) and I also used a ucase incase it is not always lowercase. though you could trim it if desired.

I don’t understand your “implied question” for the next stage of the macro or even what the “goal” of the project is, so I won’t comment on anything further.

Steve

Option Explicit
Sub Frogmand()
    Dim wksInput As Worksheet
    Dim wksOutput As Worksheet
    Dim rngStartIn As Range
    Dim rngStartOut As Range
    Dim lOffset As Long
    Dim sEnd As String
    Dim lRowOffset As Long
    Dim iColOffset As Integer
    
'Change these as appropriate
    sEnd = "END"
    Set wksInput = Worksheets("Sheet1")
    Set rngStartIn = wksInput.Range("A6")
    Set wksOutput = Worksheets("Sheet2")
    Set rngStartOut = wksOutput.Range("A1")
    
    wksOutput.Cells.ClearContents
    lOffset = 0
    lRowOffset = 0
    Do While rngStartIn.Offset(lOffset, 0)  ""
        iColOffset = 0
        Do While UCase(Trim(rngStartIn.Offset(lOffset, 0)))  sEnd
           rngStartOut.Offset(lRowOffset, iColOffset) = _
                rngStartIn.Offset(lOffset, 0)
            iColOffset = iColOffset + 1
            lOffset = lOffset + 1
        Loop
        lOffset = lOffset + 1
        lRowOffset = lRowOffset + 1
    Loop
End Sub

Reply | Quote