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.

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.

lookup
 

Gary

Thank you for the reply, but I think I was very vague in my description.

I want to be able to type "A" in a field, then below that have some
rows/fields populate with the items used in the "A" machine.

ie: Machine number = "A"

p/n desc price qty
23 valve 1.00 1
3 plc 6.75 1

Thank you, I hope this illustrates better what has me baffled.




"Gary L Brown" wrote:

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.

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.