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

here;s the link

http://www.editgrid.com/user/judygoh...scussion_board
--
nikko


"Ashish Mathur" wrote:

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

sorry, please use this link instead
http://www.editgrid.com/user/judygoh...ussion_board_2
--
nikko


"nikko" wrote:

here;s the link

http://www.editgrid.com/user/judygoh...scussion_board
--
nikko


"Ashish Mathur" wrote:

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

Please be very specific about the worksheet, cell and the exact problem.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
sorry, please use this link instead
http://www.editgrid.com/user/judygoh...ussion_board_2
--
nikko


"nikko" wrote:

here;s the link

http://www.editgrid.com/user/judygoh...scussion_board
--
nikko


"Ashish Mathur" wrote:

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

refer to the link ...
http://www.editgrid.com/user/judygoh...ussion_board_3

@ summary tab, opp_id column,
formula used - array(index('raw data'!$A$1:$M$9908,small(if('raw
data'!$A$1:$A$9908=$C$4,row('raw data'!$A$1:$A$9908)),columns('raw
data'!$B$2:B2)),2))

but when this is being dragged down the column, the returned ids are all of
similar values

if you compare this against the raw data, there shd be 3 lines reflected in
opp_id column; namely

Line 2 in raw data
Line 3 in raw data
Line 6 in raw data
--
nikko


"Ashish Mathur" wrote:

Please be very specific about the worksheet, cell and the exact problem.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
sorry, please use this link instead
http://www.editgrid.com/user/judygoh...ussion_board_2
--
nikko


"nikko" wrote:

here;s the link

http://www.editgrid.com/user/judygoh...scussion_board
--
nikko


"Ashish Mathur" wrote:

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

Hi,

You need to copy this to the right.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
refer to the link ...
http://www.editgrid.com/user/judygoh...ussion_board_3

@ summary tab, opp_id column,
formula used - array(index('raw data'!$A$1:$M$9908,small(if('raw
data'!$A$1:$A$9908=$C$4,row('raw data'!$A$1:$A$9908)),columns('raw
data'!$B$2:B2)),2))

but when this is being dragged down the column, the returned ids are all
of
similar values

if you compare this against the raw data, there shd be 3 lines reflected
in
opp_id column; namely

Line 2 in raw data
Line 3 in raw data
Line 6 in raw data
--
nikko


"Ashish Mathur" wrote:

Please be very specific about the worksheet, cell and the exact problem.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
sorry, please use this link instead
http://www.editgrid.com/user/judygoh...ussion_board_2
--
nikko


"nikko" wrote:

here;s the link

http://www.editgrid.com/user/judygoh...scussion_board
--
nikko


"Ashish Mathur" wrote:

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

it doesnt work ..

when the formula gets drag to the right ... it will return all 5 values;
including the duplicate ones i.e. total of 5 lines in the raw data, it will
return all the 5 values
but i only want it to return the unique values, 3 lines

--
nikko


"Ashish Mathur" wrote:

Hi,

You need to copy this to the right.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
refer to the link ...
http://www.editgrid.com/user/judygoh...ussion_board_3

@ summary tab, opp_id column,
formula used - array(index('raw data'!$A$1:$M$9908,small(if('raw
data'!$A$1:$A$9908=$C$4,row('raw data'!$A$1:$A$9908)),columns('raw
data'!$B$2:B2)),2))

but when this is being dragged down the column, the returned ids are all
of
similar values

if you compare this against the raw data, there shd be 3 lines reflected
in
opp_id column; namely

Line 2 in raw data
Line 3 in raw data
Line 6 in raw data
--
nikko


"Ashish Mathur" wrote:

Please be very specific about the worksheet, cell and the exact problem.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
sorry, please use this link instead
http://www.editgrid.com/user/judygoh...ussion_board_2
--
nikko


"nikko" wrote:

here;s the link

http://www.editgrid.com/user/judygoh...scussion_board
--
nikko


"Ashish Mathur" wrote:

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

Hi,

Please refer to question 8 on the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
it doesnt work ..

when the formula gets drag to the right ... it will return all 5 values;
including the duplicate ones i.e. total of 5 lines in the raw data, it
will
return all the 5 values
but i only want it to return the unique values, 3 lines

--
nikko


"Ashish Mathur" wrote:

Hi,

You need to copy this to the right.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
refer to the link ...
http://www.editgrid.com/user/judygoh...ussion_board_3

@ summary tab, opp_id column,
formula used - array(index('raw data'!$A$1:$M$9908,small(if('raw
data'!$A$1:$A$9908=$C$4,row('raw data'!$A$1:$A$9908)),columns('raw
data'!$B$2:B2)),2))

but when this is being dragged down the column, the returned ids are
all
of
similar values

if you compare this against the raw data, there shd be 3 lines
reflected
in
opp_id column; namely

Line 2 in raw data
Line 3 in raw data
Line 6 in raw data
--
nikko


"Ashish Mathur" wrote:

Please be very specific about the worksheet, cell and the exact
problem.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
sorry, please use this link instead
http://www.editgrid.com/user/judygoh...ussion_board_2
--
nikko


"nikko" wrote:

here;s the link

http://www.editgrid.com/user/judygoh...scussion_board
--
nikko


"Ashish Mathur" wrote:

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