comparing 9 columns

[fredrerik84]

Hi my head is spinning here a little im trying to compere 9 column number to find highest values (also this number will be expanded later)

I want to return the highest number of these 3 Columns"J" "N" "R" result stored as variable double
I want to return the highest number of these 3 Columns"K" "O" "S" result stored as variable2 double
I want to return the highest number of these 3 Columns"L" "P" "T" result stored as variable3 double

results will be var1 + var2 + var3 in column W

Is this possible to to somehow? some of the rows in the columns might also be empty
data rows start from 12 (header in row 11) its around 200-300 data rows (varies)

I started making a for loop code for this but all my criterias makes me dizzy :/ hope someone a little more skilled know a good solution for this

 

[Marcelo Branco]

The code worked perfectly for me

Data sample below
Max of J, N, R = 450 (gray) ---> to X2
Max of K, O, S = 100 (blue) ---> to Y2
Max of L, P, T = 200 (yellow) ---> to Z2


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[TD="bgcolor: #DCE6F1"]

Q​

[/TD]
[TD="bgcolor: #DCE6F1"]

R​

[/TD]
[TD="bgcolor: #DCE6F1"]

S​

[/TD]
[TD="bgcolor: #DCE6F1"]

T​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Header​

[/TD]
[TD]

Header​

[/TD]
[TD]

Header​

[/TD]
[TD][/TD]
[TD]

Header​

[/TD]
[TD]

Header​

[/TD]
[TD]

Header​

[/TD]
[TD][/TD]
[TD]

Header​

[/TD]
[TD]

Header​

[/TD]
[TD]

Header​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

20​

[/TD]
[TD]

10​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD="bgcolor: #B8CCE4"]

100​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

22​

[/TD]
[TD]

8​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

18​

[/TD]
[TD]

80​

[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

40​

[/TD]
[TD]

15​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

30​

[/TD]
[TD="bgcolor: #FFFF00"]

200​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD]

30​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

450​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Results in X, Y, Z

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

X​

[/TD]
[TD="bgcolor: #DCE6F1"]

Y​

[/TD]
[TD="bgcolor: #DCE6F1"]

Z​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

450​

[/TD]
[TD]

100​

[/TD]
[TD]

200​

[/TD]
[/TR]
</tbody>[/TABLE]


It should work for you too.

M.

 

[fredrerik84]

Hi thanks for responding so fast..

It did work for 1 row that is correct., but it should calculate like this:

Prises in J,N,P are for same product(same row) best price to zell X
Prises in K,O,S are for same product(same row) best price to zell Y
Prises in L,P,T are for same product(same row) best price to zell Z

all of this is for row 12. now it should work downwards and also fetch best price for row 12 to "LastRow"

as you see in your sample it finds best price from row 12,14 and 15 but those are 3 different products

here is the code I started to work on before posting here (also before adding column R,S,T), and this will not work when I add more and more columns I will need something like your code to quickly identify the highest value

For i = 12 To lr
If Cells(i, "J") > Cells(i, "N") Then
      var1 = Cells(i, "J")
end if
If Cells(i, "K") > Cells(i, "O") Then
      var2 = Cells(i, "K")
end if
If Cells(i, "L") > Cells(i, "P") Then
      var3 = Cells(i, "K")
end if
next i

 

[fredrerik84]

Just to clarify

"J12" "N12" "R12" is product 1 a
"K12" "O12" "S12" is product 1 b
"L12" "P12" "T12" is product 1 c

Highest price should be stored in row 12 "X,Y,Z"
then:
"J13" "N13" "R13" is product 2 a
"K13" "O13" "S13" is product 2 b
"L13" "P13" "T13" is product 2 c

Highest price should be stored in row 13 "X,Y,Z"

"J14" "N14" "R14" is product 3 a
"K14" "O14" "S14" is product 3 b
"L14" "P14" "T14" is product 3 c

Highest price should be stored in row 14 "X,Y,Z"

and this system will continue to last row

 

[Marcelo Branco]

Please, to get more help try to provide a data sample (~5 rows) along with expected results.
Otherwise i have to guess what you really want . It seems i'm not doing this correctly - not my lucky day

M.

 

[fredrerik84]

sorry for this it was a little complicated I have a demo here:


upload files free

 

[fredrerik84]

I found a solution, still with my skill level its in a loop like this its a little on the slow end maybe you have a better suggestion.

Sub aTest()
Dim i As Long, lr As Long
Dim j As Double, n As Double, r As Double
Dim k As Double, o As Double, s As Double
Dim l As Double, p As Double, t As Double


lr = Cells(Rows.Count, "J").End(xlUp).Row + 1
For i = 12 To lr
   j = Cells(i, "J")
   n = Cells(i, "N")
   r = Cells(i, "R")
   k = Cells(i, "K")
   o = Cells(i, "O")
   s = Cells(i, "S")
   l = Cells(i, "L")
   p = Cells(i, "P")
   t = Cells(i, "T")
   Cells(i, "X").value = WorksheetFunction.Max(j, n, r)
   Cells(i, "Y").value = WorksheetFunction.Max(k, o, s)
   Cells(i, "Z").value = WorksheetFunction.Max(l, p, t)
Next i
End Sub

 

[Marcelo Branco]

This worked for me - data in Sheet1 columns K:U; results in W:Y (according your file)

Sub aTest()
    Dim lastRow As Long, vData As Variant, vResult As Variant
    Dim i As Long
    
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        vData = .Range("K12:U" & lastRow)
        .Range("W12:Y" & lastRow).ClearContents
        vResult = .Range("W12:Y" & lastRow)
        
        For i = LBound(vData, 1) To UBound(vData, 1)
            If vData(i, 1) <> "" Or vData(i, 5) <> "" Or vData(i, 9) <> "" Then
                vResult(i, 1) = Application.Max(vData(i, 1), vData(i, 5), vData(i, 9))
                vResult(i, 2) = Application.Max(vData(i, 2), vData(i, 6), vData(i, 10))
                vResult(i, 3) = Application.Max(vData(i, 3), vData(i, 7), vData(i, 11))
            End If
        Next i
        .Range("W12:Y" & lastRow) = vResult
    End With
End Sub

M.

 

[fredrerik84]

I tested your code and it works perfectly.. Much better then what I produced.. There is just one small problem as your code is to advanced for me to edit if it's needed.. Lol

Great job

 

[Marcelo Branco]

You are welcome. Glad to help

My code is not so complex.
Gets the lastRow ibn column C
Passes the data to a variant array (vData) to speed processing (avoid access the cells on the worksheet).
Prepares another variant (vResult) to receive the results.
Loop through vData; calculates the max, for each row, of the values in columns 1-5-9, 2-6-10, 3-7-11 of vData and passes the results to, respectively, columns 1, 2 and 3 of vResult.
At last, transfers the values in vResult to range W12:Y & lastRow
That's it!

M.

 

[fredrerik84]

thanks alot for taking your time to explaining this to me - i think I will be able to edit this ,,

what a brilliant code

have a nice weekend

cheers

edit:
380 rows in less then a sec , you could say that im quite happy with this