Get unique IP numbers two columns Excell 2007

May 29, 2013 at 13:03:40
Specs: Windows XP

Hi to Readers that can asist in solving this 'get unique IP numbers' Excell 2007 problem.
Just want to say, I'm not interested in learning excel for business use, only for highly selective
purposes. (non-business) So, I use Excel 2007 . It has indeed, been a learning curve of great
magnitude, it is a life saver, time wise, when it all finally sinks in.

For an experienced Excel user, this may well be the challenge to twist ones brain into a knot before solving, but for me it is total insanity to keep trying without seeking assistance..

Lets see if I can explain coherently enough to make sense here.

Will be two columns.(1 long) (1 short)

(No quote marks are used in the actual listing)
Long list is made up of numbers like:"111.111.*.*", and/or "111.111.111.* " type numbers, , plus some that are all numbers as in "222.111.214.214".
looks like this:
111.111.*.*
111.111.111.*
222.111.214.214

Short list is made up of all numbers: '111.111.111.111'.
looks like this:
111.111.111.111
2.2.2.2
23.234.0.245

Long list and short list will always have four decimal places. but not necessarily 12 numbers in each instance. Worst case scenario would look like '0.0.0.0'

Actually looking to bump the 'long' list against the 'short' list by comparing only the the first two sets of numbers from the 'long ' list, against the first two sets of numbers in the 'short' list, and outputting these 'unique' in (short list) to column C.
Appreciate the assist.
Going bonkers with this whole formula thing.l


See More: Get unique IP numbers two columns Excell 2007

Report •


#1
May 29, 2013 at 17:22:56
This formula will get you the first two sections of your number
regardless of how many sections.

IE your number is 222.111.214.214
the formula will return 222.111 with no trailing dot.

=MID(A1,1,FIND(".",A1,FIND(".",A1)+1)-1)

MIKE

http://www.skeptic.com/


Report •

#2
May 30, 2013 at 05:20:10
Thank you for the response.
And here is part of the problem for me.
How to use it in a formula that puts it all together or do I need to do perfrom individual steps?

Report •

#3
May 30, 2013 at 09:56:54
Puts what all together? I'm not sure what output you are looking for.

Perhaps an short example of your input data and the expected output based on that input would help.

Please click on the following line and read the instructions found via that link before posting your example data.

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 30, 2013 at 12:05:00
Try this and see if it's what your looking for:

With your Short List in column A
and your Long List in column B

In column C enter the formula: =MID(A1,1,FIND(".",A1,FIND(".",A1)+1)-1)
In column D enter the formula: =MID(B1,1,FIND(".",B1,FIND(".",B1)+1)-1)

You now have your first two sections of your Short List in column C
and you have your first two sections of your Long List in column D

Next in column E enter the formula:

=IF(ISERROR(MATCH(C1,$D$1:$D$100,0)),"",C1)

Drag down as many rows as needed.
This will compare Column C against Column D
and if the contents of cell C does not equal the contents of any cell B
the contents of cell C is inserted in column E.

The columns do not have to be sorted, although it might be easier.
I Started at Row 1, change the formula if needed.
I used only 100 rows to check, again change if needed.

MIKE

http://www.skeptic.com/


Report •

Ask Question