Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Hi
I have multiple columns containing different values, p/n, description, price qty... p/n desc price A B C D 23 valve 1.00 1 1 1 32 pump 2.50 1 1 2 3 plc 6.75 1 1 1 45sd sw 9.00 1 1 Where A,B,C,D are the complete units, the numbers below are the total of each item on the left in the unit. (ie. P/N A has 1 valve and 1 plc) I need to be able to ref a part number and list ONLY every item on that unit. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
E is a count of A thru D, if E = 4 then completed.
HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "rikow" wrote: Hi I have multiple columns containing different values, p/n, description, price qty... p/n desc price A B C D 23 valve 1.00 1 1 1 32 pump 2.50 1 1 2 3 plc 6.75 1 1 1 45sd sw 9.00 1 1 Where A,B,C,D are the complete units, the numbers below are the total of each item on the left in the unit. (ie. P/N A has 1 valve and 1 plc) I need to be able to ref a part number and list ONLY every item on that unit. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Arrange your data and headers as shown:
p_n desc price Au Bu Cu Du 23 valve 1.00 1 1 0 1 32 pump 2.50 0 1 1 2 3 plc 6.75 1 1 1 0 45sd sw 9.00 0 0 1 1 select Du p_n desc price qty rsel set2 32 pump 2.50 2 2 1 23 valve 1.00 1 1 2 45sd sw 9.00 1 4 3 4 Select the data under p_n, desc and price (12 cells) in the first array and Insert Name Define array1 Select the data under Au, Bu, Cu, Du (16 cells) and name it array2 Select the Au...Du header and its data (20 cells) and Insert Name Create Top Row Repeat for the 2 cells (select, Du), the 5 cells (rsel,2,1,4,blank) and (set2,1,2,3,4) Extend the series of numbers of set2 to equal the number of parts (n). Create an array the size of array2 and fill it with =(RAND()-0.5)/100+1 Copy Paste Paste Special Value, Multiply to array2. Redo after updates. Enter this formula into rsel and copy down n times: =IF(set2COUNTIF(INDIRECT(select),"0"),"", MATCH(LARGE(INDIRECT(select),set2),INDIRECT(select ),0)) In the results array, under p_n, desc, price (12 cells) enter =IF(rsel="","",INDEX(array1,rsel,)) Under qty, enter and copy down n times =IF(rsel="","",ROUND(INDEX(INDIRECT(select),rsel), 0)) Under select, change Du to another unit as you please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |