Compare two excel Columns of differant size [Solved/Closed]

Tushar - Sep 28, 2011 at 11:07 PM - Latest reply: rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- 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.

See more 

6 replies

Best answer
venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
- Sep 29, 2011 at 02:10 AM
1
Thank you
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

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1596 users this month

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..
Best answer
1
Thank you
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

Thank you, Tushar 1

Something to say? Add comment

CCM has helped 1596 users this month

Best answer
venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
- Sep 29, 2011 at 08:59 PM
1
Thank you
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.

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1596 users this month

rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- 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
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
- Sep 29, 2011 at 07:26 AM
0
Thank you
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.