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]

Timme1: Could you post a description of the data you are working with? I don't get it. How is it sufficient to compare 2 columns at a time? Why couldn't a name be only in the left-hand column of one pair of columns and at the same time be only in the right-hand column of another pair of columns. If so, wouldn't that be counted as a miss for each pair of columns?

It really sounds like you should be using a database (Access or SQL Server) where you could simply use syntax like

SELECT COUNT(*)
FROM TABLEA, TABLEB
WHERE TABLEA.NAME = TABLEB.NAME

to get the count of matches and

SELECT COUNT(*) FROM TABLEA

to get the count of all names in one table and then divide to get your percentage.

Dale, could you give me a hint as to your approach? I have used database techniques in Excel quite a bit, but I can't quite picture what you have set up for Timme1.

Thanks, guys. I would really like to learn something here.

 

[DaleWatson123321]

Zathras,

In response to your question: "could you give me a hint as to your approach?"...

You and any others are quite welcome to more than just a "hint". Indeed, you're welcome to view the entire file. Simply email me, and I'll send the file via return email.

The file will paint the picture better than I can describe here. However, let me give you a brief description of what the file does.

It compares the "unique" names in one list (referred to as a "Special List" - on a separate sheet), with the "unique" names in up to 256 other lists on another sheet (one list per column).

These are the basic steps used...

1) A unique list of names is copied from the Special List to a separate sheet.

Then, referencing the number of columns in the Database sheet, the following is executed in a FOR NEXT routine for each of the (up to 256) separate lists...

2) A unique list of names (in the list) is copied to a separate sheet.

3) The column heading of the list is copied (from row 1) to a separate sheet containing 11 formulas that determine the requirments - i.e. the number of unique names, the number of "same" names, "different" names, total names, and percentages. (Two of the 11 formulas are "database" formulas).

4) The values from these formulas are then copied to a "Results" sheet - that is ready for printing.

The above is attached to a macro button called "Extract Names". So all it takes is a click of the button, and the report is ready to view and/or print.

I hope this helps.

Please don't hesitate to ask for the file.

Regards, ...Dale Watson

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