How can i find maximum values depending on different data

[stakar]

I have data like the following
in columns A and B in Worksheet 1

A B
5 00
6 00
1 01
3 01
2 01
4 20
1 20

The B column's data are text numbers (e.g 01, 0010, 10, 150 etc)
-----------------------------------------------------------
I have the following result in worksheet 2 unordered
(they are the unique values of previous column B)

C
00
20
01

--------------------------------------------------------
I want a D column with the above data's max values next to each value
on column C in worksheet 2.
The source data are in the column A in worksheet 1.
Here's the result

C D
00 6
01 3
20 4


Im using this code , its fust and it is what i want....but
--------------------------------------------------------------------------------

Private Sub CommandButton1_Click()
Dim RngB As Range
Dim RngC As Range
Dim Cll As Range

Set RngB = Range("B1", Range("B65536").End(xlUp))
Set RngC = Range("C1", Range("C65536").End(xlUp))

For Each Cll In RngC.Offset(, 1)
Cll.FormulaArray = "=MAX(IF(" & RngB.Address & "=" & Cll.Offset(,
-1).Address & "," & RngB.Offset(, -1).Address & "))"
Cll.Value = Cll.Value
Next Cll
End Sub
--------------------------------------------------------------------------------
But the problem is that i cant fix the code working with the two
different worksheets because the A & B columns are in Worksheet 1, the
column C is in worksheet 2 and so the new one D will be created in
Worksheet 2

If anyone can help !!!!

Thanks in advance

 

[Frank Kabel]

Hi
try the following (you have to change the sheet names):
Private Sub CommandButton1_Click()
Dim wks_1 As Worksheet
Dim wks_2 As Worksheet
Dim last_row As Integer
Dim RngC As Range
Dim Cll As Range

Set wks_1 = Worksheets("sheet1")
Set wks_2 = Worksheets("sheet2")


last_row = wks_1.Range("B65536").End(xlUp).row
Set RngC = wks_2.Range("C1", wks_2.Range("C65536").End(xlUp))

For Each Cll In RngC.Offset(, 1)
Cll.FormulaArray = "=MAX(IF('" & wks_1.Name & "'!B1:B" & last_row & "="
& Cll.Offset(, _
-1).Address & ",'" & wks_1.Name & "'!A1:A" & last_row & "))"
Cll.Value = Cll.Value
Next Cll

End Sub

 

[stakar]

Frank, thanks a lot
Theres another thing i want to ask you

In the previous code
i have a cell value in worksheet A and i want to abstract this valu
from the array.formula but i get an error


For Each Cll In RngC.Offset(, 1)
Cll.FormulaArray = "=MAX(IF('" & wks_1.Name & "'!B1:B" & last_row
"="
& Cll.Offset(, _
-1).Address & ",'" & wks_1.Name & "'!A1:A" & last_row & "))"
Cll.Value = Cll.Value
Next Cll


Frank, thanks a lot
Theres another thing i want to ask you

In the previous code
i have a cell value in worksheet 3 and i want to abstract this valu
from the array.formula but i get an error
Here's the add in
"=('" & wks_3.Name & "'!$A$2+1)-

and the formula

For Each Cll In RngC.Offset(, 1)
Cll.FormulaArray = "=('" & wks_3.Name & "'!$A$2+1)-MAX(IF('"
wks_1.Name & "'!B1:B" & last_row & "="
& Cll.Offset(, _
-1).Address & ",'" & wks_1.Name & "'!A1:A" & last_row & "))"


Also i dont understand something (this is refering to the code withou
the "=('" & wks_3.Name & "'!$A$2+1)- )
so, when i have data like 01, 000, 10 it works fine but when i have 0
1, 8 anything below 10 i get blanks cells! Have you any idea?


I appreciate your help

Stathi

 

[Frank Kabel]

Hi
not sure what you're trying to achive. Please post the resulting
formula as you would enter it manually

 

[stakar]

the code
-------------------------------------------------------
For Each Cll In RngC.Offset(, 1)
Cll.FormulaArray = "=('" & wks_3.Name & "'!$A$2+1)-MAX(IF('"
wks_1.Name & "'!B1:B" & last_row & "="
& Cll.Offset(, _
-1).Address & ",'" & wks_1.Name & "'!A1:A" & last_row & "))"
Cll.Value = Cll.Value
Next Cll
----------------------------------------------------------------

The manual

={('SH'!$A$2+1)-MAX(IF('HS'!E1:E1500=$A$4,'HS'!C1:C1500)}

**the ('SH'!$A$2+1) is the cell A2 at worksheet("SH") plus 1

 

[Frank Kabel]

Hi
have you defined the variable wls_3 and assigne to your worksheet. That
is have you included the lines:
Dim wks_3
Set wks_3 = Worksheets("SH")

After doing this the code produced no error

 

[Tom Ogilvy]

Sub Tester1()
Set wks_3 = Worksheets(3)
Set wks_1 = Worksheets(1)
last_row = 1500
Set rngc = Range("A1:A10")
For Each Cll In rngc.Offset(, 1)
Cll.FormulaArray = "=('" & wks_3.Name & "'!$A$2+1)-MAX(IF('" _
& wks_1.Name & "'!B1:B" & last_row & "=" & _
Cll.Offset(, -1).Address & ",'" & wks_1.Name _
& "'!A1:A" & last_row & "))"
Cll.Value = Cll.Value
Next Cll
End Sub

worked fine for me.

 

[stakar]

Frank,
Thanks a lot
Foolish me !

Tom thanks for your support!!