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
1
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..
0
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
1
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.
1
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);
0
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.
0