Comparing cells

[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.

[James]

C3: =IF(A3=B3;A3;"")

Is this what you want?

[JasonBourne]

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...

[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):

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?

[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

[James]

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

Is that C1 or should it / could it be C2?

[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.

[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 .

[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!

[James]

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.

[James]

Does it matter whether there are repeated values e.g. if the value in B1 appears 3 times in Column A?

[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.

[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

[JasonBourne]

It does work!!!!
Thanks James, you've saved tons of my time!!!!

[James]

No problem

[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.

[acknak]

I'm probably missing something important, but doesn't this do what you want?