Lookup function that returns multiple values in column 2

  • Thread starter Thread starter Ronn
  • Start date Start date
R

Ronn

I am trying to determain on what days a number occures. The number ma
occure on mulitple days

Example:
1212 Mon
6256 Mon
2545 Mon
3598 Mon
4569 Tue
1212 Tue
3595 Tue
6256 Wed
4569 Wed
3595 Wed

Looking for Results:
1212 - Mon, Tue
6256 - Mon, Wed
2545 - Mon
3595 - Mon, Tue, Wed
4569 - Tue, Wed

Any help would be appreciated!

Thanks,

Ro
 
I am trying to determain on what days a number occures. The number may
occure on mulitple days

Example:
1212 Mon
6256 Mon
2545 Mon
3598 Mon
4569 Tue
1212 Tue
3595 Tue
6256 Wed
4569 Wed
3595 Wed
Click to expand...

You have a typo: looks like the 3598 should be 3595. Otherwise, your result
range below is wrong.
Looking for Results:
1212 - Mon, Tue
6256 - Mon, Wed
2545 - Mon
3595 - Mon, Tue, Wed
4569 - Tue, Wed
Click to expand...
...

You'd need a general concatenation operation. Unfortunately, Excel doesn't
provide that functionality built-in. You could download and install Laurent
Longre's MOREFUNC.XLL add-in, available from

http://longre.free.fr/english

That add-in provides an MCONCAT function which could work for this. If your
source table were named TBL, then you could use the following formulas with the
top-left result (1212) in cell E1.

E1:
=INDEX(TBL,1,1)

F1: [array formula]
=SUBSTITUTE(TRIM(MCONCAT(IF(INDEX(TBL,0,1)=E1,INDEX(TBL,0,2)," ")))," ",", ")

E2: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(E$1:E1,INDEX(TBL,0,1)),0),1)

Fill F1 into F2, then select E2:F2 and fill down as far as needed. These
formulas will evaluate to #N/A when all entries in TBL have been exhausted.
 
Hi

I've just downloaded the morefunc add-in and installed it. The functions I
have tried work OK except NBTEXT(). When I try this Excel hangs. No dialog
box explaining the function appears, there is just =NBTEXT() in the cell and
I have to crash Excel to get out. Any ideas?

Andy.

Harlan Grove said:
I am trying to determain on what days a number occures. The number may
occure on mulitple days

Example:
1212 Mon
6256 Mon
2545 Mon
3598 Mon
4569 Tue
1212 Tue
3595 Tue
6256 Wed
4569 Wed
3595 Wed
Click to expand...

You have a typo: looks like the 3598 should be 3595. Otherwise, your result
range below is wrong.
Looking for Results:
1212 - Mon, Tue
6256 - Mon, Wed
2545 - Mon
3595 - Mon, Tue, Wed
4569 - Tue, Wed
Click to expand...
..

You'd need a general concatenation operation. Unfortunately, Excel doesn't
provide that functionality built-in. You could download and install Laurent
Longre's MOREFUNC.XLL add-in, available from

http://longre.free.fr/english

That add-in provides an MCONCAT function which could work for this. If your
source table were named TBL, then you could use the following formulas with the
top-left result (1212) in cell E1.

E1:
=INDEX(TBL,1,1)

F1: [array formula]
=SUBSTITUTE(TRIM(MCONCAT(IF(INDEX(TBL,0,1)=E1,INDEX(TBL,0,2)," ")))," ",", ")

E2: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(E$1:E1,INDEX(TBL,0,1)),0),1)

Fill F1 into F2, then select E2:F2 and fill down as far as needed. These
formulas will evaluate to #N/A when all entries in TBL have been exhausted.
Click to expand...
 
I've just downloaded the morefunc add-in and installed it. The functions I
have tried work OK except NBTEXT(). When I try this Excel hangs. No dialog
box explaining the function appears, there is just =NBTEXT() in the cell and
I have to crash Excel to get out. Any ideas?
Click to expand...
...

No. I don't use NBTEXT, so I've never had this problem. I've just tested it
under Excel 97. It throws errors, specifically a dialog titled 'EXCEL.EXE' and
the text 'log10: SING error', but it doesn't hang Excel. You could write Laurent
Longre and report this. In the meantime, don't use NBTEXT.
 
Hi Harlan,

This is another example of an inbuilt feature of Excel being ignored
in favour of an external add-in and/or the use of complex expressions.

Pivot Tables.

Well worth a try.

eg.

Event Day Total
1212 Mon 1
Tue 1
1212 Total 2
2545 Mon 1
2545 Total 1
3595 Tue 1
Wed 1
3595 Total 2
3598 Mon 1
3598 Total 1
4569 Tue 1
Wed 1
4569 Total 2
6256 Mon 1
Wed 1
6256 Total 2
Grand Total 10


As may be seen from this simple cut, the output produces frequencies
for each day as well.


Explore and Enjoy

David

"Ignore the question:
Seek a better solution to the problem"
 
David Byrne said:
This is another example of an inbuilt feature of Excel being ignored
in favour of an external add-in and/or the use of complex expressions.
Click to expand...

No, this is another example of David Byrne wanting an OP to abandon the OP's
own specs in order to force the OP to use David Byrne's favorite solution to
anything appearing to involve consolidation of any kind: pivot tables.

Reread the OP. The desired result is the accumulation of the dates
corresponding to the given event, seemingly concatenated into a single cell
for each event. Pivot tables can't do this.

Unlike you, I generally assume OPs know what they want.
 
Follow-up.

While it may come as a shock to some, some people use Excel for text
processing. In such instances, pivot tables are generally useless.
 
Harlan Grove said:
No, this is another example of David Byrne wanting an OP to abandon the OP's
own specs in order to force the OP to use David Byrne's favorite solution to
anything appearing to involve consolidation of any kind: pivot tables.

Reread the OP. The desired result is the accumulation of the dates
corresponding to the given event, seemingly concatenated into a single cell
for each event. Pivot tables can't do this.

Unlike you, I generally assume OPs know what they want.
Click to expand...

Great reponse Harlan.

I re-read the opening line:
"I am trying to determain on what days a number occures."


Your statement is:
"The desired result is the accumulation of the dates corresponding to
the given event, seemingly concatenated into a single cell for each
event."

and:

"Unlike you, I generally assume OPs know what they want."

OK

I read what the user requested, ie to detemine on what days a number
occurs.

Result achieved; EXACTLY what was requested..

NOWHERE is there even the slightest hint that "concatenation into a
single cell for each event" was required or expected. That was YOUR
input.

The user CLEARLY stated in just ELEVEN words what they wanted.

You then try another of your standard tactics

"No, this is another example of David Byrne wanting an OP to abandon
the OP's
own specs in order to force the OP to use David Byrne's favorite
solution to
anything appearing to involve consolidation of any kind: pivot tables"

Again...

The user had no "specs" to be abandoned, simply a desired outcome.

Your attempt to belittle my straight forward reponse to a simple
request merely re-inforces my belief that you should lighten up.
 
David Byrne said:
NOWHERE is there even the slightest hint that "concatenation into a
single cell for each event" was required or expected. That was YOUR
input.
Click to expand...
....

From the OP:

"Looking for Results:
1212 - Mon, Tue
6256 - Mon, Wed
2545 - Mon
3595 - Mon, Tue, Wed
4569 - Tue, Wed"

How do you interpret this?

Given the task at hand, this is a much more readable output format that what
you created with pivot tables. Even if you had put event codes down the left
and days across the top, the format above would still be clearer.
 
Back
Top