Vba Help needed
Closed
myrcka
-
Jul 14, 2009 at 01:54 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 15, 2009 at 09:46 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 15, 2009 at 09:46 PM
Related:
- Vba Help needed
- Vba case like - Guide
- Number to words in excel formula without vba - Guide
- Vba check if value is in array - Guide
- Vba color index - Guide
- How to open vba in excel - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 15, 2009 at 09:46 PM
Jul 15, 2009 at 09:46 PM
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
