CODEWITHSUNDEEP
excelvbamaxrepeat
alosc
alosc

Reputation: 63

Extracting maximum values of an array with repeated ocurrences in Excel's VBA

I have a table which has repeated indexes as in the picture:

I want to get the highest value of each of Time 1 / Time 2 for each index ("Apple","Banana",...)

My code goes as follows:

dim i as integer, j as integer
For i=2 to 4
      j=2
      While Cells(i,6)=Cells(j,2)
          if Cells(j,3) > Cells(i,7) then
              Cells(i,7) = Cells(j,3)
          end if 
          if Cells(j,4) > Cells(i,8) then
              Cells(i,8) = Cells(j,4)
          end if
       Wend
Next i

So the idea is to iterate through the unique names, and then go through each occurence of the name and check wether it's bigger than the currently stored time.

I know there's some syntax error, since whenever I run the code it doesn't alter my worksheet, I couldn't really find documentation on how to replace values in VBA.

So I was wondering if there is a simpler way of doing this, and/or asking for help to get proper syntax going.

Upvotes: 2

Views: 729

Answers (2)

Relajadito
Relajadito

Reputation: 99

1.- You declare "j=2" but never change it, I think you must "navegate" thru rows from 2 to 10, isn't it?

2.- When you enter the "while" statement you never change conditions so ... it will repeat forever

I'm not sure what you must obtain, so please expose a sample of it

+++++ You must delete "-" values from your table

dim i as integer, j as integer
For i=2 to 4
    For j=2 to 10
        if Cells(i,6)=Cells(j,2) then
            if Cells(j,3) > Cells(i,7) then
                Cells(i,7) = Cells(j,3)
            end if 
            if Cells(j,4) > Cells(i,8) then
                Cells(i,8) = Cells(j,4)
            end if
        end if
    Next j
Next i

+++++ You must delete "-" values from your table

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

This can be done completely without VBA. Using your sample data, in cell J1 I used this formula to get the number of unique names in column B:

=SUMPRODUCT((B2:B10<>"")/COUNTIF(B2:B10,B2:B10&""))

In cell F2 and copied down to F6 is this formula to extract those unique names:

=IF(ROW(F1)>$J$1,"",INDEX($B$2:$B$10,MATCH(0,INDEX(COUNTIF(F$1:F1,$B$2:$B$10),),0)))

In cell G2 and copied over and down to H6 is this formula to pull the maximum time values for each unique name:

=IF($F2="","",MAX(INDEX(($B$2:$B$10=$F2)*C$2:C$10,)))

The table populated on the right (columns F, G, and H) is the result:

enter image description here

Alternately, this can be easily done with a pivot table. Select your original data in columns B, C, and D and go to Insert -> Pivot Table. I selected to put it on the same worksheet, but you could put it on a new worksheet if preferred. And then it will come up asking for your Pivot Table fields, set those up like shown (note that you will need to change the values from Count to Max):

enter image description here

And then you'll have a pivot table showing the same results:

enter image description here

Upvotes: 1

Related Questions