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
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:
- Drag that down the entire sheet
- Copy column C and paste values into column D
- 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!

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

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?