Forum Discussion
Solved
Excel - Obtaining Data From A Table
Good Afternoon In the table below. I would like to: Count the quantity of items of the same size in column A and put into a separate cell. Count the quantity of items of the same size in column A...
=LET(qtyrqd,A6:A416, item,B6:B416, length,D6:D416, itemlength,UNIQUE(B6:D416), itemonlyitem,FILTER(UNIQUE(B6:D416),{1,0,0}), itemConly,FILTER(UNIQUE(B6:D416),{0,1,0}), itemonlylength,FILTER(UNIQUE(B6:D416),{0,0,1}), itemlengthqty,FILTER(SUMIFS(qtyrqd,item,itemlength,length,itemonlylength),{1,0,0}), multiply,itemlengthqty*itemonlylength, result,CHOOSE({1,2,3,4,5},itemlengthqty,itemonlyitem,itemConly,itemonlylength,multiply), finalresult,SORT(FILTER(result,itemlengthqty>0),2), finalresult)This formula seems to return the expected result in my spreadsheet.
OliverScheurich
The result required is to provide the total number of each size required.
For example there are
100 x 10 - 550mm Long,
100 x 10 - 250mm Long
100 x 10 - 560mm Long Items.
I would like to have a total number required for each size, and a total length of material for each size.
So the output would show for example:
48 100 x 10 FB 250 12000
20 100 x 10 FB 480 9600
48 and 20 are the totals for each item size and the 12000 and 9600 are the length required.
The output would list the smalles items forst to the largest items.
Many Thanks
The result required is to provide the total number of each size required.
For example there are
100 x 10 - 550mm Long,
100 x 10 - 250mm Long
100 x 10 - 560mm Long Items.
I would like to have a total number required for each size, and a total length of material for each size.
So the output would show for example:
48 100 x 10 FB 250 12000
20 100 x 10 FB 480 9600
48 and 20 are the totals for each item size and the 12000 and 9600 are the length required.
The output would list the smalles items forst to the largest items.
Many Thanks
=LET(qtyrqd,A6:A416,
item,B6:B416,
length,D6:D416,
itemlength,UNIQUE(B6:D416),
itemonlyitem,FILTER(UNIQUE(B6:D416),{1,0,0}),
itemConly,FILTER(UNIQUE(B6:D416),{0,1,0}),
itemonlylength,FILTER(UNIQUE(B6:D416),{0,0,1}),
itemlengthqty,FILTER(SUMIFS(qtyrqd,item,itemlength,length,itemonlylength),{1,0,0}),
multiply,itemlengthqty*itemonlylength,
result,CHOOSE({1,2,3,4,5},itemlengthqty,itemonlyitem,itemConly,itemonlylength,multiply),
finalresult,SORT(FILTER(result,itemlengthqty>0),2),
finalresult)
This formula seems to return the expected result in my spreadsheet.
- OliverScheurich
The formula is just what I was looking for. Many thanks for you assistance and the time you spent answering my queries is very much appreciated. It will save hours of time going forward.