CODEWITHSUNDEEP
vbaexcel
beeba
beeba

Reputation: 432

Transform Data by Groups in Excel

I have some data in Excel grouped into blocks of data in a single column. Each block has a serial number identifying at the top, then a space, and then the associated data. The format is similar to the following:

    20204

    0.009447773
    0.008672609
    0.04769611
    0.041069839
    -0.035158783
    -0.06094639
    0.059548043
    0.029640036

    8081

    0.003970223
    -0.024156246
    0.063038863
    0.005341972
    0.124618374
    0.005495709
    0.098642513
    0.005636186
    0.063350961
    0.128130779


    106663

    0.115009077
    0.049194194
    -0.057100467
    0.037476741
    0.063087314
    0.072773643
    0.003909923
    0.000448073
    0.008006874
    0.008718021
    0.009883258
   -0.022708477
    0.028655466

The blocks occur at regular intervals and are all the same length (spaced by 2007 cells). So the first serial number appears at D19, the next at D2026, then D4033, etc. This extends for several thousand rows.

I want to multiply each of the blocks of numbers by a different amount based on their serial number. So I'd want to multiply everything in the first block by 0.5, everything in the second by 2, everything in the third by 1.2, etc, according to a table such as: 

   Serial   Scalar
    20204   0.5
    8081    2
    106663  1.2

What would be the most efficient way to do this, either by VBA or in a formula in an adjacent cell?

Upvotes: 1

Views: 65

Answers (1)

user3598756
user3598756

Reputation: 29421

you could expolit Areas property of Range object

Option Explicit

Sub main2()
    Dim iArea As Long
    Dim serialToScalarTable As Range, cell As Range
    Dim multiplier As Double

    Set serialToScalarTable = Range("A2:B4") '<--| table where you have serial/scalar correspondence

    With Range("D19", Cells(Rows.count, 4).End(xlUp)).SpecialCells(xlCellTypeConstants, xlNumbers)
        For iArea = 2 To .Areas.count Step 2
            multiplier = Application.VLookup(.Areas(iArea - 1).Value, serialToScalarTable, 2, False)
            For Each cell In .Areas(iArea)
                cell.Value = cell.Value * multiplier
            Next
        Next
    End With
End Sub

Upvotes: 1

Related Questions