Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Thanked 1 Time in 1 Post

    Lazy normalisation (2000sp4)

    I'm looking for a macro which will normalise data. Have searched the forum but couldn't find one.

    My current list has 2 columns which need to be preserved, but the rest of the row needs to be normalised with an extra column created from the column heading (row 1). Needs to be flexible as to how many columns are filled with data for each row.

    Example (very abbreviated)

    <table border=1><td>ID1</td><td>Name</td><td>1991</td><td>1992</td><td>001</td><td>Prov</td><td>150</td><td>500</td></table>

    <table border=1><td>ID1</td><td>Name</td><td>Year</td><td>Value</td><td>001</td><td>Prov</td><td>1991</td><td>150</td><td>001</td><td>Prov</td><td>1992</td><td>500</td></table>
    Feeling lazy, so asking around in case someone has such a macro in their code library and is willing to share. If not, I will write one this week and post back, as I'm sure it's a common problem.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 30 Times in 30 Posts

    Re: Lazy normalisation (2000sp4)

    Try this:
    Sub Normalize()
    Dim wshS As Worksheet
    Dim lngMaxRow As Long
    Dim lngRowS As Long
    Dim lngMaxCol As Long
    Dim lngColS As Long
    Dim wshT As Worksheet
    Dim lngRowT As Long

    Set wshS = ActiveSheet
    lngMaxRow = wshS.Range("A65536").End(xlUp).Row

    Set wshT = Worksheets.Add
    wshT.Range("A1") = wshS.Range("A1")
    wshT.Range("B1") = wshS.Range("B1")
    wshT.Range("C1") = "Year"
    wshT.Range("D1") = "Value"
    lngRowT = 1

    For lngRowS = 2 To lngMaxRow
    lngMaxCol = wshS.Range("IV" & lngRowS).End(xlToLeft).Column
    For lngColS = 3 To lngMaxCol
    lngRowT = lngRowT + 1
    wshT.Range("A" & lngRowT) = wshS.Range("A" & lngRowS)
    wshT.Range("B" & lngRowT) = wshS.Range("B" & lngRowS)
    wshT.Range("C" & lngRowT) = wshS.Cells(1, lngColS)
    wshT.Range("D" & lngRowT) = wshS.Cells(lngRowS, lngColS)
    Next lngColS
    Next lngRowS
    End Sub
    The macro should be run from the sheet containing the non-normalized data. It will create a new sheet with normalized data.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Thanked 1 Time in 1 Post

    Re: Lazy normalisation (2000sp4)

    Thanks Hans,

    Just the trick.

    Will try and generalise it when I get a minute.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts