How do I Copy data in Excel if multiple conditions are met?

[stv0505]

OK; I have a dataset that is imported to an Excel Worksheet from an Access Database Query. The data is set up in columns with headings as below:

Date Sample_ID Meas_Type Data_Type Average
9/12/2007 1363-rpm-aa 2 5 847.7
9/12/2007 1363-rpm-aa 2 2 848.9
9/12/2007 1363-rpm-aa 2 3 74.5
9/12/2007 1363-rpm-aa 2 6 -0.165
9/12/2007 1363-rpm-aa 4 0 760.7
9/12/2007 1363-rpm-aa 4 5 758.9
9/12/2007 1363-rpm-aa 4 3 32.5
9/12/2007 1363-rpm-aa 4 8 1.15

(Simplified set of data, I have thousands of Samples, 6 different measurement types with 4 data types per measurement type with Avg., Median, Min, Max, etc... per Sample.)

In another worksheet within the same workbook, I have a Summary spreadsheet. I want to return the value that is in the Average column for a Sample ID if the Sample ID in my Summary worksheet matches the Sample_ID of my imported data and the Measurement Type and Data Type I specify.

For example; I want the cell values in column H of my Summary worksheet to be equal to the Average from the imported data sample above only if the Sample_ID in my Summary worksheet equals the Sample_ID in my imported data and IF it is Measurement type 4 and Data type 5. In this example it will return the value of 758.9.

Is there a COPY IF statement where it has to meet 3 criteria? Is it better to sort/copy/paste the data into separate worksheets for measurement type and data type and then do a VLOOKUP?

Any help is greatly appreciated!!!!!!!

 

[Rofeu]

Hi,

it is NEVER better to seperate data, it will only complicate things for you.

That being said:
Option1: Modify the query to return only aggregates
Option2: Use a PivotTable on the Query results
Option3: For Average this will work:

=SUMPRODUCT(--(Query!B1:B50=Summary!A1)*--(Query!C1:C50=Summary!B1)*--(Query!D1:D50=Summary!C1),E1:E50)/SUMPRODUCT(--(Query!B1:B50=Summary!A1)*--(Query!C1:C50=Summary!B1)*--(Query!D1:D50=Summary!C1)

Take care though, that you'll need a different formula for each statistical measure.

I'd also use the formula option as a last resort. Try to apply one of the other options first.

Cheers,

Roel

 

[mintjulep]

Option 4: filter and hide columns.

 

[stv0505]

Thanks for the input Rofeu. Per your suggestion I developed multipe Queries that essentially group the data by measurement type and data type. I have written VB code to import the individual Queries as spreadsheets within the Excel folder that Management uses to review the data and have populated their summary page by using VLOOKUP for the same file name in the correct spreadsheets that are developed from the Queries. Seems to work for their needs.