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

Hi,

Try my solution at the following link

http://office.microsoft.com/en-us/ex...CL100570551033

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

Hi

Ur data only has Ashish appearing once..
If Ashish appears twice.. the return result is going to have 2 lines showing
the name Ashish.. and i only want it to return this value once.

is there a way to achieive it? Please let me know.

Thanks!
--
nikko


"Ashish Mathur" wrote:

Hi,

Try my solution at the following link

http://office.microsoft.com/en-us/ex...CL100570551033

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

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

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

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

Hello again;
i tried using below formulas but when it gets dragged down the column, the
return results dun look right. are the formulas i'm using incorrect?

IF(ISERROR(INDEX('Closed To Date'!$A$1:$M$9908,SMALL(IF('Closed To
Date'!$A$1:$A$9908=$C$4,ROW('Closed To Date'!$A$1:$A$9908)),COLUMNS('Closed
To Date'!K2:K2)),11)), "-", INDEX('Closed To
Date'!$A$1:$M$9908,SMALL(IF('Closed To Date'!$A$1:$A$9908=$C$4,ROW('Closed To
Date'!$A$1:$A$9908)),COLUMNS('Closed To Date'!K2:K2)),11))


IF(ISERROR(INDEX('Closed To Date'!$A$1:$M$9908,SMALL(IF('Closed To
Date'!$A$1:$A$9908=$C$4,ROW('Closed To
Date'!$A$1:$A$9908)),COLUMNS($B$9:B9)),11)), "-", INDEX('Closed To
Date'!$A$1:$M$9908,SMALL(IF('Closed To Date'!$A$1:$A$9908=$C$4,ROW('Closed To
Date'!$A$1:$A$9908)),COLUMNS($B$9:B9)),11))


Below is my Raw Data
Col A C D K
Owner Name Prodt Family Prodt Value 18 characters identifier
Peter Lic 10000 ABCD
Peter Lic 5000 ABCD
Peter Mnt 5000 ABCD
Peter Mnt 3000 ABCD
Peter Consulting 4000 ABCD

here's what i'm trying to achieve in the Results section:
18 Characters identifier Lic Value Mnt Value
Consulting Value
ABCD 15,000 8,000
4,000

My formulas however are returning below; w 4 rows of duplicate data:
Results section
18 Characters identifier Lic Value Mnt Value
Consulting Value
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000

how can i achieve only 1 row of ABCD ?

Thanks in advance for any help!
--
nikko


"Ashish Mathur" wrote:

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

Hi,

Upload the file somewhere and paste a link here.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
Hello again;
i tried using below formulas but when it gets dragged down the column, the
return results dun look right. are the formulas i'm using incorrect?

IF(ISERROR(INDEX('Closed To Date'!$A$1:$M$9908,SMALL(IF('Closed To
Date'!$A$1:$A$9908=$C$4,ROW('Closed To
Date'!$A$1:$A$9908)),COLUMNS('Closed
To Date'!K2:K2)),11)), "-", INDEX('Closed To
Date'!$A$1:$M$9908,SMALL(IF('Closed To Date'!$A$1:$A$9908=$C$4,ROW('Closed
To
Date'!$A$1:$A$9908)),COLUMNS('Closed To Date'!K2:K2)),11))


IF(ISERROR(INDEX('Closed To Date'!$A$1:$M$9908,SMALL(IF('Closed To
Date'!$A$1:$A$9908=$C$4,ROW('Closed To
Date'!$A$1:$A$9908)),COLUMNS($B$9:B9)),11)), "-", INDEX('Closed To
Date'!$A$1:$M$9908,SMALL(IF('Closed To Date'!$A$1:$A$9908=$C$4,ROW('Closed
To
Date'!$A$1:$A$9908)),COLUMNS($B$9:B9)),11))


Below is my Raw Data
Col A C D K
Owner Name Prodt Family Prodt Value 18 characters identifier
Peter Lic 10000 ABCD
Peter Lic 5000 ABCD
Peter Mnt 5000 ABCD
Peter Mnt 3000 ABCD
Peter Consulting 4000 ABCD

here's what i'm trying to achieve in the Results section:
18 Characters identifier Lic Value Mnt Value
Consulting Value
ABCD 15,000 8,000
4,000

My formulas however are returning below; w 4 rows of duplicate data:
Results section
18 Characters identifier Lic Value Mnt Value
Consulting Value
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000
ABCD 15,000 8,000
4,000

how can i achieve only 1 row of ABCD ?

Thanks in advance for any help!
--
nikko


"Ashish Mathur" wrote:

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