Page 1 of 1

Comparing cells

Posted: Fri Mar 14, 2008 5:13 pm
by JasonBourne
Hi,
I've been searching for such function / macros for ages.
Imagine you need to compare several sells values and get common values listed in separate cell. Here is the sample:
A1 = 30
A2 = 43
A3 = 35
--------
B1 = 50
B2 = 34
B3 = 35

C1 = 35 (this sell should contain a formula, not a value)
The same goes for comparing text values. To give you a better picture of why the hack do I need this function I'll tell you that I have a long list of URLs and another short one and so I need to compare them and have matching values in a third list.

Re: Comparing sells

Posted: Fri Mar 14, 2008 5:23 pm
by James
C3: =IF(A3=B3;A3;"")

Is this what you want?

Re: Comparing sells

Posted: Fri Mar 14, 2008 5:29 pm
by JasonBourne
James wrote:C3: =IF(A3=B3;A3;"")

Is this what you want?
I wish it was that simple:-) What I mean is to compare every sell from a first massive with a first sell from a second massive, then do the same for the second sell of a second massive and so on...

Re: Comparing sells

Posted: Fri Mar 14, 2008 5:51 pm
by James
Sorry I don't understand. I'm assuming you mean "cell" as in a box on a spreadsheet, not "sell"?
What is a "massive"?

Are you saying you want to compare cell A1 in 2 separate spreadsheets? Your example in your first post suggested comparing values in 2 columns (A & B):
JasonBourne wrote:Imagine you need to compare several sells values and get common values listed in separate cell. Here is the sample:
A1 = 30
A2 = 43
A3 = 35
--------
B1 = 50
B2 = 34
B3 = 35

C1 = 35 (this sell should contain a formula, not a value)
The same goes for comparing text values.
How long is your list of values?

Re: Comparing sells

Posted: Fri Mar 14, 2008 5:59 pm
by JasonBourne
Ok, I guess it's because of it's Friday:-) Yes, I mean cell, not sell:-).
I'm gonna give you a more illustrative sample to clarify the whole thing.

I need to compare cell values on a single sheet.

A1 = http://www.google.com
A2 = http://www.yahoo.com
A3= http://blah-blah.com

B1 = http://www.Iguessnowitlooksbetter.com
B2 = http://www.yahoo.com
B3 = http://www.thanksgoditsfridayalready.com

C1 = http://www.yahoo.com (formula)

I'm talking about several thousands values in A column and several hundreds in B column and I need to have matching values for the column A and B at column C

Re: Comparing sells

Posted: Fri Mar 14, 2008 6:05 pm
by James
JasonBourne wrote:C1 = http://www.yahoo.com (formula)
Is that C1 or should it / could it be C2?

Re: Comparing sells

Posted: Fri Mar 14, 2008 6:09 pm
by JasonBourne
Well I'm giving a sample where there is only one match, but it's possible to have several and I want to have them listed in column C.

Re: Comparing sells

Posted: Fri Mar 14, 2008 6:14 pm
by James
That (the answer to my Q) doesn't matter as much as - how automated do you want this to be?

For instance, using the formula I pasted above, you could:
  1. Drag that down the entire sheet
  2. Copy column C and paste values into column D
  3. Sort column D to remove blanks
But obviously steps 2 & 3 are manual in this method. If you need this to be done via a macro, then I suggest you ask nicely in the Macros and UNO API forum ;).

Re: Comparing cells

Posted: Fri Mar 14, 2008 9:36 pm
by JasonBourne
Well, now I'm sitting at home with my Macbook on my laps and trying your suggestion ;)

1. Ok, I think you still can't get my point:-) A formula which I want to place on column C should do the following:
Compare Every cell from column A with a first cell from column B. Keep in mind that it's possible that identical to B1 cell value could be located in ANY cell of a column A, from A1 to An, where n - is a given number of cells for a particular case

2.After comparing the first cell of a column B with every single cells value in column A do the same with the second cell of a column B (B2) and so on


Thanks a lot for your help! 8-)

Re: Comparing cells

Posted: Fri Mar 14, 2008 9:39 pm
by James
JasonBourne wrote:A formula which I want to place on column C should do the following:
Compare Every cell from column A with a first cell from column B.
Ah! Now I understand :idea: Will need to think on this for a while.

Re: Comparing cells

Posted: Fri Mar 14, 2008 9:53 pm
by James
Does it matter whether there are repeated values e.g. if the value in B1 appears 3 times in Column A?

Re: Comparing cells

Posted: Fri Mar 14, 2008 9:58 pm
by Dave
Example:

I have this in columns A and B:

q s
w t
e f
r u
t h
y o
u k
I
o
p
a
s
d
f
g
h
j
k

In C1, I put this: =IF(ISNA(OFFSET(A1;MATCH(B1;$A$1:$A$19;0)-1;0));"No Match";OFFSET(A1;MATCH(B1;$A$1:$A$19;0)-1;0))
Copy down column C.

With an "s" in B1, I got an "s" in C1. With anything not in the list of column A, I got "No match".

David.

Re: Comparing cells

Posted: Fri Mar 14, 2008 10:07 pm
by James
OK one way to do it, similar to the method I posted before, is to use:
=IF(ISNA(VLOOKUP(B1;$A$1:$A$65536;1;0))=1;"No match";VLOOKUP(B1;$A$1:$A$65536;1;0))

If you enter this formula in Column D it will tell you if the value is unique or not:
=IF(COUNTIF($A$1:$A$65536;C1)>1;COUNTIF($A$1:$A$65536;C1);"")

HTH,
 Edit: Changed hyphen to "No match" so that result is similar to Dave's post 

Re: Comparing cells

Posted: Fri Mar 14, 2008 11:49 pm
by JasonBourne
It does work!!!!
Thanks James, you've saved tons of my time!!!!

Re: Comparing cells

Posted: Fri Mar 14, 2008 11:56 pm
by James
No problem :)

Re: Comparing cells

Posted: Fri Feb 13, 2009 1:28 am
by specialsymbol
I'm right now trying to find a macro that does this for only one column- find identical values/strings in different cells of the same column. Doesn't work by now, but I'm still trying.

Ok, found the solution:
I got the german version of OpenOffice.Org.

The parameters are all in german, as IF becomes WENN, COUNTIF becomes VERGLEICH and so on.

This formula does work:

Code: Select all

=WENN(VERGLEICH(J1;$J$1:$J$65536;1)=ZEILE(J1);"";VERGLEICH(J1;$J$1:$J$65536))
It will deliver the last line where the identical value of the given line is found. When there is no identical line, nothing will be shown.

Re: Comparing cells

Posted: Fri Feb 13, 2009 3:58 am
by acknak
I'm probably missing something important, but doesn't this do what you want?