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?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.
- i am also not clear what he wants, may be i think this is what he wants.
but always learning from you guys. 🙂- Thank you for your replies. I trust the below clarifies what I am looking for.
In the table below is the data that I have.
Qty
Rqd. Item Length
4 100 x 133 x 20 5559
1 100 x 133 x 25 5349
1 100 x 133 x 15 2602
2 100 x 133 x 30 2602
2 100 x 133 x 20 5559
2 100 x 133 x 25 5349
7 100 x 133 x 15 2602
2 100 x 133 x 30 2602
I would like a formula that lists the the data into cells as per below:
Total
Qty
Rqd. Item Total Length
6 100 x 133 x 20 33354
3 100 x 133 x 25 16047
8 100 x 133 x 15 20816
4 100 x 133 x 30 10408
If possible I would like to have the items in smalles size to largest,
