refer to the link ...
http://www.editgrid.com/user/judygoh0325/ms_discussion_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
:
Please be very specific about the worksheet, cell and the exact
problem.
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
sorry, please use this link instead
http://www.editgrid.com/user/judygoh0325/ms_discussion_board_2
--
nikko
:
here;s the link
http://www.editgrid.com/user/judygoh0325/MS_discussion_board
--
nikko
:
Hi,
Upload the file somewhere and paste a link here.
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
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
:
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
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
:
In my example Ashish appears multiple times (A1,A4,A7)
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
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
