last 30

[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.

[Ron de Bruin]

Hi

Do you want to copy them to another sheet or ?

--
Regards Ron de Bruin
http://www.rondebruin.nl


"pytelium" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> pytelium
> ------------------------------------------------------------------------
> pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
> View this thread: http://www.excelforum.com/showthread...hreadid=391020
>

[pytelium]

Ron,the best maybe would be to copy all the records fulfilling the condition to a ne sheet and then do the calculations in the new sheet.

[pytelium]

Had a look at your site,Ron,very good,don't know if there is anything there too solve my problem

[Ron de Bruin]

Hi pytelium

We don't know what you want to do exactly

Maybe this
http://www.rondebruin.nl/copy5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"pytelium" <[email protected]> wrote in message
news:[email protected]...
>
> Had a look at your site,Ron,very good,don't know if there is anything
> there too solve my problem
>
>
> --
> pytelium
> ------------------------------------------------------------------------
> pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
> View this thread: http://www.excelforum.com/showthread...hreadid=391020
>

[pytelium]

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.

[Dave Peterson]

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.





pytelium wrote:
>
> 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.
>
> --
> pytelium
> ------------------------------------------------------------------------
> pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
> View this thread: http://www.excelforum.com/showthread...hreadid=391020

--

Dave Peterson

[pytelium]

Brilliant,Dave,it works,thank you. My only problem is I must carry out these steps
every day as my original sheet is updated each day .

[Dave Peterson]

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.

pytelium wrote:
>
> Brilliant,Dave,it works,thank you. My only problem is I must carry out
> these steps
> every day as my original sheet is updated each day .
>
> --
> pytelium
> ------------------------------------------------------------------------
> pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
> View this thread: http://www.excelforum.com/showthread...hreadid=391020

--

Dave Peterson

[pytelium]

I have everything fixed to my satisfaction on this front,Dave,thank you.
I have another query but I will start a new thread.