Extracting certain rows

I have data in following format. Data is sorted by column1 first and Column3 second. Now I would like to extract values in column4 for the rows with max value in column3 for a given group in column1. So in the data below I would like to extract rows 1,4 and 9. Obviously I have thousands of rows with different sets of data to do this for.

Column1 Column2 Column 3 Column 4
A 567 5 abc
A 568 2 gbv
A 453 1 gfh
B 546 25 ght
B 256 15 rte
B 265 5 rtw
B 325 2 tyr
B 315 1 riu
C 246 56 ade
C 234 55 jff
C 342 32 hgf
C 651 12 dfd


Jay

Try this which assumes data starts in row 2, column A. Change worksheets as
required.

Sub ExtractRows()
Dim rnga As Range
Dim lastrow As Long, r As Long, n As Long
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rnga = .Range("a2:a" & lastrow)
r = 2
Do
n = Application.CountIf(rnga, .Cells(r, "A"))
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp)(2) = .Cells(r,
4)
r = r + n
Loop Until r > lastrow
End With
End Sub

HTH

"sa02000" wrote:

>
> I have data in following format. Data is sorted by column1 first and
> Column3 second. Now I would like to extract values in column4 for the
> rows with max value in column3 for a given group in column1. So in the
> data below I would like to extract rows 1,4 and 9. Obviously I have
> thousands of rows with different sets of data to do this for.
>
> Column1 Column2 Column 3 Column 4
> A 567 5 abc
> A 568 2 gbv
> A 453 1 gfh
> B 546 25 ght
> B 256 15 rte
> B 265 5 rtw
> B 325 2 tyr
> B 315 1 riu
> C 246 56 ade
> C 234 55 jff
> C 342 32 hgf
> C 651 12 dfd
>
>
> Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=520616
>
>

Works great. This is amazing... I want to learn this. I know the basic macro creation and basic object/method structure of OOP but I can't seem to articulate these in excel...any suggestions on what is the best way to go around doing that in a regular busy life (meaning ...a way to spend 3-4 hours a week for few weeks and get exertise in this)......

Jay

Well, getting a good book on VBA is a start (I have John Walkenbach's "Excel
Power Programming with VBA" which has a CD full of practical examples), lots
of practice e.g. record macros to see what they do and keep looking at this
site (or the programming site) where you will learn much from the experts
(not me!!)

I guess it's lile much else ..practice, practice and more practice!

Glad to know it worked for you.

"sa02000" wrote:

>
> Works great. This is amazing... I want to learn this. I know the basic
> macro creation and basic object/method structure of OOP but I can't
> seem to articulate these in excel...any suggestions on what is the best
> way to go around doing that in a regular busy life (meaning ...a way to
> spend 3-4 hours a week for few weeks and get exertise in this)......
>
> Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=520616
>
>

Is it possible to change the macro below so that it copies all the values in column2 corresponding to same value in column1 and paste them in different worksheet on row with same value as in column1 but while pasting I want to take a transpose (convert column data into row)??

Jay