Lookup table with duplicate 'lookup_values'

G

Greggers

Dear All,
I’m 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
 
M

Max

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,MATCH(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
...

---
 
G

Greggers

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 said:
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,MATCH(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
..

---
Greggers said:
Dear All,
I’m 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
Click to expand...
Click to expand...
 
M

Max

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

In my xl03, it refers to the menu command "Insert>Name",
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)

---
Greggers said:
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
Click to expand...
 
G

Greggers

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

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

Greg


Max said:
Are you inserting a new worksheet
or using the Insert tab, in the top menu bar?
Click to expand...

In my xl03, it refers to the menu command "Insert>Name",
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)

---
Greggers said:
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
Click to expand...
Click to expand...
 
M

Max

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.
 
G

Greggers

Max,
Most helpful.
Many thanks

Greggers

Max said:
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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cross Reference Vertical and Horizontal for Data 4
Lookup: Multiple Occurances 5
Empty Cells 5
Lookup from Static List/Array 2
Sum the number of days between dates by Month 16
Possible getpivotdata with offset? 1
Query to create new table format 4
Totaling columns 4

Top