Find Large VBA

[mahmed1]

Hello

i have some month names across A:E
January (merged a1:b1) February (c1:d1) etc

now i need a code that will look in b:d:f etc row 2 to 100 and see which is the largest value found and then give the month of the largest value found and if there is a tie then give me that month also

this should be displayed in a msgbox with the value

If i had 60 as the largest value found for feb and may then
msgbox (Largest amount (value) to be given in feb and may)

 

[mahmed1]

Hi Rick

just to confirm, in row 1 across i have the months (merged) as stated previously
in row 2 i have the actual texts (Expected, say A2 and Actual B2) copied across
my data starts from Row 3

eg
January ........copied across (merged)
expected Actual ........copied across
80 100

sometimes users may enter text instead of values so i guess the isnumber will take care of that.

i hope i didnt confuse it by saying expected and actual values starting from row 2. The headings are in row 2

 

[Rick Rothstein]

Hi Rick

just to confirm, in row 1 across i have the months (merged) as stated previously
in row 2 i have the actual texts (Expected, say A2 and Actual B2) copied across
my data starts from Row 3

eg
January ........copied across (merged)
expected Actual ........copied across
80 100

While the code I gave you in my last message should still work, here is the code revised to exclude Rows 1 and 2 because they are both header rows...

Sub ShowMonthsWithMaxActualAmounts()
  Dim R As Long, C As Long, Max As Double, Msg As String, vArr As Variant
  Max = Evaluate("MAX(IF(ISNUMBER(A3:AZ100),A3:AZ100,0)*(MOD(COLUMN(A3:AZ100),2)=0))")
  vArr = Evaluate("IF(ROW(),IF((MOD(COLUMN(A3:AZ100),2)=0)*(A3:AZ100=" & Max & "),A3:AZ100,""""))")
  For C = 2 To 52 Step 2
    For R = 1 To 98
      If vArr(R, C) <> "" Then
        Msg = Msg & ", " & Cells(1, C - 1)
        Exit For
      End If
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub

Sub ShowMonthsWithMaxExpectedAmounts()
  Dim R As Long, C As Long, Max As Double, Msg As String, vArr As Variant
  Max = Evaluate("MAX(IF(ISNUMBER(A3:AZ100),A3:AZ100,0)*(MOD(COLUMN(A3:AZ100),2)=1))")
  vArr = Evaluate("IF(ROW(),IF((MOD(COLUMN(A3:AZ100),2)=1)*(A3:AZ100=" & Max & "),A3:AZ100,""""))")
  For C = 1 To 51 Step 2
    For R = 1 To 98
      If vArr(R, C) <> "" Then
        Msg = Msg & ", " & Cells(1, C)
        Exit For
      End If
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub

 

[mahmed1]

Hi Rick

Firstly I want to say thank you so much. You are a legend

Can I be a pain as for 1 other thing

Is it possible to do the same thing above but looking a couple of sheets

i have 3 sheets set up exactly the same and I have 3 regions. Sheets - North, West, East

can I get the same results added up to give the actual and expected over these 3 regions so I can see which month I am expected to receive the most money and which month I have received the most money

this code works fabulous for the 1 sheet

thank you. So much again

 

[mahmed1]

I suppose it will be 1 set of data adding up all the cells over the 3 sheets to 1 data set and then using that

i.e

North!a3+West!a3+East!a3 to az100 across all sheets

 

[Rick Rothstein]

Is it possible to do the same thing above but looking a couple of sheets

i have 3 sheets set up exactly the same and I have 3 regions. Sheets - North, West, East

can I get the same results added up to give the actual and expected over these 3 regions so I can see which month I am expected to receive the most money and which month I have received the most money

Does the output from these macros give you what you want (or did you want only the unique months no matter what sheet they were on)?

Sub ShowMonthsWithMaxActualAmountsAcrossSheets()
  Dim R As Long, C As Long, Max As Double, TempMax As Double, Msg As String, WS As Worksheet, vArr As Variant
  For Each WS In Sheets(Array("North", "West", "East"))
    TempMax = Evaluate(Replace("MAX(IF(ISNUMBER([EMAIL="'@'!A3:AZ100),'@'!A3:AZ100,0)*(MOD(COLUMN('@'!A3:AZ100),2)=0"]'@'!A3:AZ100),'@'!A3:AZ100,0)*(MOD(COLUMN('@'!A3:AZ100),2)=0[/EMAIL]))", "@", WS.Name))
    If TempMax > Max Then Max = TempMax
  Next
  For Each WS In Sheets(Array("North", "West", "East"))
    vArr = Evaluate(Replace("IF(ROW(),IF((MOD(COLUMN([EMAIL="'@'!A3:AZ100),2)=0)*('@'!A3:AZ100"]'@'!A3:AZ100),2)=0)*('@'!A3:AZ100[/EMAIL]=" & Max & "),'@'!A3:AZ100,""""))", "@", WS.Name))
    For C = 2 To 52 Step 2
      For R = 1 To 98
        If vArr(R, C) <> "" Then
          Msg = Msg & ", (" & WS.Name & ": " & WS.Cells(1, C - 1) & ")"
          Exit For
        End If
      Next
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub

Sub ShowMonthsWithMaxExpectedAmountsAcrossSheets()
  Dim R As Long, C As Long, Max As Double, TempMax As Double, Msg As String, WS As Worksheet, vArr As Variant
  For Each WS In Sheets(Array("North", "West", "East"))
    TempMax = Evaluate(Replace("MAX(IF(ISNUMBER([EMAIL="'@'!A3:AZ100),'@'!A3:AZ100,0)*(MOD(COLUMN('@'!A3:AZ100),2)=1"]'@'!A3:AZ100),'@'!A3:AZ100,0)*(MOD(COLUMN('@'!A3:AZ100),2)=1[/EMAIL]))", "@", WS.Name))
    If TempMax > Max Then Max = TempMax
  Next
  For Each WS In Sheets(Array("North", "West", "East"))
    vArr = Evaluate(Replace("IF(ROW(),IF((MOD(COLUMN([EMAIL="'@'!A3:AZ100),2)=1)*('@'!A3:AZ100"]'@'!A3:AZ100),2)=1)*('@'!A3:AZ100[/EMAIL]=" & Max & "),'@'!A3:AZ100,""""))", "@", WS.Name))
    For C = 1 To 51 Step 2
      For R = 1 To 98
        If vArr(R, C) <> "" Then
          Msg = Msg & ", (" & WS.Name & ": " & WS.Cells(1, C) & ")"
          Exit For
        End If
      Next
    Next
  Next
  Msg = Mid(Msg, 3)
  MsgBox Msg
End Sub

 

[mahmed1]

Hi

what i wanted was to get the month i am most expected to get

eg sample data

North

Jan Feb
10 15. 20.30

West


Jan Feb
20 30. 20.50

East


Jan Feb
5 10 50 30


Most expected month is Feb with 90
and most actual is also feb with 110

this is just a sample data of how the results should

the code you gave for the max month for 1 sheet is the same result i want so in theory all the data across all these sheets are added in 1 datasaet and then doing the same as what you gave the code

i will test the code you gave

Is the anychance you can explain how the tempmax part works esp with the replace bit
i understand the rest of the code

thank you

 

[Rick Rothstein]

what i wanted was to get the month i am most expected to get

eg sample data

North

Jan Feb
10 15. 20.30

West

Jan Feb
20 30. 20.50

East

Jan Feb
5 10 50 30

Most expected month is Feb with 90
and most actual is also feb with 110

What are the numbers you show for each sheet... the maximum values for each month per sheet no matter what row the individual month appeared in (where the row for the actual is independent from the row for the expected)?

 

[mahmed1]

The numbers are just normal numbers i in put for each sheet. They are independant

as an example

North
say I was expected to get 50pounds in january but the actual was 60
and expected 100 and got actual of 150 in feb

just say for this example it was the same for the other regions

the results id expect is:
im expected to receive the most amount in feb 300 and actual received is 450

i hope i aint conplicating it

 

[Rick Rothstein]

The numbers are just normal numbers i in put for each sheet. They are independant

as an example

North
say I was expected to get 50pounds in january but the actual was 60
and expected 100 and got actual of 150 in feb

just say for this example it was the same for the other regions

the results id expect is:
im expected to receive the most amount in feb 300 and actual received is 450

i hope i aint conplicating it

The problem I am having is figuring out where those single row numbers are coming from given each month is checking 98 rows of data (Rows 3 thru 100) on each sheet. It is important to keep in mind when asking questions on forums that as obvious as everything is to you, that is how unobvious it is to us given that we know nothing about your data or what you want to do with it except for what you tell us... you cannot assume that we will "figure it out".

 

[mahmed1]

Hi Rick

Sorry for not being able to explain properly

The numbers for each month expected and actual is updated manually for each month. No calculations involved

I have an companies assigned to each row of data say in (Z3 copied down). I should really be putting them in A3 copied down to 100 which i will now end up doing as it makes more sense to do so (the spreadsheet was set up like that already)


There could be a different company assigned to differe
nt rows for each region therefore im not interested in the company but the amount.

So when a company say's il give 100 to the north region in May
il find that row and in the expected put in 100

But if they end up giving 200 in Aug il put in 200 for that company in the actual column for that month.

This is a manual input for each sheet, month depending on which company is giving/expected to give.

The thing im interested is which months am i going to receive the most and expected to receive the most (The initial code gives me that.

But now i want to go that step further and give an overal analysis to give me which month overall im expected to get the most money and which month i have received the most money (Ignore the company who has given it)

In theory its a sum of all the 3 sheets cell in to 1 and that doing the same code you have given initially in 1.

Unfortunately i cant create another sheet or else i would have just recreated the same sheet as the other 3 sheets and had a formula like this and copied across and down and then run the code for this data on the main summary data if you like

=north!a3+East!a3+West!a3 copied accross and down

Please forgive me as i am trying my best to be clear

Thank you so much for your help and patience

Ps what does the latest actual code you provided results give

The max months for each sheet?