Extracting certain rows

[sa02000]

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

 

[Toppers]

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]

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

 

[Toppers]

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]

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