Carpel tunnel hell! Please help!

[allornothing]

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 ]

[Mark W.]

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 ]

[codyswan]

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.

[allornothing]

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.

[lars]

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

[daleyman]

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.

[daleyman]

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

[allornothing]

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