| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP(lookup_values) repost | Excel Worksheet Functions | |||
| duplicate table just values not formulas in the table | Charts and Charting in Excel | |||
| Match with 2 Lookup_Values used as search criteria. | Excel Worksheet Functions | |||
| Match with 2 Lookup_Values used as search criteria. | Excel Worksheet Functions | |||
| Match with 2 Lookup_Values used as search criteria. | Excel Worksheet Functions | |||
Linear Mode
