|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
February 17th, 2006, 05:13 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup/multiple
This is a small snapshot of what i have in 2 sheets:-
Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help. 
|
|
#2
February 17th, 2006, 05:38 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Hi!
Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2  $11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help.
|
|
#3
February 17th, 2006, 05:46 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
That's an array formula.
It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2 $11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help.
|
|
#4
February 17th, 2006, 05:47 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Hi,
I want the Names returned in rows like....... dept2 sales place1............name2 name7 name9 I cant give the end user the option of filtering for each instance of an item since the sheets have 1000s of records. Meanwhile, I will try to work with the formula, Thanks "Biff" wrote: Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2 $11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help.
|
|
#5
February 17th, 2006, 06:18 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Ok, just post back if you get stuck.
An error trap will make that formula kind of "long" (almost twice as long). Biff "Minerva" wrote in message ... Hi, I want the Names returned in rows like....... dept2 sales place1............name2 name7 name9 I cant give the end user the option of filtering for each instance of an item since the sheets have 1000s of records. Meanwhile, I will try to work with the formula, Thanks "Biff" wrote: Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2 $11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help.
|
|
#6
February 17th, 2006, 06:41 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Thanks a lot Biff......otherwise i had ended up creating 2 pivots with names
showing in the other! However, looks like i need to change the last parameter for each combination of the 3(department/category/place)...i.e........ROWS($1:1) because when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. Thanks anyways.......it helped me ease my work to some extent. -------------------------------------------------------------------------- "Biff" wrote: That's an array formula. It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2 $11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help.
|
|
#7
February 17th, 2006, 06:58 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
when i drag this to the next combination, i need to change this from
ROWS($1:4) back to ROWS($1:1) in this example case. That's why you should (if you're able) have the names returned across a row rather than down a column: name2..........name7..........name9 Biff "Minerva" wrote in message news Thanks a lot Biff......otherwise i had ended up creating 2 pivots with names showing in the other! However, looks like i need to change the last parameter for each combination of the 3(department/category/place)...i.e........ROWS($1:1) because when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. Thanks anyways.......it helped me ease my work to some extent. -------------------------------------------------------------------------- "Biff" wrote: That's an array formula. It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2 $11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help.
|
|
#8
February 17th, 2006, 07:11 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
To return across the row:
=INDEX(Sheet2!$D$2:$D$11,SMALL(IF((Sheet2!$A$2:$A$ 11=$A2)*(Sheet2!$B$2:$B$11=$B2)*(Sheet2!$C$2:$C$11 =$C2),ROW(Sheet2!D$2 $11)-ROW(Sheet2!D$2)+1),COLUMNS($A:A)))Biff "Biff" wrote in message ... when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. That's why you should (if you're able) have the names returned across a row rather than down a column: name2..........name7..........name9 Biff "Minerva" wrote in message news Thanks a lot Biff......otherwise i had ended up creating 2 pivots with names showing in the other! However, looks like i need to change the last parameter for each combination of the 3(department/category/place)...i.e........ROWS($1:1) because when i drag this to the next combination, i need to change this from ROWS($1:4) back to ROWS($1:1) in this example case. Thanks anyways.......it helped me ease my work to some extent. -------------------------------------------------------------------------- "Biff" wrote: That's an array formula. It MUST be entered using the key combo of CTRL,SHIFT,ENTER. Biff "Biff" wrote in message ... Hi! Where do want the names returned? Across a row? Down a column? And what about the dupes? Do you want the same duplicated names for each instance of dept2 sales place1? Here's a basic formula that will do what you want: =INDEX(Sheet2!D$2 $11,SMALL(IF((Sheet2!A$2:A$11=A $2)*(Sheet2!B$2:B$11=B$2)*(Sheet2!C$2:C$11=C$2),RO W(Sheet2!D$2$11)-ROW(Sheet2!D$2)+1),ROWS($1:1)))Assuming that each table starts in cell A2 of the respective sheets. When drag copied down will return: name2 name7 name9 #NUM! This is just the "basic" formula, no error checking/trapping. Have you considered filtering? Biff "Minerva" wrote in message ... This is a small snapshot of what i have in 2 sheets:- Department/Category/Place-----------(3 similar columns for both sheets) dept2 sales place1 dept3 support place2 dept1 backend place3 dept2 sales place1 dept3 support place2 dept2 sales place1 dept1 backend place3 dept1 backend place3 dept1 backend place3 dept3 support place2 ---------------------------------- and Name column in sheet-2 dept1 backend place3 name1 dept2 sales place1 name2 dept1 backend place3 name3 dept1 backend place3 name4 dept3 support place2 name5 dept1 backend place3 name6 dept2 sales place1 name7 dept3 support place2 name8 dept2 sales place1 name9 dept3 support place2 name10 ------------------- as you can see, there are multiple entries of the same item for each column. I need to look up items (in table2) that match (with table1) the Department & Category & Place and get Names........for example, dept2 & sales & place1 (of table1) when looked up brings up three Names.........name2, name7, name9. I found some discussions around the topic, but am unable to understand the formula, please help in deriving the same. Many Thanks for your help.
|
|
#9
February 17th, 2006, 07:12 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
Here's a quick sample, implemented with a slight tweak to Biff's array to
enable copy across (horizontally) to pull the multiple names out, and ... with an error trap slapped on to return neat blanks: "" instead of #NUM! errors: http://cjoint.com/?crigMaFlaD Minerva_wks_1.xls (Just copy across from D2 in sheet: X by the smallest possible extent large enough to cater for the max number of names expected per combo. Admit I did shorten the formulas a little by using sheetnames: X, Y instead of: Sheet1, Sheet2 g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---
|
|
#10
February 17th, 2006, 07:27 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup/multiple
I did shorten the formulas a little by using
sheetnames: X, Y instead of: Sheet1, Sheet2 g) Those should be mandatory sheet names! Biff "Max" wrote in message ... Here's a quick sample, implemented with a slight tweak to Biff's array to enable copy across (horizontally) to pull the multiple names out, and ... with an error trap slapped on to return neat blanks: "" instead of #NUM! errors: http://cjoint.com/?crigMaFlaD Minerva_wks_1.xls (Just copy across from D2 in sheet: X by the smallest possible extent large enough to cater for the max number of names expected per combo. Admit I did shorten the formulas a little by using sheetnames: X, Y instead of: Sheet1, Sheet2 g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
