How to analyze an array year to year to find the highest change in actual numbers?

Waimea

Active Member
Joined
Jun 30, 2018
Messages
423
Hi,

I have an array with values for 10 cities for 5 years, years 2020,2021, 2022, 2023,2024 and 2025.

I want to find the cities with the highest change (in absolute numbers and percentage) year to year and also from year 2020 to year 2025.

I have my data in a range.

VBA Code:
data = Sheets("Data").Range("AR26").CurrentRegion.Value

I am then using the following code to loop through the values of the different years.

VBA Code:
Dim idxCol As Integer, idxRow As Long

For idxCol = 2 To 6
    For idxRow = LBound(data, 1) To UBound(data, 1)
          
        area = data(idxRow, 1)
        aval = data(idxRow, idxCol)
   Next idxRow
    
    Application.Wait (Now + TimeValue("00:00:02"))
   

Next idxCol

Where do I start to find the change in actual numbers year from year? How do I calculate the change in percentage?

I want to keep the values in the array and then at the end redim the array into my sheet.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Waimea

Active Member
Joined
Jun 30, 2018
Messages
423
VBA Code:
Column A     Column B     Column C etc
Name           2020             2021
Town1           500               720
Town2           319               391
Town3           778               1092


I want to check if column C minus column B, column D minus column C.

And I want to find the highest change in absolute numbers and the percentage change between years.

So after running the code I would like to get a msgbox with the highest change in actual numbers & the percentage change.

I would also like to check the highest change in absolute numbers for the 10 cities between 2020 and 2025.
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
423
VBA Code:
  If idxCol = 3 Then
                If data(idxRow, idxCol) > data(idxRow, 2) Then
                    MsgBox area, data(idxRow, idxCol)
                Else
                '
                End If
        End If

        If idxCol = 4 Then
                If data(idxRow, idxCol) > data(idxRow, 3) Then
                    MsgBox area, aval
                End If
        End If

I want to compare all values and find the highest value betweeen 2020 and 2025.

Any suggestions?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top