Help Keep Track of Duplicate IP's with Excel

Ratman2050 · #1 05-25-2011

I was looking for a way to manage my IP's and to make sure I don't use the same one on accident. I keep my IP's in an excel spreadsheet, but over time this IP list can become very long and hard to track. It is possible to use CTRL + F to search for the IP but I found a more effective way.

How to Highlight, Remove or Delete Duplicate Cells in Excel 2007?

To use this method, just make sure you select the ENTIRE column (i.e. click on the column letter at the top it will highlight everything) then apply the conditional formatting. This way everytime you add a new IP in the rows in that specific column, it will automatically work.

Here is a screenshot of the method working on my excel sheet.

Good luck!

GrannyT · #2 05-25-2011

Tried that - have you missed a bit off the end?

Ratman2050 · #3 05-25-2011

Quote:

Originally Posted by grannytranny

Tried that - have you missed a bit off the end?

Missed a bit of what off the end?

GrannyT · #4 05-25-2011

LOL - I opened my XL - Did what you said (conditional formatting) - then I don't see what I have to put in after that.

(Please bear in mind I'm blonde and thick!!)

rsot · #5 05-25-2011

@GTran - what you implying there? or better...what you drinking there?

GrannyT · #6 05-25-2011

Just my normal tipple hon - wine with dinner and vodka and tonic after (He's snoring on the settee and I've got to wake him to put the chickens away in 20 minutes - I need a drink) LOL:D

Ratman2050 · #7 05-25-2011

Quote:

Originally Posted by grannytranny

LOL - I opened my XL - Did what you said (conditional formatting) - then I don't see what I have to put in after that.

(Please bear in mind I'm blonde and thick!!)

If you highlighted the entire column you add IP's on like I do, and then applied the conditional formatting, then you are done.

Next time you add another IP address and it is a duplicate of one already written in your excel sheet, then it will highlight both and you will know that you are logging into the same IP as before.

ShadyOne · #8 05-25-2011

Yeah it is a great feature.

I am sure this will help many people who are not aware of it

Ratman2050 · #9 05-25-2011

I couldn't edit the original thread so I'm gonna put this here.

The highlighting feature above depends on how you sort out your IP's. I did some research and finally found a method that works extremely well (for my method of excel sorting). The way I sort my IP's can be seen here:

As you can see, Test 1 and Test 2 are where your ebay account usernames would go. Below them is the IP you use to login with them. Now, I use one phone to tether and give me IP's to logon multiple accounts. Before I login to eBay I ALWAYS go to IPBurger.com and check my IP, then copy and paste it to excel. Say I logon "Test 2" and write down the IP (in this case it is 199.99.0.0) and then I want to know if I entered in that same IP somewhere before in "Test 1's" column. How do I do that?

You run something called a MACRO. Here is what you need to do:

Copy the code below:

Code:

Sub HighlightDups()
Dim i, LastRowA, LastRowB
LastRowA = Range("A" & Rows.Count).End(xlUp).Row
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
Columns("A:A").Interior.ColorIndex = xlNone
Columns("B:B").Interior.ColorIndex = xlNone
For i = 1 To LastRowA
If Application.CountIf(Range("B:B"), Cells(i, "A")) > 0 Then
Cells(i, "A").Interior.ColorIndex = 36
End If
Next
For i = 1 To LastRowB
If Application.CountIf(Range("A:A"), Cells(i, "B")) > 0 Then
Cells(i, "B").Interior.ColorIndex = 36
End If
Next
End Sub

Then

Quote:

Press ALT + F11 in Excel.

Select INSERT > MODULE

Paste the macro into the editing area to the right.

Close the VBE and return to the worksheet.

Press ALT + F8 in excel.

When the Macros window opens, highlight this macro and click 'Options..'.

Enter a letter to be used as a keyboard shortcut and click 'OK'.

Close the Macros window.

To identify duplicates, press CTRL + your shortcut letter.

Note: If you prefer a different highlight color, change the '36' in both places in the macro to a different number.

I'm still working on a way to find how to use this method for example if you have 2 or more ebay accounts as this only works for 2 columns. One solution to this is using the same macro code above, but replacing the columns "A" and "B" with for ex. "C" and "D" and so on and then creating a new shortcut.

Hope it helps.

Source: Highlighting duplicate cells in two columns Excel 2007? - Yahoo! Answers

NotAHappyBunny · #10 05-28-2011

hi

thanks for that useful tip.

The code is very easy to modify to allow many columns to be compared.
Here is an example of columns B, C & D being compared. Just copy & paste this code into the Visual Basic Macro Editor in Excel.
Go to top toolbar menu (at the top of the screen) & select Tools > Macro > Visual Basic Editor
A new window will open.
Select View > Code from the toolbar menu (at the top of the screen).
Just paste the code from below.

You may need to adapt it slightly to customise it to compare your desired columns. If this is the case, simply replace the column letters with your own in the code (e.g This example shows the columns B, C & D being compared).

In the same Visual Basic Window > Save the macro.

You can easily make a button to execute this code whenever you paste a new ip entry into the spreadsheet.

Right-Click on the top toolbar in your spreadsheet > select Customise.

A small window opens choose the tab "Commands" and scroll down until you find "Macro". You should see a yellow smiley face icon - select it and drag it to your top toolbar.
Click the button in the same window "Modify Selection" > click assign macro and select your recently saved macro.

Close the window, save and press the new button - your duplicate entries will be highlighted.

Paste the following code into the visual basic code editor.

Sub HighlightDups()

Dim i, LastRowB, LastRowC, LastRowD

LastRowB = Range("B" & Rows.Count).End(xlUp).Row
LastRowC = Range("C" & Rows.Count).End(xlUp).Row
LastRowD = Range("D" & Rows.Count).End(xlUp).Row

Columns("B:B").Interior.ColorIndex = xlNone
Columns("C:C").Interior.ColorIndex = xlNone
Columns("D:D").Interior.ColorIndex = xlNone

For i = 1 To LastRowB
If Application.CountIf(Range("C:C"), Cells(i, "B")) > 0 Then
Cells(i, "B").Interior.ColorIndex = 36
If Application.CountIf(Range("D:D"), Cells(i, "B")) > 0 Then
Cells(i, "B").Interior.ColorIndex = 36
End If
End If
Next

For i = 1 To LastRowC
If Application.CountIf(Range("B:B"), Cells(i, "C")) > 0 Then
Cells(i, "C").Interior.ColorIndex = 36
If Application.CountIf(Range("D:D"), Cells(i, "C")) > 0 Then
Cells(i, "C").Interior.ColorIndex = 36
End If
End If
Next

For i = 1 To LastRowD
If Application.CountIf(Range("B:B"), Cells(i, "D")) > 0 Then
Cells(i, "D").Interior.ColorIndex = 36
If Application.CountIf(Range("C:C"), Cells(i, "D")) > 0 Then
Cells(i, "D").Interior.ColorIndex = 36
End If
End If
Next
End Sub

I know it looks complicated but it should only take you 10-30 minutes depending on experience, and it will save you lots of time & eliminate the risk of duplicate ip's.

ps

the grins in the column D should be replaced with the same format as colums B & C. I.E ("C:C")

ShadyOne · #11 05-28-2011

^ Seems like a lot of effort lol

What does it actually do more than the OP's first post?

NotAHappyBunny · #12 05-29-2011

it compares 5 columns of data, all i did was copy & paste a few lines of the original code & adapt to to look at my desired columns.

Each stealth will require one column so unless you only have 2 acc's - the op stated that they were trying to figure out a way to do it with multiple columns (more than 2) .

I prefer buttons to keyboard shortcuts - plus the smiley face reminds me to do it

ShadyOne · #13 05-29-2011

^ Oh yeah, I see. Thanks.