473,836 Members | 1,265 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Comparing data in two columns in excel VBA

82 New Member
Hi, I would like to compare values in two columns in excel 2007 vba.

Basically if a cell value doesn't exist in column B that exists in column A then add the value to the end of column B.

Quite new to excel vba. Would appreciate any help.

Thanks
Dec 16 '11
18 6858
kadghar
1,295 Recognized Expert Top Contributor
b is 2
m is 13 (hope so)

so just
b= range(cells(1,1 3), cells(1,13).end (xldown))
and when you write down "d", just do the same.

Remember that cells(i,j) i is for rows, j is for columns (weird, i know)
Dec 16 '11 #11
Mihail
759 Contributor
You can customize how Excel show column's names.
Use the Office button (Top left side - colored as a rainbow).
This will help you to easy write VBA code.

Returning to your question, this code will do your job.
The single requirement is to have NOT empty cells in rows you are compare (A, B).

Expand|Select|Wrap|Line Numbers
  1. Sub AddToB()
  2. Dim cA As Long
  3.     cA = 1 ' column A
  4. Dim cB As Long
  5.     cB = 2 'column B
  6. Dim rA As Long, rB As Long
  7.  
  8.     rA = 1 'First row in A column (if you have a column header,: rA=2)
  9.     Do Until IsEmpty(Cells(rA, cA))
  10.         rB = 1 'First row in B column (if you have a column header,: rB=2)
  11.         Do Until IsEmpty(Cells(rB, cB))
  12.             If Cells(rB, cB) = Cells(rA, cA) Then
  13.                 GoTo Next_rA
  14.             End If
  15.             rB = rB + 1
  16.         Loop
  17.         ' No cell in rB = Ra
  18.         Cells(rB, cB) = Cells(rA, cA)
  19. Next_rA:
  20.         rA = rA + 1
  21.     Loop
  22. End Sub
Dec 17 '11 #12
NeoPa
32,584 Recognized Expert Moderator MVP
@Starlight849
It would be nice if you could confirm that the code in post #9 does actually work and do the job perfectly. I suspect you may have missed it due to posting yourself near to that time. It's always advisable to check all posts since the last one you recognise to avoid such things, but that may well not have occurred to you yet as you're quite new to this I know.

Kadghar:
I think the fastest way to do it, ...
I think if you check this out you'll find very few, if any, scenarios where your approach will outperform using the built-in Find() method. It's not absolutely impossible, but as the number of cells to check increases, so does the interpretation overhead of all the lines of code.

Certainly, well managed array processing can perform faster than referencing object collections, but ReDimming is also (proportionally ) a very large overhead indeed. I certainly wouldn't expect your claim of faster processing to be borne out by testing.

Alternatives are always welcome, of course, and we mostly think and work differently from others doing a similar job, so different approaches don't ever (in themselves) indicate any is right or wrong.
Dec 17 '11 #13
starlight849
82 New Member
NeoPa, your approach did indeed work. When I created a new excel workbook and used some sample data it worked fabulously. Perhaps the workbook I was working in has become corrupt. I will copy the data over and follow your approach.

Thanks for all of your help.
Dec 18 '11 #14
NeoPa
32,584 Recognized Expert Moderator MVP
I'm pleased to have helped :-) A little intrigued that it didn't with your other data, but I understand your data is sensitive so we won't pursue that angle.
Dec 18 '11 #15
kadghar
1,295 Recognized Expert Top Contributor
@NeoPa

You're absolutely right, redimming will slow it down quite a bunch. But as I said before: even if the core algorithm is bad. If you reduce your interaction with excel, the code will be a few hudred times faster (literaly).

I've worked with codes that would take hours to run with excel functions inside a loop. And as soon as I replaced them with any lousy/lazy algorithm that reads/writes only once. The time is reduced to seconds.
Dec 18 '11 #16
NeoPa
32,584 Recognized Expert Moderator MVP
Kadghar:
I've worked with codes that would take hours to run with excel functions inside a loop. And as soon as I replaced them with any lousy/lazy algorithm that reads/writes only once. The time is reduced to seconds.
I can imagine scenarios where this could be possible, but they're not common. I assume you turned off ScreenUpdating and Calculation was set to Manual while these long processes were running. Otherwise you would certainly expect some very long delays.

I can also imagine scenarios where the process would certainly be improved by taking it away from Excel cell storage and manipulating it in local memory (Implementing a specific type of Sort routine for instance - specifically not the inbuilt Sort() method). Using Find() in the code is very similar to this though. The performance of inbuilt functions is already heavily optimised. I think there may be a possibility of finding a scenario where that could be improved upon if the amount of data being processed were nearing Excel limits, but even that I'd need to check to be sure.

For a job like this, unless specified otherwise, tidy, sensible and efficient code is more usable and better for most projects than optimised code that doesn't share those other characteristics .
Dec 19 '11 #17
starlight849
82 New Member
Neopa,
I believe I figured out what the possible issue could have been with my original workbook. I had previously saved it as a .xls file and then converted it to a .xlsm file. Maybe the conversion caused the issue of the macros not working correctly.

Thanks again for your help.

Ps: If I was wanting to ignore the first row in the excel sheet, how would I go about that?
Dec 19 '11 #18
NeoPa
32,584 Recognized Expert Moderator MVP
starlight849:
If I was wanting to ignore the first row in the excel sheet, how would I go about that?
You would change line #8 to :
Expand|Select|Wrap|Line Numbers
  1. lngFrom = Val(Split(ActiveSheet.UsedRange.Address, "$")(2)) + 1
This tells it to ignore the first row of data that is found in the worksheet and start on the line immediately below it.
Dec 20 '11 #19

Sign in to post your reply or Sign up for a free account.

Similar topics

5
4095
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and running my data to excel. However, I need the data to export into Excel in a certain format, i.e it needs to begin importing at cell A4, and in truth it would be great
3
1614
by: Arno R | last post by:
Hi all, I need to transfer a lot of (denormalized) data from Excel To Access. Data is totally wrong formatted for my needs. I need to transform rows and columns. I also need to be able to specify different ranges to get what I want So I need range A1:E34 in one able and I need range A39:P50 in another table. After that I still will have to do some 'data-massage' in Access ... IIRC I did see code here recently to import data AND...
3
1826
by: phong.lee | last post by:
Hello all, i'm new at this. I need some assistant in transferring data from excel to access. I created a macro that basically gather all the necessary data that i need to bring into access. I also created a table in access with the correct columns to store the data from excel. Is there a way in VBA code that i can write so that when the data that i need can be automatically transfer into my access table? Thank you
1
15885
by: Brian Conklin | last post by:
Hello Eneryone, I am having a problem. I have written a little app that will take a text "pipe" delimited file and place all of the values in to an Excel spreadsheet. It works great on any of my XP Pro machines. When I install the app on a Win2K Pro machine, I get the following error message: ************** Exception Text ************** System.Runtime.InteropServices.COMException (0x80020008): Bad variable type. at...
4
4644
by: Agnes | last post by:
I can export the data to excel, but it is really really slow. need 5-6 mins to export 30 fields (a hundred records) . for my old vfp application, less than 3 minutes. for 500-800 records. Does any idea to improve the alog ?? Or what Can I do in my sql server ? Thanks a lot 'For displaying the column name in the the excel file. For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1 .Cells(1, intColumn + 1).Value =...
0
1237
by: comp21 | last post by:
Hi, Now, I have retrieved or imported data from excel spreadsheet to vb6 application. Now I want to compare this part of data with the one already existing in mssql2000 server(table name being same). If some data which is there in excel spread sheet not to be found in mssql2000 server then those data has to be stored in a seperate table in mssql server. Can anyone please help me with the code....
1
1205
by: jainsiddarth | last post by:
Hi experts, I want to retrieve data in excel based on the resources. I have three columns in the excel i.e. task, resource and status. Now tasks are unique and can be assigned to multiple users and for all task status can be either done, pending or blank. Now I want a summary sheet where I can display the summary data. Here there will be five columns i.e. person responsible, Total, Done, Pending and Blanks. Now for each person responsible,...
0
951
by: =?Utf-8?B?Y29uc3RhbnRpbg==?= | last post by:
How to build an array to make it easy to read the data from Excel worksheet table then load the data into oracle.The table looks like this have 4 columns:1-entity name with two rows(employee,employee),2-entity/table Definition(no rows),3-attribute name has 2 rows(employee_id,employee_name),4-Attribute/Column Definition with no rows. My code is below: Imports System.Data.OleDb Imports System.Data Imports Oracle.DataAccess.Client
3
2395
by: harmony123 | last post by:
hi have anyone tried importing specific column of an excel sheet to an sql database? i have tried importing data from excel and it worked but my problem is i want to import only specific columns from my excel file not necesarily importing all the columns..
0
5341
by: vivek kapile | last post by:
Language: ASP.net Platform: Visual Studio 2008 with ASP.net Technology: Used in ASP.net Introduction 1. Add a gridview into a aspx file 2. Add a button into a aspx file and give the name as "btnExportToExcel" 3. Write a code in aspx.cs file
0
9660
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10535
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10582
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9365
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5815
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4005
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3106
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.