how to return mulitple corresponding values

[T. Valko]

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then, based on the sample data you posted...

Assuming this data is in the range A2:B12. You have the unique groups listed
in the range E2:E5.

Enter this array formula** in F2 and copy down to F5:

=SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12,"")&" "))," ","^")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Melanie]

Thanks! That worked! Although, for some reason, it wouldn't let me reference
the unique list (col E) from a pivot table in another worksheet so I
referenced the unique list using = in the same worksheet and for some reason
that worked(?). I'm just happy it works! I've been working on this on-and-off
for a week.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[nikko]

Hi Biff

Using below formula and its returning the values not in sequence

=INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities
Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1)))

is there a way to return the corresponding values in sorted order i.e. w the
most recent activity showing up first?

Thanks you!

 

[T. Valko]

Refresh my memory.

--
Biff
Microsoft Excel MVP

Hi Biff

Using below formula and its returning the values not in sequence

=INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities
Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1)))

is there a way to return the corresponding values in sorted order i.e. w
the
most recent activity showing up first?

Thanks you!

 

[nikko]

Sorry .. didnt get wad you mean...
--
nikko

Refresh my memory.

 

[T. Valko]

Explain what you're wanting to do.

I know what the formula does but what do you mean by: sorted order i.e.the
most recent activity showing up first?

That sounds to me like you're looking for dates?

 

[nikko]

the returned values are dates format... & i jus want to return the dates from
oldest to newest...

 

[T. Valko]

This will extract the dates in ascending order, oldest to newest, that meet
the condition.

I'm leaving out the sheet name so be sure to add it to your formula.

Let's assume you want the results starting in cell X1.

Array entered** :

=SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1))

Copy down until you get #NUM! errors meaning all applicable data has been
extracted.

If you want an error trap:

=IF(ROWS(X$1:X1)<=COUNTIF(A$1:A$13,J$2),SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"")

Copy down until you get blanks meaning all applicable data has been
extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[nikko]

Thanks ... it works perfectly.

Now with the dates returned in ascending order, i'm trying to return the
related activity comments and contact person. however if there are 2 similar
dates with different contact person, the formula does not seen to work ..

desired results
Date Contact Person
25-Mar Peter
25-Mar Alan

formula is returning
Date Contact Person
25-Mar Peter
25-Mar Peter

Formula - (INDEX('Data3-Opp Activities
Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities
Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp Activities
Info'!$C$1:$C$10000)),ROW(1:1)),7)))

 

[T. Valko]

if there are 2 similar dates with different contact person

Ok, it gets complicated!

Here's a small sample file that demonstrates this.

xNikko.xls 17kb

http://cjoint.com/?efhZw8gEFM

 

[nikko]

Thanks ... works perfectly!!!
--
nikko

Ok, it gets complicated!

Here's a small sample file that demonstrates this.

xNikko.xls 17kb

http://cjoint.com/?efhZw8gEFM

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[jbf]

Hi Biff,

I've read quite a few of the posts on returning multiple corresponding
values but everything is basically over my head. I don't have any training or
exp in this stuff. I've tried to replicate the example at:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx?pid=CL100570551033#Remove

I was able to return only one value for ashish 234. I couldn't get the
formula to return 534 or 834. Here's the formula I have.

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$A$7)),ROW(1:1)),2)

Even if I get this right, I'm not sure if it is what I need. I have a data
set where one value can have multiple corresponding values. It's not a 1:1
relationship. I want all values returned but only the distinct values. Here's
an example of the data.

Acct Line
1010 12
1010 12
1010 12
1010 12
1010 12
1010 5
1010 7
1010 5
1020 22
1020 13
1030 10

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

Can you help, please?

 

[T. Valko]

This is what I want the result to look like.

1010 12
5
7
1020 22
13
1030 10

I can get the result to look like this:

1010...5...7...12
1020...13...22
1030...10

Do you want to go with that?

 

[jbf]

Yes...that would be great.

I can get the result to look like this:

1010...5...7...12
1020...13...22
1030...10

Do you want to go with that?

 

[T. Valko]

OK....

Assume your data is in the range A2:B12.

Create these named ranges:
Insert>Name>Define
Name: Acct
Refers to: =$A$2:$A$12

Name: Line
Refers to: =$B$2:$B$12

List the unique Acct numbers in a range of cells. Assume they're in the
range D24. You can do this several ways: type them in manually, use a
formula, or use the advanced filter to extract the uniques and copy to a new
location.

Enter this array formula** in E2:

=MIN(IF(Acct=D2,Line))

Copy down to D4

Enter this array formula** in F2:

=IF(COLUMNS($E2:F2)<=COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line)),MIN(IF((Acct=$D2)*(Line>E2),Line)),"")

Copy down to F4.

Select the range F2:F4 and copy across until you get a solid column full of
blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This portion of the formula can be calculation intensive if you have a large
amount of data:

COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line))

You can place this as a separate formula in a helper cell and then refer to
that cell instead of using it in every formula. That formula returns the
count of unique line numbers per acct. This is used as an error trap so that
when the formula is copied across and as it "runs out of data" it will
return a blank rather than an error.

 

[Bernd P]

Hello,

Another approach:
Select a range of 6 rows and 2 columns and array-enter:
=Pfreq(A1:A11,B1:B11)

Pfreq you can find here:
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd

 

[:)]

Hi Biff,

I am following this thread and use your example. My formula is
{=INDEX(Customer,SMALL(IF(Revenue*$C$3>Criteria,ROW(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))}

I got what I want, but now I face a new problem. I want those values whose
status is not 'under negotiation'. I tried
{=INDEX(Customer,SMALL(IF((Status<>"under
negotiation")*(Revenue*$C$3>Criteria),ROW(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))}

But was return #N/A. Is there anything I can do to rectify this?

Thanks

 

[:)]

I managed to solve the issue. Thanks

Hi Biff,

I am following this thread and use your example. My formula is:
{=INDEX(Customer,SMALL(IF(Revenue*$C$3>Criteria,ROW(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))}

I got what I want, but now I face a new problem. I want those values whose
status is not 'under negotiation'. I tried
{=INDEX(Customer,SMALL(IF((Status<>"under
negotiation")*(Revenue*$C$3>Criteria),ROW(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))}

But was return #N/A. Is there anything I can do to rectify this?

Thanks