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.214Short 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.245Long 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

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

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?

Puts whatall 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.

Try this and see if it's what your looking for: With your Short List in column A

and your Long List in column BIn 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 DNext 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

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History