Returning all instances

[okanem]

I have a problem I know someone has an easy answer too.

Sheet 1 contains the following

A1 : A6 all have ref1 entered in them
A7 : A12 all have ref2 entered in them

B1 : B6 list ref1 unique id's 60001 to 60006
B7 : B12 list ref2 unique id's 70001 to 70006

On sheet 2 I want to be able to type in either ref1 or ref2 into Cell
A1 making the entire list of unique id's relating to that ref appear in
B1 to B whatever it takes to list them all.

There are a lot of ref's and multiple unique ID's per ref.

Tried using the vlookup but only the first unique ref relating to the
ref I entered gets returned.

Hope someone can help

Eternally grateful
Okanem

 

[davesexcel]

If the ranges are constant you could name them
highlite the range that is ref1
goto=>insert=>name=>define
name the range, it can only be one word for now call it -ref1- at the
bottom of the box the range should be what you highlited if not make it
so, you can always go back in there and adjust your range.
Now in sheet two goto cell A1
click on your macro recorder name your macro ref1 and enter
goto cell A1 and type in =ref1
select the cell you want to be at next
stop recording
goto the forms toolbox and hit the button icon and then place a button
on the spreadsheet, assign macro ref1 to that button, and throw a name
on the button
do the same steps for your other ranges

 

[okanem]

Thanks dave,

unfortunately the ref's wont be constant, I used ref1, ref2 for
simplicity, the ref number which has multiple unique id's (upto 20
id's) linked to it is a 14 digit alpha numeric code which the end user
will type into A1 in sheet 2 and hopefully have all the unique id's
returned in B1 to B whatever.

Any other solutions anyone?

Okanem

 

[davesexcel]

Oh Okay,
so you want a value entered into A1 and b1,b2,b3.... will correspond t
A1
Where in the list will A1 be referring to?
will it be at the left or the right of your tabl

 

[Domenic]

On Sheet2, enter the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, in B1 and copy down:

=IF(ROWS(B$1:B1)<=COUNTIF(Sheet1!$A$1:$A$12,$A$1),INDEX(Sheet1!$B$1:$B$12
,SMALL(IF(Sheet1!$A$1:$A$12=$A$1,ROW(Sheet1!$A$1:$A$12)-ROW(Sheet1!$A$1)+
1),ROWS(B$1:B1))),"")

If you find the above solution a little too slow, a different approach
can be used involving helper columns which may be more efficient.

Hope this helps!

 

[Max]

Any other solutions anyone?

Try this non-array response to your multi-post in .misc
a couple of days ago (pl do not multi-post)

----------

One play ..

In Sheet2, A1 will house, eg: ref1

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(Sheet1!B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1:
=IF(Sheet1!A1="","",IF(Sheet1!A1=$A$1,ROW(),""))

Select B1:C1, copy down to say, C20, to cover the max
expected extent of returns for any reference entered in A1

Col B returns the required results, all neatly bunched at the top
(Hide away col C, if desired)

 

[Ron Rosenfeld]

I have a problem I know someone has an easy answer too.

Sheet 1 contains the following

A1 : A6 all have ref1 entered in them
A7 : A12 all have ref2 entered in them

B1 : B6 list ref1 unique id's 60001 to 60006
B7 : B12 list ref2 unique id's 70001 to 70006

On sheet 2 I want to be able to type in either ref1 or ref2 into Cell
A1 making the entire list of unique id's relating to that ref appear in
B1 to B whatever it takes to list them all.

There are a lot of ref's and multiple unique ID's per ref.

Tried using the vlookup but only the first unique ref relating to the
ref I entered gets returned.

Hope someone can help

Eternally grateful
Okanem

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

With your ref num list named "RefList" and your Serial Number column named
"SerNums", this formula should work.

You must enter this formula as an ARRAY formula in a number of rows which is at
least as great as the number of serial numbers for any particular ref number;
and not greater than the total number of rows in RefList.

Sheet2!B1: =INDEX(UNIQUEVALUES(IF($A$1=RefList,SerNums,""),1),ROWS($1:2))

Then, hold down <ctrl><shift> while hitting <enter>. Excel will place braces
{...} around the formula.

Copy/Drag down the required number of rows.


--ron