last 30

  • Thread starter Thread starter pytelium
  • Start date Start date
P

pytelium

I have a spreadsheet with about 60000 records.One column contains people
names. e.g

1 john smith
2 steve jones
3 mary loo
4 john smith

I want to carry out some analysis of each name(there are numbers in the
other fields),but i only want to include the last 30 entries maximum of
each name.e.g.John Smith has 63 entries I only want the last 30,Mary
Loo has 25 so I want them all.
 
Had a look at your site,Ron,very good,don't know if there is anythin
there too solve my proble
 
I want to copy all the records of all the people in a sheet into a new
sheet,but only including the last 30 records(or less) of each person.
In other words if John Smith has 63 records(rows),I only wa the last
30,but if Mary Loo has 25 I want all of hers. There are about 60000
rows comprising of only about 800 people.
With the new smaller sheet,i will be doing some calculations.
 
I think I'd try this against a copy of the file--no damage to the original!

Insert a new column A.
Put =row() in A1 and drag down all 60000 rows.
select column A and edit|copy followed by edit|paste special|values
Now you have one column that has an indicator for the original order of the
data.

Now sort your data by the name field (ascending) with a secondary key of column
A but in descending order.

So now each name is grouped together with the last rows at the top of each
group.

Now insert a new column B
If the name field is in column C, then put this in B2 (headers in row 1???)

(I'd save the file as a new name here--just in case!)

=COUNTIF($C$2:C2,C2)
drag down all 60000 rows
(the calculation for this could take a long time)

Now you have a counter for each item that belongs to "John Smith" in descending
order.

Select column B and edit|copy followed by edit|paste special|values
(constants will be quicker than formulas for the next step.)

Apply Data|Filter|Autofilter to that column B.
Filter to show less than 31.

Copy those visible rows to your new worksheet in a new workbook???

Sort the data (if you want) by the original order of the data (column A)
delete columns A and B.

save this new workbook.

And since you used a copy of the original workbook, just delete that working
copy.
 
Brilliant,Dave,it works,thank you. My only problem is I must carry ou
these steps
every day as my original sheet is updated each day
 
Try recording a macro when you do it manually. Then you can generalize that
code (make sure you include the last row mostly).

And run that macro whenever you want.

If you have any trouble with anything, post your code and I'm sure you'll get
help.

One tip...

To get the range to be sorted (and last row):

dim myRng as range
dim LastRow as long

with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("a1:X" & lastrow)
end with

Change x to the last used column in that worksheet.
 
I have everything fixed to my satisfaction on this front,Dave,thank
you.
I have another query but I will start a new thread.
 
Back
Top