# Compare two excel Columns of differant size

Solved/Closed
Tushar - Sep 28, 2011 at 11:07 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 30, 2011 at 06:48 AM
Hello,

i am having two excel columns of differant size..Both the columns contain mobile numbers..i have columns A containing all the mobile numbers ..and Column B contains mobile numbers that are not in service..

So I would like to extract mobile numbers in column A which are not equal to column B...or Equal to column B..

Regards.

## 4 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 29, 2011 at 02:10 AM
suppose sample data is like this in columns A and B . column headings are in the row no. 1.

all
mobile
nos inactive nos
1 1
2 3
3 5
4 7
5
6
7
8
9

try this macro and see columns F and G

```Sub test()
Dim ra As Range, rb As Range, ca As Range, cfind As Range
Range("F1") = "in service"
Range("G1") = "not in service"
Set ra = Range(Range("A2"), Range("A2").End(xlDown))
Set rb = Range(Range("B2"), Cells(Rows.Count).End(xlUp))
For Each ca In ra
Set cfind = Columns("B:B").Cells.Find(what:=ca.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Cells(Rows.Count, "G").End(xlUp).Offset(1, 0) = ca.Value
Else
Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = ca.Value
End If
Next ca
End Sub```
thanks a lot venkat ..it was sucessful..but I wanted to know that what happens if I compare a column with 4 lakh mobile numbers to column with 1 lakh numbers..will this work..
can you let me know how to compare in Msaccess..and can I use this macro..since I have to compare almost 4 lakh records at once..and wanted this process to become faster..as excel is crashin again and again with huge load
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 29, 2011 at 08:59 PM
sorry I do not have familiarity with access vba. you can post the problem again in the same forum or some other access forum indicating the software is access. some access vba expert will help you.
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Sep 30, 2011 at 06:48 AM
For access you will not need VBA. Create two tables with two columns in each table. The first field/column of the table will be for the number and the second for the status. Import the column A in one table and Column B in the other table. Now run the update sql to update the status

lets say table you have these two tables
```CREATE TABLE tbl_column_a (column_a  text(50), status text(50))
CREATE TABLE tbl_column_b (column_b  text(50), status text(50))```

You import your column A in table tbl_column_a in column_a of the table
and you import your column B in table tbl_column_b in column_b of the table

Now you can run an update statement to mark the record
```UPDATE TBL_COLUMN_A AS TBL_A
SET
TBL_A.STATUS = "ACTIVE"
WHERE
EXISTS (SELECT  1 FROM   TBL_COLUMN_B TBL_b WHERE TBL_A.COLUMN_A=TBL_B.COLUMN_B);```

or you can run a select statement to find out what is present in one and not in other ( you can use Access. Some thing like this

```SELECT
TBL_COLUMN_B.COLUMN_B,
'INACTIVE' AS number_status
FROM
TBL_COLUMN_A
RIGHT  JOIN TBL_COLUMN_B
ON TBL_COLUMN_A.COLUMN_A = TBL_COLUMN_B.COLUMN_B
WHERE (TBL_COLUMN_A.COLUMN_A Is Null);```
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 29, 2011 at 07:26 AM
in excel 2003 there are only lilttle more than 63 thousand rows.
of course in excel 2007 much more

in such large database with running a macro it may crash. better use Access.