Lookup table with duplicate 'lookup_values'
 

Dear All,
Im trying to display data on separate worksheets depending on reference
numbers on a source worksheet SheetA. The number of rows of data for each
reference number VARIES, so I cannot just reference the position on worksheet
SheetA. VLOOKUP fails because it needs a unique reference number for each
row.

SheetA
Ref No. Site Locn etc
01 Site M Locn A ..
01 Site M Locn B ..
01 Site M Locn C ..
01 Site M Locn D ..
03 Site N Locn F ..
03 Site N Locn H ..
03 Site N Locn T ..
03 Site N Locn U ..
03 Site N Locn K ..
06 Site R Locn S ..
06 Site R Locn V ..
06 Site R Locn W ..
... .. ..

On SheetM, I wish to display all data associated with Ref 01.
On SheetN, I wish to display all data associated with Ref 03 etc.
Can anyone help?

I'm using Excel 2007.
Many thanks in anticipation

Lookup table with duplicate 'lookup_values'
 

One play which automates it using non-array formulas ..
(I'm using xl03, but think it should work just as well in your xl07)

Illustrated in this sample:
http://www.savefile.com/files/1600987
AutoCopy Lines by Key Col to Resp Sheets.xls

In sheet: WS1 (the "master")
Assume data in cols A to C, data in row2 down,
with the key col = col A (Ref no)
(Ref nos in col A are assumed text numbers: 01, 03, etc)

List the ref nos in K1 across, in text:
01,03,06, etc (can be in any order)

Put in K2: =IF($A2=K$1,ROW(),"")
Copy K2 across as far as required,
then fill down to cover the max expected extent of source data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named: 01
With the same col headers pasted into A1:C1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MA TCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV $1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN, WS1!$K$1:$IV$1,0)),0)))

Copy A2 across to C2, fill down to say, C10 (copy down by the smallest
possible range sufficient to cover the max expected extent for any Ref no.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)

Cols A to C will return only the lines for the Ref No: 01 from "WS1", with
all lines neatly bunched at the top

Now, just make a copy of the sheet: 01, rename it as the next Ref No: 03,
and you'd get all the results for the 03. Repeat the copy rename sheet
process to get the rest of the Ref No sheets (a one-time job). Adapt to suit
...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greggers" wrote:
Dear All,
Im trying to display data on separate worksheets depending on reference
numbers on a source worksheet SheetA. The number of rows of data for each
reference number VARIES, so I cannot just reference the position on worksheet
SheetA. VLOOKUP fails because it needs a unique reference number for each
row.

SheetA
Ref No. Site Locn etc
01 Site M Locn A ..
01 Site M Locn B ..
01 Site M Locn C ..
01 Site M Locn D ..
03 Site N Locn F ..
03 Site N Locn H ..
03 Site N Locn T ..
03 Site N Locn U ..
03 Site N Locn K ..
06 Site R Locn S ..
06 Site R Locn V ..
06 Site R Locn W ..
.. .. ..

On SheetM, I wish to display all data associated with Ref 01.
On SheetN, I wish to display all data associated with Ref 03 etc.
Can anyone help?

I'm using Excel 2007.
Many thanks in anticipation

Lookup table with duplicate 'lookup_values'
 

Max,
Thanks for a prompt response, it looks good.
However I'm rather confused by the action described as :
"
Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK
"
Are you inserting a new worksheet or using the Insert tab, in the top menu
bar?
Please explain

Thanks

Greggers

"Max" wrote:

One play which automates it using non-array formulas ..
(I'm using xl03, but think it should work just as well in your xl07)

Illustrated in this sample:
http://www.savefile.com/files/1600987
AutoCopy Lines by Key Col to Resp Sheets.xls

In sheet: WS1 (the "master")
Assume data in cols A to C, data in row2 down,
with the key col = col A (Ref no)
(Ref nos in col A are assumed text numbers: 01, 03, etc)

List the ref nos in K1 across, in text:
01,03,06, etc (can be in any order)

Put in K2: =IF($A2=K$1,ROW(),"")
Copy K2 across as far as required,
then fill down to cover the max expected extent of source data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named: 01
With the same col headers pasted into A1:C1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MA TCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV $1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN, WS1!$K$1:$IV$1,0)),0)))

Copy A2 across to C2, fill down to say, C10 (copy down by the smallest
possible range sufficient to cover the max expected extent for any Ref no.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)

Cols A to C will return only the lines for the Ref No: 01 from "WS1", with
all lines neatly bunched at the top

Now, just make a copy of the sheet: 01, rename it as the next Ref No: 03,
and you'd get all the results for the 03. Repeat the copy rename sheet
process to get the rest of the Ref No sheets (a one-time job). Adapt to suit
..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greggers" wrote:
Dear All,
Im trying to display data on separate worksheets depending on reference
numbers on a source worksheet SheetA. The number of rows of data for each
reference number VARIES, so I cannot just reference the position on worksheet
SheetA. VLOOKUP fails because it needs a unique reference number for each
row.

SheetA
Ref No. Site Locn etc
01 Site M Locn A ..
01 Site M Locn B ..
01 Site M Locn C ..
01 Site M Locn D ..
03 Site N Locn F ..
03 Site N Locn H ..
03 Site N Locn T ..
03 Site N Locn U ..
03 Site N Locn K ..
06 Site R Locn S ..
06 Site R Locn V ..
06 Site R Locn W ..
.. .. ..

On SheetM, I wish to display all data associated with Ref 01.
On SheetN, I wish to display all data associated with Ref 03 etc.
Can anyone help?

I'm using Excel 2007.
Many thanks in anticipation

Lookup table with duplicate 'lookup_values'
 

Are you inserting a new worksheet
or using the Insert tab, in the top menu bar?

In my xl03, it refers to the menu command "InsertName",
to define named ranges
(not insert worksheet)

When I "Click Insert Name Define",
it opens a "Define Name" dialog
where the subsequent steps as mentioned are then done

Hope that clarifies it sufficiently for you
(I don't have/know xl07, hope the steps aren't too different)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greggers" wrote:
Max,
Thanks for a prompt response, it looks good.
However I'm rather confused by the action described as :
"
Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK
"
Are you inserting a new worksheet or using the Insert tab, in the top menu
bar?
Please explain

Thanks

Greggers

Lookup table with duplicate 'lookup_values'
 

Max,
Thanks.
Found it under the 'Formulas' tab, 'Define Name' section.

How can I find the post giving this technique from Harlan?

Greg


"Max" wrote:

Are you inserting a new worksheet
or using the Insert tab, in the top menu bar?

In my xl03, it refers to the menu command "InsertName",
to define named ranges
(not insert worksheet)

When I "Click Insert Name Define",
it opens a "Define Name" dialog
where the subsequent steps as mentioned are then done

Hope that clarifies it sufficiently for you
(I don't have/know xl07, hope the steps aren't too different)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greggers" wrote:
Max,
Thanks for a prompt response, it looks good.
However I'm rather confused by the action described as :
"
Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK
"
Are you inserting a new worksheet or using the Insert tab, in the top menu
bar?
Please explain

Thanks

Greggers

Lookup table with duplicate 'lookup_values'
 

Ahh, good. Do spare a moment to click the "Yes" button below.

As for Harlan, think I've provided you with the gist of it. You could try
google search/browse his posts in Excel newsgroups.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greggers" wrote:
Max,
Thanks.
Found it under the 'Formulas' tab, 'Define Name' section.

How can I find the post giving this technique from Harlan?

Greg

Lookup table with duplicate 'lookup_values'
 

Max,
Most helpful.
Many thanks

Greggers

"Max" wrote:

Ahh, good. Do spare a moment to click the "Yes" button below.

As for Harlan, think I've provided you with the gist of it. You could try
google search/browse his posts in Excel newsgroups.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greggers" wrote:
Max,
Thanks.
Found it under the 'Formulas' tab, 'Define Name' section.

How can I find the post giving this technique from Harlan?

Greg

Lookup table with duplicate 'lookup_values'
 

Welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greggers" wrote in message
...
Max,
Most helpful.
Many thanks

Greggers