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.
=LET(qtyrqd,A2:A9,
item,UNIQUE(B2:C9),
itemdescription,FILTER(item,{1,0}),
itemqty,FILTER(SUMIF(B2:B9,item,qtyrqd),{1,0}),
itemlength,FILTER(UNIQUE(B2:C9),{0,1}),
itemqtylength,itemqty*itemlength,
result,CHOOSE({1,2,3},itemqty,itemdescription,itemqtylength),
result)Is this what you are looking for? This formula seems to work in my spreadsheet as shown in the attached file.
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