Compare two excel Columns of differant size
Solved/Closed
Tushar
-
Sep 28, 2011 at 11:07 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 30, 2011 at 06:48 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 30, 2011 at 06:48 AM
Related:
- Compare two excel Columns of differant size
- Mtu size calculator - Guide
- Fifa 23 pc download size - Download - Sports
- Gta 5 size for windows 11 download free - Download - Action and adventure
- Beyond compare - Download - File management
- A5 size in word - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 29, 2011 at 02:10 AM
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
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
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
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 29, 2011 at 08:59 PM
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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Sep 30, 2011 at 06:48 AM
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
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
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
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
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 29, 2011 at 07:26 AM
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.
of course in excel 2007 much more
in such large database with running a macro it may crash. better use Access.
Sep 29, 2011 at 03:24 AM