An easy one I know but it's driving me nuts ! Please help me.

Posted by Marko on March 05, 2001 11:08 PM

I've read all the thread and can't find how to do this.

Column A1 to A1000 has a series of numbers (formatted as text)

Column A row 1 Column B row 1

203.25.125 66.25.125
601.22.125 101.22.22
25.45.87 203.25.125
661.31.209 234.34.56

Notice that cell A1 has the same string as cell B3

I want to create a macro that searches for unique strings in column a that aren't alreadt in column B anywhere. Then I want those unique numbers added to the first available blank cell in column B

I know this will be some simple lookup function or code but I've been trying all day and can't work this out.

Thanks in advance. Can you please email me at mbrady@bigpond.net.au in case I can't get back on here for a while.

Marko

Posted by Dave Hawley on March 06, 2001 5:53 AM

Hi Marko

This should do the trick

Sub TryThis()
Dim Col1 As Range, Col2 As Range
Dim Cell As Range
Application.ScreenUpdating = False

Set Col1 = Columns(1).SpecialCells(xlCellTypeConstants)
Set Col2 = Columns(2).SpecialCells(xlCellTypeConstants)

For Each Cell In Col1
If WorksheetFunction.CountIf(Col2, Cell) = 0 Then
Range("B65536").End(xlUp).Offset(1, 0) = Cell
End If
Next

Application.ScreenUpdating = True

End Sub

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on March 06, 2001 8:08 AM

Using VLOOKUP

You can also use the following array formula to get what you want.

B5 =IF(ISNA(VLOOKUP(A1:$A$4,$B$1:B4,1,0)),A1:$A$4,"")

B5 is the first blank cell after the entries which are already there.

You must enter this formula by hitting CONTROL+SHIFT+ENTER after typing it.

These will produce a blank cell when an entry from A exists in B.

If you don't like blank cells appearing in B, an additional formula (omitted here) in C can eliminate the blank cells

Aladin

Posted by Marko on March 06, 2001 7:08 PM

Thanks Dave ! Woo, that one is a little over my head but I'll give it a try and see how it goes.
Thanks again buddy !

Marko

Posted by marko on March 06, 2001 7:21 PM

Re: Using VLOOKUP

Thanks Aladin
I tried that formula and it creates a circular reference. Exactly where do I place this formula ? What cell? I tried Davids code but when I run his macro it crashes excell every time so I'm not sure where to go now.

Marko : I've read all the thread and can't find how to do this. : Column A1 to A1000 has a series of numbers (formatted as text) : Column A row 1 Column B row 1 : 203.25.125 66.25.125

Posted by Aladin Akyurek on March 06, 2001 11:24 PM

Re: Using VLOOKUP

Marko: You need to place the formula immediately *after* the last non-blank cell in column B.

Aladin