Need to extract items from a database

macdad

New Member
Joined
Dec 13, 2004
Messages
26
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,941
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

New Member
Joined
Nov 22, 2005
Messages
10
Use Advanced Filter for the unique list
Sumproduct or Conditional Sum to get the values (not VLookup)
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
Check out MS Query (Data | Import external data > New database query...)

macdad said:
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

New Member
Joined
Dec 13, 2004
Messages
26
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
 

Forum statistics

Threads
1,078,473
Messages
5,340,543
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top