Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
An Array Lookup Formula
We need to write array formulae that return all occurrances of a lookup value
in a lookup array instead of just the first occurrance. For example: Col. A Apples Apples Pears Oranges Apples Oranges Plums Pears Col. B 2 bushels 4 bushels 1 bushel 2 bushels 3 bushels 2 bushels 5 bushels 3 bushels We want array formulae in columns D & E to pull all the pears and their corresponding quantities: Col. D Pears Pears Col. E 1 bushel 3 bushels Any suggestions?? Thanks in advance! -- Susan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
An Array Lookup Formula
Why not just filter on Pears?
"SueDot" wrote: We need to write array formulae that return all occurrances of a lookup value in a lookup array instead of just the first occurrance. For example: Col. A Apples Apples Pears Oranges Apples Oranges Plums Pears Col. B 2 bushels 4 bushels 1 bushel 2 bushels 3 bushels 2 bushels 5 bushels 3 bushels We want array formulae in columns D & E to pull all the pears and their corresponding quantities: Col. D Pears Pears Col. E 1 bushel 3 bushels Any suggestions?? Thanks in advance! -- Susan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
An Array Lookup Formula
Hi,
Unless there is a reason not to sort on column A, I think Jim has athe right idea. Is there a reason you can not sort this way, mayme the order of Colum A is important and needs to be preserved? Thanks, "SueDot" wrote: We need to write array formulae that return all occurrances of a lookup value in a lookup array instead of just the first occurrance. For example: Col. A Apples Apples Pears Oranges Apples Oranges Plums Pears Col. B 2 bushels 4 bushels 1 bushel 2 bushels 3 bushels 2 bushels 5 bushels 3 bushels We want array formulae in columns D & E to pull all the pears and their corresponding quantities: Col. D Pears Pears Col. E 1 bushel 3 bushels Any suggestions?? Thanks in advance! -- Susan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
An Array Lookup Formula
Ordinarily, filtering would work fine for a manual process. We are trying to
select multiple data sets from the same list data and return the answers to multiple locations, in an automated fashion. Also, the sheet on which the data list resides includes multiple combo boxes that get messed up after a filter is run, so we need another solution aside from "auto filter" to extract the necessary data. "Jim Thomlinson" wrote: Why not just filter on Pears? "SueDot" wrote: We need to write array formulae that return all occurrances of a lookup value in a lookup array instead of just the first occurrance. For example: Col. A Apples Apples Pears Oranges Apples Oranges Plums Pears Col. B 2 bushels 4 bushels 1 bushel 2 bushels 3 bushels 2 bushels 5 bushels 3 bushels We want array formulae in columns D & E to pull all the pears and their corresponding quantities: Col. D Pears Pears Col. E 1 bushel 3 bushels Any suggestions?? Thanks in advance! -- Susan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
An Array Lookup Formula
Hi,
This creates a copy of the sheet and sorts it on Col A, so it does not disturbe your original data. Hope it helps. Sub Macro1() Range("A1").Select Selection.CurrentRegion.Select ThisSheet = ActiveSheet.Name Sheets(ThisSheet).Select Sheets(ThisSheet).Copy Befo=Sheets(1) Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Thanks, "SueDot" wrote: Ordinarily, filtering would work fine for a manual process. We are trying to select multiple data sets from the same list data and return the answers to multiple locations, in an automated fashion. Also, the sheet on which the data list resides includes multiple combo boxes that get messed up after a filter is run, so we need another solution aside from "auto filter" to extract the necessary data. "Jim Thomlinson" wrote: Why not just filter on Pears? "SueDot" wrote: We need to write array formulae that return all occurrances of a lookup value in a lookup array instead of just the first occurrance. For example: Col. A Apples Apples Pears Oranges Apples Oranges Plums Pears Col. B 2 bushels 4 bushels 1 bushel 2 bushels 3 bushels 2 bushels 5 bushels 3 bushels We want array formulae in columns D & E to pull all the pears and their corresponding quantities: Col. D Pears Pears Col. E 1 bushel 3 bushels Any suggestions?? Thanks in advance! -- Susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i need help with a lookup and/or array type formula | Excel Worksheet Functions | |||
Array formula combined with Lookup | Excel Discussion (Misc queries) | |||
Array formula lookup | Excel Worksheet Functions | |||
Lookup Array Formula | Excel Worksheet Functions | |||
Two-Way Lookup Array Formula | Excel Worksheet Functions |