Hi,

Try this. I have tested it and it works fine.

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),COLUMNS($B$9:B9)),2)),"",INDEX( $A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)) ,COLUMNS($B$9:B9)),2))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
Hi
Taking a closer look @ the formula; it does look identical ..
what i'm using vs what has been provided in the link ..
but when i drag down the formula, the same number will occur multiple
times..
any idea why?

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

IF(ISERROR(INDEX('Closed To Date'!$A$1:$J$9926,SMALL(IF('Closed To
Date'!$A$1:$A$9926=$C$4,ROW('Closed To
Date'!$A$1:$A$9926)),ROW(1:1)),10)),
"-", INDEX('Closed To Date'!$A$1:$J$9926,SMALL(IF('Closed To
Date'!$A$1:$A$9926=$C$4,ROW('Closed To Date'!$A$1:$A$9926)),ROW(1:1)),10))

this is what i'm hoping to achieve in the results section
Ashish Value A Value B Value C

Not
Ashish Value A
Ashish Value B
Ashish Value C

Or
Ashish Value A Value B Value C
Ashish ] wants to remove
Ashish ] these 2 lines
--
nikko


"Ashish Mathur" wrote:

In my example Ashish appears multiple times (A1,A4,A7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
Hi
Having some issues with below formula; any assistance / help will be
appreciated

1) wish to return multiple unique values, however below forula is
returning
the results twice if the identifier appears twice in the raw data
spreadsheet... Any idea how to resolve this?
INDEX('Closed To Date'!$A$1:$K$9926,SMALL(IF('Closed To
Date'!$A$1:$A$9926=$C$4,ROW('Closed To
Date'!$A$1:$A$9926)),ROW(1:1)),10))

A1: K9926 - raw data
Col A - identifier, same identifier can appear twice or thrice in the
raw
data spreadsheet


--
nikko