Multiple Answers For A Look up

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help.

I have two columns of data. One is a receipt number the other is an order
number. Basically, I want to create a lookup/index that will show me all
the order numbers associated to a receipt in one cell. i.e. Each receipt
can be for more than one order.

I've got this so far.

=INDEX(fgnpo!$A$2:$B$14001,MATCH(M2,fgnpo!$A$2:$A$14001,0),2)

This will return one of the values, but not all of them.

Here's part of the list.

Receipt Order


12668

26685
12669 26615
12670 26630
12671 26145
12671 26458
12671 26591



What I want is it to show

Receipt Order


12668 26685
12669 26615
12671 26630
12671 26145, 26458, 26591.


Is this possible?

Please help.

Many thanks

No spam! :o)
 
This old post may help with what you're looking for.

http://tinyurl.com/2lakm

However, it's example is for the values to be returned in a column.

You requested scenario is to have the data returned in a row, *across*
columns.

If this approach appeals to you, post back for a formula that can go *across
columns*.
--

Regards,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Help.

I have two columns of data. One is a receipt number the other is an order
number. Basically, I want to create a lookup/index that will show me all
the order numbers associated to a receipt in one cell. i.e. Each receipt
can be for more than one order.

I've got this so far.

=INDEX(fgnpo!$A$2:$B$14001,MATCH(M2,fgnpo!$A$2:$A$14001,0),2)

This will return one of the values, but not all of them.

Here's part of the list.

Receipt Order


12668

26685
12669 26615
12670 26630
12671 26145
12671 26458
12671 26591



What I want is it to show

Receipt Order


12668 26685
12669 26615
12671 26630
12671 26145, 26458, 26591.


Is this possible?

Please help.

Many thanks

No spam! :o)
 
Yep thought about that but there are just too many orders and receipts!

Surely there must be a way of getting multiple values from this type of
lookup. I can't believe I'm the only person to have this problem? Is there
a way of finding the first , second, third etc instances, because I was
thinking using a countif function would tell how many instances an order
appeared in the list.

thanks for the help any way I'll continue searching and report back soon!

No Spam :o)
 
Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(fgnpo!$A$1:$B$14001,SMALL(IF(fgnpo!$A$1:$A$14001=$M$2,ROW(fgnpo!
$A$1:$A$14001)),ROW(1:1)))

and copy down
 
RD Hmmmm across the columns that might work then I'll just put in a formula
to show all the cells in like =A1&","&B1&","&C1

Please could I have your formula.

Thanks

No Spam :o)
 
Hi
no. it would return the values starting with the matched value found in
the 'lowest' row. What kind of result did you get exactly. And whaty
formula have you used exactly?
 
RagDyer said:
This old post may help with what you're looking for.

http://tinyurl.com/2lakm

However, it's example is for the values to be returned in a column.

You requested scenario is to have the data returned in a row, *across*
columns.

No, the OP's requested scenario was for the data to be returned to a
single cell, separated by commas.

Alan Beban
 
no said:
Yep thought about that but there are just too many orders and receipts!

Surely there must be a way of getting multiple values from this type of
lookup. I can't believe I'm the only person to have this problem? Is there
a way of finding the first , second, third etc instances, because I was
thinking using a countif function would tell how many instances an order
appeared in the list.

thanks for the help any way I'll continue searching and report back soon!

No Spam :o)

The difficulty is not getting multiple values returned; it is returning
them to a single cell separated by commas.

Alan Beban
 
I haven't got all the data here but this is the info I've been working from:



Receipt Number Order Number
1 10
1 9
1 5
1 6
1 44
2 51
2 3
3 5
4 10
5 5
5 6
5 70
5 6
6 4
8 9
9 8
10 99
10 10
10 5
11 6
12 70
13 70
14 2
15 10
11 5
12 6
13 9
14 9
15 10



What I wanted was a list of Receipt Numbers with the orders next to it.
i.e.

Receipt Number Orders
1 10,9,5,6,44
2 51,3

etc.

As you can see from my earlier request I'm dealing with 14000 lines, I'm
only using the above data to get the thing working. I was thinking of
dumping it into access but the end users haven't got that programme, so it's
not an option!

The formula you gave me I've changed. The list of receipts starts in e1.
I'm confused by the row statement! All I get is REF!


=INDEX($A$1:$B$30,SMALL(IF($A$1:$A$30=$E$1,ROW($A$1:$A$30)),ROW(1:1)))


Thanks in advance

No Spam :o)
 
Alan Beban wrote...
...
The difficulty is not getting multiple values returned; it is
returning them to a single cell separated by commas.

Indeed. So OP needs a user-defined function since there's no built-i
Excel functionality that could do this (other than approaches involvin
many redundant cells for intermediate formulas). One such udf id give
in

http://www.google.com/[email protected]

Using that mcat function, if your data were in A2:B1000 and you wante
to concatenate the col B values corresponding to col A values equal t
the entry in cell X99, you could use the array formula

=SUBSTITUTE(TRIM(mcat(IF(A2:A1000="D",B2:B1000,"")&" "))," ",","
 
With the receipts starting in E1, and the order numbers starting in F1.

Your list of unique, sequential receipt numbers to look up starts in M1.

Enter this array formula in N1, but use a regular <Enter>:

=INDEX($F$1:$F$1000,SMALL(IF($E$1:$E$1000=M1,ROW($E$1:$E$1000),""),TRANSPOSE
(ROW($E$1:$E$1000))))

Now, if you read the old post that I linked to, you'll see that to insure
that *all* the order numbers are returned, you should have more formulas
going across the columns then you have order numbers, so that you receive at
least one error, telling you that *all* orders have been returned, and there
are none left, so that errors (#NUM!) are generated.
Otherwise, you'll never be *sure* that you have them all.

If you guess that you might have 10 orders, you should copy this formula
across 10 columns.
BUT ... DON'T REALLY COPY!

Select N1, but *DON'T* use the "fill handle".
Simply click and drag the *selection* across 10 columns.
This gives you the formula in N1, which is colored white, and the rest of
the selection, which is colored grey.
NOW, do a <F2>, and then <Ctrl> <Shift> <Enter>.

You now have your array formula copied across 10 columns, and with an
existing receipt number in M1, you should have all pertinant order numbers
displayed in the row, and hopefully, at least one #NUM! error, telling you
that you have *all* the existing order numbers returned.

You can now select the row of 10 array formulas, and copy them down in the
*regular* way, using the "fill handle".
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

RD Hmmmm across the columns that might work then I'll just put in a formula
to show all the cells in like =A1&","&B1&","&C1

Please could I have your formula.

Thanks

No Spam :o)
 
I tried to talk him into doing it my way.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

RagDyer said:
This old post may help with what you're looking for.

http://tinyurl.com/2lakm

However, it's example is for the values to be returned in a column.

You requested scenario is to have the data returned in a row, *across*
columns.

No, the OP's requested scenario was for the data to be returned to a
single cell, separated by commas.

Alan Beban
 
Hi
the formula should read:
=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$E$1,ROW($A$1:$A$30)),ROW(1:1)))

But this would give you only one element per cell. If you want to
combine them in one cell you'll need VBA. No way to do this without VBA
 
Confused now.

All I get is 10 in all cells? Bit bizarre! Was thinking I could use an
ifcount function to tell me how many times a receipt appears in the list.
Then in the adjoining cell I could use the small function to list out the
orders. I could then amalgamate all the adjoining cells into one. It
wouldn't be pretty but I think I can do that. I'll let you know how I get
on. Thnaks for all the help.

No Spam :o)
 
Here's the formula


{=IF($P2<COLUMN(Q2)-16,"",INDEX(fgnpo!$B$1:$B$13392,SMALL(IF((fgnpo!$A$1:$A$
13392=allforei!$M2),ROW(fgnpo!$A$1:$A$13392)),COLUMN(Q2)-16)))}

I'm using a countif to tell me how many orders there are.

All works ok all hidden so the end user only sees the list of orders.

Thanks for your help.

No Spam :o)
 
Back
Top