Carpel tunnel hell! Please help!
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Carpel tunnel hell! Please help!

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Everyday I get a list of many thousands of IP addresses saved as an excel spreadsheet.

    Is there a formula or macro or something that I can use that will find every occurance of an IP in column D and add a label such as "hotmail" next to it in column F?

    Thanks!

    [ This Message was edited by: allornothing on 2002-04-10 15:37 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not use an AutoFilter to display the rows and enter "hotmail" in column F on the 1st displayed row and copy down?

    [ This Message was edited by: Mark W. on 2002-04-10 15:44 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It sounds like you already have a large list of what IP addresses match with hotmail, yahoo, etc. If not, you can sort some recent lists by IP address and make a table from there. The listing with the IP addresses and corresponding providors becomes your vlookup table.

    Then, next to each IP address for that day, you will have a copy of a formula something like this:

    =vlookup(sheet1!:a1[containing an IP address you want to match],IPTable[which is on another sheet or in another file and reused every day],2[return second column data, the matching hotmail or yahoo],options)

    I'm not sure if IP addresses are all clustered together for a particular service. If they are, set the option to match the nearest entry.

    If IP addresses for a service are not clustered together, use exact matching. In that case, put the vlookup inside an IF statement. Make the IF statement return some value like "n/a" if the IP address is not in your table yet.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the suggestions,
    "Find and replace all" would be perfect for this but I need to put the label in another column.

    I am doing some reading on autofilter now and crossing my fingers,
    Thanks codyswan but that is a little out of my league.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you need to use find and replace copy the original column to the column next to it insert column if you have to, then find replace all IP with Hotmail sort then delete

    Mark's is easiest but this will work too

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    don't be afraid to try codyswans method, vlookup is real easy once you've done it one time, and is really going to save the day for you on this one.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it depends on whether you have a definitive "reference" listing of all the IP's against what you want them to be called in the next column.. If so then vlookup is your tool.

    First put the definitive reference on another sheet, say "sheet2!A1:b200" where column A has the IP addresses and column B the labels.

    On your big list, in the new column, lets say for row 2 where IP address is in column A...

    =VLOOKUP(A2,sheet2!A1:B200,2,false)

    ...which basically says, return from the range specified, the value in the second column where the first column value matches that in cell A2 of this list (false just means don't estimate a match).

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Lars, I think that may have done it.
    Quick and dirty. I love it!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com