compare columns in excel 1

[Timme1]

Hello,

I'm looking for a solution for the following problem:

I have two columns with names.
I want to insert a funtion in a cell that compares these two columns and gives me the % difference (of the names which aren't present in both columns).

Is there an existing function for this? or how can I do this (without making new columns containing one's and zero's to calculate the difference).

Thanks in advance,

Timme

 

[Zathras]

You can encapsulate the logic of columns of 1's and 0's within a VBA User-Defined function, but you would need to be a bit more specific in how you want to do the calculation. For example if ListA has 100 names and ListB has 70 names and there are 60 matching names (40 in ListA that do not match any in ListB and 10 in ListB that do not match ListA), what is your definition of the % difference?

50 divided by 170? (29.4%)
Average of 40/100 and 10/70? (27.1%)
Some other calculation?

Once that is specified, it would be possible to write a User-Defined function that would have the following syntax:

=PERCENTDIFF(ListA,ListB)

where ListA and ListB are two ranges that contain the names.

And of course, there is the problem of what constitutes a match. The simples is if you can require only exact matches, i.e. SMITH and SMYTH would not match.

 

[Zathras]

Take a look at the answer posted in thread68-432495
You might be able to apply the technique for counting matches to achieve the results you want.

 

[onedtent]

also, this macro will extract unique values in column G, u can change destination (dest) as u like

Public Sub Uniques()
Dim rngA As Range
Dim rngB As Range
Dim cell As Range
Dim rw As Long
Dim dest As Range

Application.ScreenUpdating = False
Set rngA = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Set rngB = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
Set dest = Range("G1&quot
For Each cell In rngB
With cell
If Application.CountIf(rngA, .Value) = 0 Then

dest.Value = .Value
Set dest = dest.Offset(1, 0)

End If
End With
Next cell
For Each cell In rngA
With cell
If Application.CountIf(rngB, .Value) = 0 Then

dest.Value = .Value
Set dest = dest.Offset(1, 0)

End If
End With
Next cell
Application.ScreenUpdating = True
End Sub

 

[Timme1]

Hi,

The columns I have to compare are of equal size and they have to be exactly the same to match.(smith <> smyth)

eg 150 names and 65 are the same, then 43 % are the same and 57 % is different

Timme

 

[Zathras]

Something like this may be what you are looking for:

====================================================

Option Explicit
Function NonMatchCount(ListA As Range, ListB As Range) As Double
' Counts the number of items that are in both lists (must be same size)
' and returns the percentage of unmatched names

Dim c As Range
Dim d As Range
Dim MatchCount As Double

For Each c In ListA
  For Each d In ListB
    If c.Text = d.Text Then
      MatchCount = MatchCount + 1
      Exit For
    End If
  Next d
Next c
NonMatchCount = 1 - MatchCount / ListA.Count
End Function

 

[FatalExceptionError]

onedtent

How would i go about making that a function with parameters (columnToCopy, ColumnToCopyTo)

I'm new with this and don't know VB (I know C++, SQL+, Some Java and some C)

Is there a good site to go to read about the reserved words and logical operators and functions. If cats always land on their feet
and Toast always lands buttered side up
What happens if you tape toast, buttered side up on a cat's back??????

 

[onedtent]

set rngA and set rngB are the two columns being compared. u can set them to whatever u like.

set dest is ColumnToCopyTo

since u r looking for unique values, u need to comapre each column to the other. that 's where the two For Each cell.... routines come into play

rest is just routine stuff keeping VB happy

 

[Zathras]

I haven't followed the links myself so this isn't an endorsement, but in thread68-366578 there are a couple of web sites suggested for beginners.

Here is the short course:
1. When in Excel, type Alt-F11 to bring up the VBA editor.
2. Click Insert/Module from the menu
3. Paste the code into the module.
4. Leave VBA open or close it - no difference
5. Go back to Excel and use the function.

The function will be available whenever there is a workbook open that has the module linked.

The VBA help files provide most of the details you need regarding functions, etc. But finding exactly what you are looking for is sometimes a chore.

Best tip: Use the macro recorder: Tools/Macro/Record New Macro... After you stop recording, you can edit the resulting code which will teach you the basics.

 

[FatalExceptionError]

Is there anyway to save macros seperate from the .xls file
so that it can be used in another excel file. If cats always land on their feet
and Toast always lands buttered side up
What happens if you tape toast, buttered side up on a cat's back??????

 

[onedtent]

yes. if u save them in Personal.xls they could be used by any excel file on the computer.

 

[Timme1]

Hello,
Thank you for all the information you gave me.

The nonmatchcount function works very well. But I have 100000 records en 256 columns. This fuction is then really too slow (even more than an hour), certainly when I have to use it many times again.
(of the loop I think)

Is it possible to use a faster function?

Thanks for all the information I received. I also think it will be very helpful for many other readers (beginners like me)

 

[onedtent]

if u can dice 3 onions in 2 minutes, then a food chopper will do it for u in 10 seconds, where will u get a food chopper that will dice a ton of onions in 10 seconds?

u may have to be patient with Zathras' solution.

if u wish to learn more about such things, here is a link, but i doubt you will find something that will just zap your data

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

 

[carrr]

Onedtent is dead on...
This is just my 2 cents, but if you've got 100k+ records with 256 columns, you're working in the wrong environment. It's not the function that's slowing you down, you're pushing Excel's upper limit. You should consider another management program, i.e Access or,ideally, SQL Server.

 

[Zathras]

Agree with carrr.

BTW, how do you get 100k+ records in Excel? I can only get 65k or so.

 

[carrr]

Zathras...
Again, dead on...
I still run 97, but I didn't think the newer versions offered much more, if any. I thought 65K was the max, and that you could even lay it low sooner by making the records column-heavy. My take is, Timme1's either overestimating a bit, or lucky his workbook even opens. In either case, any function's going to take a good while to run on a crowded house like this one.

 

[xlhelp]

He has got them spread out over 256 columns.

With that many calculations, I would go on a vacation and hopefully it would be done when I comeback.

Zathras, haven't looked at your routine in detail, but would setting screenupdating to false speed up the process?

 

[Zathras]

I haven't tried that, but I'm not really doing any screen updating so I don't think so.

However, if he drops it on a cell and recalc is set to auto, any change in one of the referenced ranges will trigger the process to run again in its entirety.

So, make that a 2-week vacation.

 

[DaleWatson123321]

Timme,

In response to your question... &quot;Is it possible to use a faster function?&quot;...

Yes. Excel's &quot;database functions&quot; are extremely powerful and indeed can and should be used with your type of application.

They are &quot;powerful&quot; in two main ways.

1) Flexibility - in terms of being able to specify &quot;precise criteria&quot; for the type of &quot;data analysis&quot; required, and the criteria can be &quot;as elaborate as is necessary&quot;.

2) Speed - The database formulas and database extraction and filter functions are &quot;custom&quot; functions. Being custom functions, they are written in the &quot;C&quot; language, making them MUCH faster than &quot;looping through records with VBA&quot;.

I've &quot;taken up the challenge&quot; to make the case that Excel users should seriously consider this powerful component of Excel.

Based on your descriptions, I've created a model that I believe achieves the objective you've described. It takes into account the fact that one column can have names that do NOT exist in the OTHER column, and vice-versa. It generates a total list of UNIQUE names from both columns, and deducts the names that appear in one column but not the other, and of course the percentages.

You mention that you want to compare TWO columns, but you also mention that you have a total of 256 columns (and 100,000 records). I gather than you intend to compare two- columns-at-a-time, and that you intend to break up the number of rows - to allow for Excel's maximum of 65,536 rows.

In the model I've developed, I've populated two columns from A1:A65536, and B1:B65536.

Using a combination of the database formulas an extraction capability - which I've attached to a &quot;macro button&quot;, it takes exactly 1 second to produce the results for the two columns. And my PC is only a Pentium 2 - 300 Mhz.

So at this rate, my calculation is that it will take approximately &quot;2 minutes&quot; to generate the results for 256 columns. This is of course based on only the 65536 row maximum, and it does not take into account the time it will take you to copy the columns from your worksheet into the &quot;template&quot; worksheet I've created for you.

If you'd like the file, or if anyone else is interested, please email me - but please provide a specific reference. As I've been offering other files, it'll be difficult to know which file you're asking for without such a reference.

I hope this helps.

With the holiday coming up, I'll leave my HOME address as well.

Regards, ...Dale Watson

WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca

 

[Timme1]

Hello,

Thank you Dale for the file you have sent me. I'm really surprised by the speed of the calculations using database properties in excel. Otherwise, it is really too slow. I think many people will be interested and surprised by the file you made. None of my friend even knew that this is possible in excel.

Greetings,

Timme