At the beginning of each month I deal with raw data from all the referrers that have sent traffic to our site. I import this into Excel and then delete the i.p. addresses. Thing is there are hundreds of thousands of i.p addresses and even when I sort them numerically there are odds and ends of urls wedged in. For example 1029i.com, 1039thepoint.com, 1073jamz.com...etc. Is there a way to set the auto-filter to find data that contains numeric symbols but no alpha characters. When I start deleting rows I often accidently delete numeric urls that I should be retaining.
Thanks in advance for any suggestions you may offer. *************************************************************** 07-02-03, 10:59 PM Bazcaz Dont know if this will help..go to page 15 filtering your work: Filtering *************************************************************** 07-03-03, 08:09 PM crescen7 It seems like the custom filtering option which filters on "contains" may work.
If not, it wouldn't be that hard to write a filtering script for the raw data, (assuming it comes in using a common format) prior to sending it to Excel. If any of your web-site associates have some programming experience they could probably write it.
If all else fails, post back and I'll try to hack something up if you can give an example of the raw data file.
good luck! *************************************************************** 07-15-03, 04:31 PM Georgia85 I appreciate both your input. Unfortunately I am quite familiar with the auto filter function and while appreciate the info, I did already know this.
And Crescen...I'm trying to use the autofilter option "contains" but how do I tell it to look for an alpha character?
See, there is one url in the middle of all the ip addresses (now imagine an excel worksheet with 600,000 lines of IP addresses). I want to set the filter find all lines that "contains" an alpha character....so I won't accidently delete a valid url when I'm doing my clean up of the data. *************************************************************** 07-22-03, 06:46 PM Kwll Georgia, I would recommend using Access vs excel if you want to do some complex sorting or queries. You can always keep your data in excel and then import it into Access and create your own reports, queries and/or forms. *************************************************************** 07-23-03, 12:21 PM Georgia85 Alrighty KWLL...then how do this in Access? I still need to figure out a way to differentiate all numbers from numbers with some alpha characters. Big Grin *************************************************************** 07-23-03, 01:09 PM Kwll What you have to have is something that you can designate as a primary key...which is a number that is unique and can identify individual records. Social security numbers are commonly used for this purpose. I don't have your data but surely there must be something that can designate a primary key. If not you could always use an autonumber field and designate that as the primary key so long as you don't do a lot of manipulation with the records and do a lot of deletions. Design view of the table is where you designate the primary key along with other properties you may want to use. *************************************************************** 07-23-03, 02:58 PM Georgia85 Thanks Kewl. It's been quite some time since I've used Access...guess I'll just have to play around with it some. Not sure it will serve my purpose. The data base I pull from is about a million lines of data which is too much for excel - that's why I have to delete so many lines in order to fit in the usefull data.
If I could get people to stop searching on the internet then my job would be much easier! Oh wait...if they did that then I wouldn't have a job! *************************************************************** 08-05-03, 11:41 AM Dwight With reference to crescen7's post, here is a link to sorting program that may serve as a starting point: VBSortGen is free to test for 10 days and $10 to keep
I downloaded and installed the VBSortGen program, and it seems to run and work fine on my Win98 machine. (I downloaded the "InstallVBSortGenOnly.msi" file).
I would guess you'll need a customized sort script, but I thought this program might be a useful starting point.
Dwight
P.S. I don't know what program version your Excel is, but I just noticed that the Excel 2002 offers a new feature over prevous Excel versions.
Sort Text as Numbers
Sort a column that has both numbers and numbers formatted as text.
This feature prevents unexpected sorting results by detecting mixed lists of numbers and text and enabling sorting text as numbers. Excel 2002 Product Guide
This message has been edited. Last edited by: DorianGreyed,
Posts: 9192 | Location: Atlanta, GA, USA | Registered: 06-03-02
