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.
Thank you for your reply.
I have inputted your formula into the actual spreadsheet. It works however there are two issues that occur.
1) When the contents of the item column are listed they are duplicated a number of times.
This may be due to there being empty rows in the actual spreadsheet that are not in the above example.
2) Line 2 and line 5 of your formula refers to cells B2:C9. In the actual spreadsheet there is another column in the table. When I input B2:B9,D2:D9 instead of B2 to C9 an error message occurs.
Is there anyway to resolve these issues?
I have inputted your formula into the actual spreadsheet. It works however there are two issues that occur.
1) When the contents of the item column are listed they are duplicated a number of times.
This may be due to there being empty rows in the actual spreadsheet that are not in the above example.
2) Line 2 and line 5 of your formula refers to cells B2:C9. In the actual spreadsheet there is another column in the table. When I input B2:B9,D2:D9 instead of B2 to C9 an error message occurs.
Is there anyway to resolve these issues?
Can you share your actual spreadsheet with all columns and rows including the empty rows?
The spreadheet is attached as requested.
The issues are detailed in a previous post and it would assist if the items were tabulated if possible from smallest size to largest size.
Many thanks for your assistance with this request.
2 100 x 10 FB 550 2 100 x 10 FB 550 1 100 x 10 FB 560 1 100 x 10 FB 560 In your actual spreadsheet there are same items with different length, for example 100 x 10 FB with 550 and 560.
Which is your expected result? Do you want to return:
4 100 x 10 FB 2200
2 100 x 10 FB 1120
or
6 100 x 10 FB 3320
- 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