• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting Data with 4 Criteria

Busymanjohn

Busymanjohn

Member
Poser ,,,, and really annoying me. I have a table of data consisting of the below


Geo/IOT Product Qtr Wk1 Wk2 Wk3 Wk4

1 Crabs 1Q09 1500 2000 2500 3000

1 Crabs 2Q09 2000 2500 3000 3500


This is only a small sample ,,, data goes on for hundreds of row. What I am looking to do is have the same data extracted form this "raw" data but only when it meets certain criteria ,,, such as Geo/IOT, Product, 1Q09 and Wk2 .... Geo/IOT and Wk number will be in a drop down menu ( combo box ), the others are content of cells .... I've tried using SUMPRODUCT, INDEX MATCH and VLOOKUP ,,, can't get any of them to work correctly ,,, been at it for a couple of hours and has become annoying. Any help please.
 
Thanx for starting a new post.


Have you tried a Pivot Table ?


Do you want summary data or all records that match your criteria ?


Can you post your data somewhere

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
http://rapidshare.com/files/442174539/Poser.xls


Hi Hui ,,,, the data returned should only be one instance, i.e. no duplicates. I don't want to use a pivot as this will eventually be used by a second file.
 
C5: =SUMPRODUCT(('Raw Data'!$B$4:$B$19=Summary!$B5)*('Raw Data'!$C$4:$C$19=Summary!C$4),(OFFSET('Raw Data'!$C$4:$C$19,,INDEX('Raw Data'!$D$3:$P$3,,Summary!$E$2))))


and copy across
 
Hi, The solution given by Hui worked ,,,, but what I didn't check was that it worked for other Geo/IOT numbers relevant to the first drop down menu, is there a way to include the first drop down menu with this solution??
 
Absolutely

But what is the relationships between the Geo/IOT numbers and the list in Drop Down 1 ?

Your data only has 1 in the Geo/IOT Column
 
Hi Hui, the drop down menu will obviously have more than just 1 once I build the file to encompass all the data .... in fact will go from 1 - 10 as there are 10 different regions to collect data from.
 
Try in


C5: =SUMPRODUCT(('Raw Data'!$A:$A=$C$2)*('Raw Data'!$B:$B=Summary!$B5)*('Raw Data'!$C:$C=Summary!C$4),(OFFSET('Raw Data'!$C:$C,,INDEX('Raw Data'!$D$3:$P$3,,Summary!$E$2))))
 
Hi Hui, returns a NUM error,,,, and I think it may be to do with the 3rd argument ('Raw Data'!$C:$C=Summary!C$4)as data in column C can have 1Q09 multiple times.
 
Strange works for me in Excel 2010 ?


try:

C5: =SUMPRODUCT(('Raw Data'!$A$4:$A$19=Summary!$C$2)*('Raw Data'!$B$4:$B$19=Summary!$B5)*('Raw Data'!$C$4:$C$19=Summary!C$4),(OFFSET('Raw Data'!$C$4:$C$19,,INDEX('Raw Data'!$D$3:$P$3,,Summary!$E$2))))


It is only using the values where 1Q09 and all other criteria are met, so that's ok


Did you make any other changes since yesterday ?
 
Hi Hui, seems to work ok now, thanks for the extra time spent on this ,, gonna save me pulling my hair out trying to get it to work, Appreciate the help, if I get any more problems with it, I'll repost. Thanks again.
 
very strange!

C5: =SUMPRODUCT(('Raw Data'!$A$4:$A$19=Summary!$C$2)*('Raw Data'!$B$4:$B$19=Summary!$B5)*('Raw Data'!$C$4:$C$19=Summary!C$4),(OFFSET('Raw Data'!$C$4:$C$19,,INDEX('Raw Data'!$D$3:$P$3,,Summary!$E$2))))

is given is C5; =0


Hui or Busymanjohn can you give any reason for this?


I have not made any change to the data in the original file.
 
You must log in or register to reply here.
Back
Top