Need to extract items from a database

[macdad]

I need to generate reports regularly from a database that is routinely updated. I have tried using pivot tables to extract the data that I want to report or graph. I have become frustrated with pivot tables either due to my lack of experience or limitations in the tables. I am looking for a non-pivot table method of generating the same reports. IN this database I have a list of exporters that can change from month to month. How can I generate a unique list of these exporters from the database?

For Example

Exporter Month Volume
X Jan 20
Y Jan 15
X Feb 10
Z Feb 30
A Mar 5

How do I automatically generate a list that would contain A, X, Y, and Z? I would use this list and sumproducts/vlookups to generate the reports that the pivot tables used to generate?

Thanks

 

[Domenic]

You can use the Advanced Filter to generate a unique list (check 'Unique records only'). Alternatively, you can use the following formula...

Assumptions:

A1:C1 contains your headers/labels

A2:C6 contains your data

Formula:

E1: leave empty

E2, copied down:

=IF(OR(COUNTIF($E$1:E1,A2:$A$6)=0),INDEX(A2:$A$6,MATCH(0,COUNTIF($E$1:E1,A2:$A$6),0)),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[neofytosc]

Use Advanced Filter for the unique list
Sumproduct or Conditional Sum to get the values (not VLookup)

 

[tusharm]

Check out MS Query (Data | Import external data > New database query...)

I need to generate reports regularly from a database that is routinely updated. I have tried using pivot tables to extract the data that I want to report or graph. I have become frustrated with pivot tables either due to my lack of experience or limitations in the tables. I am looking for a non-pivot table method of generating the same reports. IN this database I have a list of exporters that can change from month to month. How can I generate a unique list of these exporters from the database?

For Example

Exporter Month Volume
X Jan 20
Y Jan 15
X Feb 10
Z Feb 30
A Mar 5

How do I automatically generate a list that would contain A, X, Y, and Z? I would use this list and sumproducts/vlookups to generate the reports that the pivot tables used to generate?

Thanks

 

[macdad]

Thanks for the suggestions. I decided to create a simple pivot table to generate the unique list of exporters and wrote a simple macro to copy this list to the proper location in the reports.

Mike