Carpel tunnel hell! Please help!

allornothing

New Member
Joined
Apr 9, 2002
Messages
3
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top