Re: last 30

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 work*** 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.





pytelium wrote:
>
> I want to copy all the records of all the people in a *** into a new
> ***,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 ***,i will be doing some calculations.
>
> --
> pytelium
> ------------------------------------------------------------------------
> pytelium's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25521
> View this thread: http://www.excelforum.com/showthread.php?threadid=391020

--

Dave Peterson
.

Quantcast