On Feb 19, 2:37 pm, "Ragdyer" wrote:
The formula I suggested will do that.

Have you tried it in that scenario?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------"Steve" wrote in message

oups.com...
On Feb 19, 11:49 am, "RagDyeR" wrote:





Try this:

=IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))=1,LOOKUP(2,1/(A1:E10),**A1:E1),"Mismatch")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Steve" wrote in message

roups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:

=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1***,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

roups.com...

Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Hi,

That did work better, but now I have those situations where there is
only one entry in the entire row. Where this is the case, I need that
entry to appear in the adjacent cell in 6 th column. I apologize for
my example not being that clear. I threw it together in a hurry this
morning without considering all of the possible scenerios.

Regards,

Steve- Hide quoted text -

- Show quoted text -


It seems to work when using the Fruits I have in my example, however
my spreadsheet list names of customers, like "ALCATEL-LUCENT",
"NOKIA", "NORTEL", "MOTOROLA", etc... For some reason it won't work
when I apply it to the customer names.....


Regards,

Steve