extraction data from excel spreadsheet

[John105183]

Hi I have a general understanding of excel, but now wish to filter out data from a worksheet to post to another part of the same sheet , the criteria being that I need to show from a list of orders , the number of frequency, over 5 that a certain part number has been purchased and were the quantity on those purchase's exceeded 10 and the sum of the total quantity ordered , because of the large number of part numbers available typing that into a formulae is not practical because its time consuming, is there an efficient way of doing this , any help would be gratefully appreciated

[Richard Buttrey]

Hi and welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[John105183]

HI Richard thanks very much for the quick response to my post , I have uploaded the sample spreadsheet so you can get an idea of what I am trying to achieve, my Knowledge of excel is limited for the moment but I have tried various ideas , but have been unable to get the result by an efficient formulae.
Thanks once again for your efforts.

[mike7952]

Here is a vba solution

Please Login or Register  to view this content.

[FDibbins]

Do you have the list of part numbers, or does that need to be extracted too?

Also, would it be OK to just list all parts, with their counts and amounts, and either just use that list, or extract from that?

[FDibbins]

Assuming you have that list of parts, then this will pull out the sum of all orders placed more than 5 times, and that have more than 10 ordered (it differs from your sample total)
=IF(COUNTIFS($C$3:$C$76,B81)>=5,SUMIFS($F$3:$F$76,$C$3:$C$76,B81,$D$3:$D$76,">=10"),"")
and for the count...
=IF(COUNTIFS($C$3:$C$76,B81)>=5,COUNTIFS($C$3:$C$76,B81,$D$3:$D$76,">=10"),"")

[mike7952]

@FDibbins
My understanding of what the OP is wanting is if the order count > 5 with order qty > 10 then sum the Total amount ordered. And not just Where the sum of order count > 5 with order qty > 10. Idk I could be wrong.

If possible also if the total order value of the same part numbers could be incorportated into the same formulae that would be great but this is not a priority at this stage.

Please Login or Register  to view this content.

[FDibbins]

I looked at it that way too, and you could be right. Then the formula becomes...
=IF(COUNTIFS($C$3:$C$76,B81)>=5,SUMIFS($F$3:$F$76,$C$3:$C$76,B81),"")

[mike7952]

I know when I wrote my code I came up with the same values your first formulas produced.

[John105183]

Hi Guys thanks for all you input, I have perhaps not explained part of the issue correctly as most of the suggestions rely on reference to the part numbers at the bottom of the sample worksheet which I search through and entered manually, I need to extract these also using the same criteria i.e appearing on the list=>5 and order Qty =>10 , them all you suggestions do work thanks

[mike7952]

If you can use a vba solution then my solution in post 4 doesn't rely on your part numbers at the bottom.

[John105183]

Hi Mike
Although I am not familiar with VBA and your solution doesn't rely on part numbers posted manually , will it still print the part number that fits the criteria then print it in result like at the bottom of my sample sheet so I can recognise which part number fits the criteria, if so I am willing to learn VBA many thanks again

[mike7952]

Yes it will do what you want. If it doesn't we can modify the code

[M. Shahbaz]

Sir please help me, ,,,
And solve my problem,
Approve my thread
Thanks,