 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006Excel help and advice needed please
Hi, I'm not very good with Excel, which must be obvious or I wouldn't be asking for help would I? I have a spreadsheet which has grown to hold 23,562 data entries from 7888 name categories, and I need to trim it by deleting old and excess data. Doing this manually will take too long to consider doing it on a regular basis, and there must be some method of speeding the process up. I can manage to get the data into alphabetical and date order, and run the subtotal tool to show how many entries are under each heading.. The smallest entry is 1, and the largest entry found so far is 17. Ideally I'd like no more than three entries per heading, and quickly and easily delete everything in excess of that on a weekly basis. Can it be done? Easily? Using ordinary language? I'd really appreciate it if anyone can point me in the right direction. Thanks Dave In an adjacent column add =COUNTIF(A:A,A1)>1 and the autofilter this new column for a value of TRUE, and delete the visible rows.  Signature--- HTH Bob (change the xxxx to gmail if mailing direct) > Hi, > I'm not very good with Excel, which must be obvious or I wouldn't be [quoted text clipped - 14 lines] > Thanks > Dave > In an adjacent column add > [quoted text clipped - 22 lines] >> Thanks >> Dave I'm not at all sure what this does, or why, or how you know what data is where. Try it and see what happens. Signature--- HTH Bob (change the xxxx to gmail if mailing direct) >> In an adjacent column add >> [quoted text clipped - 25 lines] > I'm not at all sure what this does, or why, or how you know what data is > where. What happens is that I get various returns of false and true that are not what I want at all. First 6 entries (beginning in cell A2) are AB = False AB = True AB = True ABN = True ADE = False AFKW = False AFKW = True AFKW = True So if I delete all the False entries I am losing data I need and keeping data I want to lose! Great Joke... Thanks > Try it and see what happens. > [quoted text clipped - 28 lines] >> I'm not at all sure what this does, or why, or how you know what data is >> where. so maybe filter on the other value? Signature--- HTH Bob (change the xxxx to gmail if mailing direct) > What happens is that I get various returns of false and true that are not > what I want at all. [quoted text clipped - 46 lines] >>> I'm not at all sure what this does, or why, or how you know what data is >>> where. No need, another suggestion worked perfectly well and does the job brilliantly. > so maybe filter on the other value? > [quoted text clipped - 49 lines] >>>> I'm not at all sure what this does, or why, or how you know what data >>>> is where. Hi Dave Assuming your data has Dates in column A and Category in column B, with headers in row 1. Also, assuming you want to retain what are the latest entries for each category and discard the rest. First, sort the whole block of data by Date>Descending. Use a spare column and enter =COUNTIF($B$2:B2,B2) Copy down for the entire length of your data. Then Data>Filter>Autofilter> use the dropdown on this new column and select Custom>Greater than >3 Mark the visible rows>right click>Delete Rows Data>Filter>Show All NB. Work on a copy of your data first, just in case you make any mistakes. SignatureRegards Roger Govier > Hi, > I'm not very good with Excel, which must be obvious or I wouldn't be [quoted text clipped - 17 lines] > Thanks > Dave > Hi Dave > [quoted text clipped - 34 lines] >> Thanks >> Dave My spreadsheet has a header row at the top, and the data has names in column A, the date doesn't appear until Column M. My dates are always sorted by date descending, so that the latest data is at the top of the entries. I have no empty columns until column X. My knowledge is very limited, but do I do a COUNTIF in column X? Would that be COUNTIF($A$2:A2,A2)? Is there no way to do this using subtotal instead, as I am happy with use of that function? Thanks I want to discard every entry more than three Hi You still haven't said which column holds the Category or heading that you are not wanting more than 3 of. If it is Names, then yes in X2 enter =COUNTIF($A$2:A2,A2) The formula counts how many times that name appears in an ever expanding number of rows as you copy down the table. When it has been applied, using an Autofilter on column X and selecting the values greater than 3, will select all the rows where the name appears more than 3 times, so those rows can be deleted. You will then be left with no more than 3 entries for each name. If is not Name that want to act upon but Category as you mentioned in your first post, make the formula in column X carry out the Countif on the column letter containing the categories. SignatureRegards Roger Govier >> Hi Dave >> [quoted text clipped - 52 lines] > > I want to discard every entry more than three Column A holds the defining category, but it is the rows across from the 4th onward that I wish to delete. It worked up to a point, but you could have warned me how long it would take to delete the number of rows involved - I was beginning to panic. Having to do it one number at a time was also very time consuming. Thankfully, in future, there won't be so much dross to clear out. Thanks for your help. > Hi > [quoted text clipped - 68 lines] >> >> I want to discard every entry more than three |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.