1 reply
venkat1926
- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015
It is possible to write a macro but it is becoming complicated.
I am giving a hybrid solution. see whether it is helpful to you
your data sheet will be like this
A B C D E F h1 h2 hello hey yo
hello 3 5 7 10
hello 5
hello 6
hello 8
hey 2
hey 9
yo 10
yo 7
yo 15
the entries in D1,E1,F1 are clear
if you have more unique entries in col. A you can extend
further than F1.
the formula in D2 is
=MATCH(MAX(IF($A$1:A$100=D1,B1:$B$100)),$B$1:$B$100,0)
NOW INVOKE THIS FORMULA BY CONTROL+SHIFT+ENTER
copy D2 to E2 abd F2(if necessary beyond)
note that the values in D2,E2,F2 give the ROW NUMBER of the maximum value in col B corresponding to different entries like hello etc in column A
now I have created a small macro
when you run the macro the number 5,7,10 are copied in sheet 2.
modify the macro to suit you
I am giving a hybrid solution. see whether it is helpful to you
your data sheet will be like this
A B C D E F h1 h2 hello hey yo
hello 3 5 7 10
hello 5
hello 6
hello 8
hey 2
hey 9
yo 10
yo 7
yo 15
the entries in D1,E1,F1 are clear
if you have more unique entries in col. A you can extend
further than F1.
the formula in D2 is
=MATCH(MAX(IF($A$1:A$100=D1,B1:$B$100)),$B$1:$B$100,0)
NOW INVOKE THIS FORMULA BY CONTROL+SHIFT+ENTER
copy D2 to E2 abd F2(if necessary beyond)
note that the values in D2,E2,F2 give the ROW NUMBER of the maximum value in col B corresponding to different entries like hello etc in column A
now I have created a small macro
Sub test()
Dim j As Integer, k As Integer, rng As Range
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set rng = Range(Range("d2"), Range("d2").End(xlToRight))
j = WorksheetFunction.Count(rng)
'MsgBox j
For k = 1 To j
Cells(Range("d2").Offset(0, k - 1).Value, "b").Copy Worksheets("sheet2").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
Next k
End Sub
when you run the macro the number 5,7,10 are copied in sheet 2.
modify the macro to suit you
Subscribe To Our Newsletter!
