Mukil - - Latest reply: Manish Bakde - Aug 11, 2010 at 06:31 AM

Best answer

venkat1926

- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015

suppose the data is from A1 to A9

in any cell paste this formula

modify the formula to suit your data

fill some day(numbers) in a sample sheet A1 to A9 and B1 to B9 and test the formula and then only modify it to suit you.

in any cell paste this formula

=INDIRECT("B"&MATCH(MAX(A1:A9),A1:A9,0))

modify the formula to suit your data

fill some day(numbers) in a sample sheet A1 to A9 and B1 to B9 and test the formula and then only modify it to suit you.

A few words of thanks would be greatly appreciated. Add comment

3194 users have said thank you to us this month

venkat1926

- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015

Match(x, r, 0)

Set r = Range(Cells(k, 1), Cells(k + 3, 1))

in the fisrt loop r is A1 to A4

x is the maximum of r

match function finds out the row no. in r of the value x

(o the third argument in match function is getting exact value)

then if find out the value of B in that row.

In the subsequent loops there is a small problem when you come to A5 to A8 it will only find the row no. within that range namely A5 to A8 . but what we want the the row number of the overall A range. so that addition of k-1 in the

set rmax=.....................

I hope I made it clear. see help under match in the worksheet. then do some experiments. you will get the hand of it. I am happy you do not want to blindly copy the macro and use it. You want to find out the logic. That will help you to write macros. greetings for happy new year. If you have doubts post back.

Set r = Range(Cells(k, 1), Cells(k + 3, 1))

in the fisrt loop r is A1 to A4

x is the maximum of r

match function finds out the row no. in r of the value x

(o the third argument in match function is getting exact value)

then if find out the value of B in that row.

In the subsequent loops there is a small problem when you come to A5 to A8 it will only find the row no. within that range namely A5 to A8 . but what we want the the row number of the overall A range. so that addition of k-1 in the

set rmax=.....................

I hope I made it clear. see help under match in the worksheet. then do some experiments. you will get the hand of it. I am happy you do not want to blindly copy the macro and use it. You want to find out the logic. That will help you to write macros. greetings for happy new year. If you have doubts post back.

venkat1926

- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015

I shall tell you how to modify the macro

copy the original macro safely somewhere so that it is not messed up

in the worksheet

large(a1:a6,2)

will give you the second maximum n A1 to A6

but in vba youi have to use "worksheetfunction "

dim y as double

y=worksheetfunction.large(r,2)

something like this. can you now try to modify the macro.

If there is problem you are free to repost

copy the original macro safely somewhere so that it is not messed up

in the worksheet

large(a1:a6,2)

will give you the second maximum n A1 to A6

but in vba youi have to use "worksheetfunction "

dim y as double

y=worksheetfunction.large(r,2)

something like this. can you now try to modify the macro.

If there is problem you are free to repost

Ya i build the macro to find second max value & so on..

thanks..

U used a command to clear column "C" .

Columns("C:C").Delete

i wanna to clear Column D,E,F while staring macro

i used the same as

Columns("D:D").Delete

Columns("E:E").Delete

but its not clearing the value instead it shift the values to previous column.

do the needs

Mukil..

- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015

You have not explained properly You must give the logic behind your needs.

maximum of four cells in which column and also which four cells.

you have two columns A and B.

coumn A has 8 cells, B has 8 cells. which is the maximums value, is it 10 in A1 or 4 in B1.

I suppose your logic is something like this.

you take the first four rows. In this subset in column A. the maximum is 20. the next cell value for 20 is 6. so 6 is to be added in C1 (that is the first row of the subset of column A.

similarly the second subset is 5th to 8 rows. here the maximum of column A in this subset is 52 which has next cell is 3. this 3 is be in column c in the fifth row (the first subset) in column C

next subset is rows 9 to 12 and goes on

Kindly confirm

maximum of four cells in which column and also which four cells.

you have two columns A and B.

coumn A has 8 cells, B has 8 cells. which is the maximums value, is it 10 in A1 or 4 in B1.

I suppose your logic is something like this.

you take the first four rows. In this subset in column A. the maximum is 20. the next cell value for 20 is 6. so 6 is to be added in C1 (that is the first row of the subset of column A.

similarly the second subset is 5th to 8 rows. here the maximum of column A in this subset is 52 which has next cell is 3. this 3 is be in column c in the fifth row (the first subset) in column C

next subset is rows 9 to 12 and goes on

Kindly confirm

u r correct.. i need the same

Take the first four rows. In this subset in column A. the maximum is 20. the next cell value for 20 is 6. so 6 is to be added in C1 (that is the first row of the subset of column A).

similarly the second subset is 5th to 8 rows. here the maximum of column A in this subset is 52 which has next cell is 3. this 3 is be in column c in the fifth row (the first subset) in column C

next subset is rows 9 to 12 and goes on

kindly help me in this ...

- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015

Of course i can have a formula which is becoming cumbersome and complex. so I have given a macro.

suppose there are two maximums in a subset e.g. a1 to a4 then it will take the corresponding B value only for the first maximum .

Sub test() Dim j As Integer, k As Integer, r As Range, x As Double, rmax As Range Worksheets("sheet1").Activate Columns("C:C").Delete j = Range("A1").End(xlDown).Row For k = 1 To j Step 4 Set r = Range(Cells(k, 1), Cells(k + 3, 1)) x = WorksheetFunction.Max(r) Set rmax = Cells(WorksheetFunction.Match(x, r, 0), "b") Cells(k, "C").Value = rmax Next k End Sub

suppose there are two maximums in a subset e.g. a1 to a4 then it will take the corresponding B value only for the first maximum .

Hi Venkat

Thanks for ur macro

but its not working properly.

for first set its fine - gives correct value

from the second set(ranges(5:8) ) onwards - its give the value of the fourth row.

the max is working fine .the probs. with the match function.

the result of my excel

A B C

10 1 2

60 2

30 3

40 B

50 5 B

60 6

70 7

80 8

90 9 B

100 10

110 11

120 12

130 13 B

140 14

150 15

160 16

170 17 B

180 18

190 19

200 20

-- look into it....

Thanks for ur macro

but its not working properly.

for first set its fine - gives correct value

from the second set(ranges(5:8) ) onwards - its give the value of the fourth row.

the max is working fine .the probs. with the match function.

the result of my excel

A B C

10 1 2

60 2

30 3

40 B

50 5 B

60 6

70 7

80 8

90 9 B

100 10

110 11

120 12

130 13 B

140 14

150 15

160 16

170 17 B

180 18

190 19

200 20

-- look into it....

venkat1926

- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015

yes there is a small error in the macro . sorry. try this revised macro

I have indicated where the modification is done in the macro as a comment.\\

please confirm whether it works ok.

I have indicated where the modification is done in the macro as a comment.\\

please confirm whether it works ok.

Sub test() Dim j As Integer, k As Integer, r As Range, x As Double, rmax As Range Worksheets("sheet1").Activate Columns("C:C").Delete j = Range("A1").End(xlDown).Row For k = 1 To j Step 4 Set r = Range(Cells(k, 1), Cells(k + 3, 1)) 'msgbox r.Address x = WorksheetFunction.Max(r) 'msgbox x Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b") 'the modification is in the above line 'msgbox rmax.Address Cells(k, "C").Value = rmax Next k End Sub

mukil
>
venkat1926

- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015

Hi Venkat

Thanks a lot.

Its working fine.

can u plz explain the match function

Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b")

i can able to get other functions.

from

mukil

Thanks a lot.

Its working fine.

can u plz explain the match function

Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b")

i can able to get other functions.

from

mukil

i need some more help form u.

actually i have a set of values in a column and i have 2 compare only by four,four cells and find the max & the value next to that cell.

i.e

@ my excel i have

A B C

10 4 6

20 6

15 7

05 9

45 1 3

35 5

52 3

20 4 ..... like that goes on..

I need the values in column "C" as i specified..