Excel IP Comparison Sheet
Hi...I just came across this thread and I'm completely new to the forum.
I just bought AmazonGhost and will be starting on my first Ghost account, but back to subject.
I saw that many of you are doing IP comparisons with no automated excel sheet, so I took some time to figure one out. I’m familiar with vlookup, etc…so a bit of Google’in and some Trial & Error wasn’t too difficult.
I personally hate doing things by pen&paper or doing any manual work whatsoever for things that should be automated, and for some reason it really bothered me that this hasn't been solved.
I can’t imagine having to review 200 IPs to see if I have a duplicate…
I figured I'll have to make something like this sooner rather than later so jumped on it.
I hope it’s a good contribution and intro to the forum.
I tend to write a lot and try to cover everything…I hope the forum accepts huge posts. NOTE:
I tried testing it, but I won’t vouch it’s 100% perfect.
Please make sure to scan it when you first start using it and make sure it’s working.
Please PM me at any time if you find there is a problem, and definitely inform everyone on this thread.
THANKS! SETUP THE SHEET
_____________________________________
First, notice there are two sheets.
The first is the one you’ll work with, called “IP CHECK”
The second is “MAC ADDRESS”.
*This is for you Amazon users as I know you need to refer to your MAC Addresses also.
You can delete my sample data. Only delete values in columns: ACCOUNT & IP ADDRESS
The other columns contain comparison formulas which need to be there.
MAC ADDRESS
(eBay only users, can skip this, you can even delete the whole column if it bothers you. Formulas update automatically on Excel. Not sure about OpenOffice. )
To setup mac addresses, place a name for your account in the “Account” column, then assign a mac address in the second column “Assigned MAC Address”.
I chose to put the Mac Address reference on a separate sheet to keep things more clean.
DO NOT change the name of the sheet as it’s used in the reference formula. CHECK IP IS GOOD TO USE
_____________________________________
1| Enter or Choose from dropdown the Acct you are going to be using.
*Make sure you type the name EXACTLY the same each time, or just use dropdown select after you’ve entered all your account names to be sure. If you name differs even a single character, you may get a false go ahead, or false duplicate and was an IP Addres.
2| MAC Address should pop up automatically from referencing your MAC Address sheet, you won’t have to search for it. Value should be copy and paste’able (except in excel, excel will copy/paste formula, not value)
3| Enter your current IP address to the column “IP ADDRESS”
Now you will be generally referring to the USAGE column.
If it shows a dash ( - ), you’re GOOD TO USE that IP Address for the current Account.
If it shows "DUPLICATE!" DO NOT use the IP Address, it was used previsously for a different account. (Same Account will not show duplicate, but will show that IP was used previously in "DUPLICATE" column)
TO PUT SIMPLY:
USAGE COLUMN
- = GOOD
DUPLICATE! = BAD! NOTE
I chose to use dashes if there is no value because you will have to continue to copy the formulas down the sheet.
I would hate if someone forgot to copy formulas down and kept getting and OK to use the IP Address.
With the dashes there is no guess work. Columns with dashes contain formulas, so keep copying those down the sheet as you enter IP Addresses... FURTHUR EXPLANATION FOR THOSE INTERESTED
_____________________________________
1| IF the IP address is new, both “DUPLICATED” and “USAGE” column will have a dash ( - ) in it.
*Hence, you can continue to use the IP Address.
2| IF the IP address has been used already, column “DUPLICATED” will show which account used that IP Address before. Only the first account that it was duplicated will show. If you followed the IP sheet properly the whole time, you shouldn’t have to see which other acct’s duplicated this IP as that should never happen.
Basically this means, if you used the same more than two accts, it will only show for one other account, it's not a "I screwed up many times" reference sheet.
3| IF the IP address has been used already, AND that account was the SAME account, you will still get a dash in the “USAGE” column as it’s still safe to use.
4| If the IP address was used by a DIFFERENT account, “USAGE” column will show “DUPLICATE!”. Do not use the IP Adress, you will link accounts. REMINDER
_____________________________________
Please keep reviewing and testing your entries and check that the sheet is correct for a while and let me know if there's any errors.
It's never been used, I just made it.
If you're not comfortable with that, DON'T USE IT.
I don't much patience for...less civil forum users that moan and complain at anything they can.
I'm just contributing my attempt thus far, please help review and test it.
In retrospect, I guess I shouldn't have called the first column Duplicate as it might be "scary" to see the IP was duplicated.
Also, I could have combined bother DUPLICATE & USAGE formulas into a single column...if a lot of ppl want me to simplifiy the sheet, I guess I could go about doing that and submit again.
Let me know.
Feel free to rename the columns as you like.
Please keep me updated on the sheet.
I hope it works out and helps a lot of you.
Vigil
|