Data Extraction Problem

Since you have Excel 2003, make your range of data a list (Data-List...).
Excel will outline the range with a border and add a new row with an
asterisk. Select all that outlined data and name it. Save your file

Now you can build a Microsoft Query query that you can save and run at will,
pulling the results back into the same workbook. Go to a empty/new
worksheet, then use Data-Import External Data-New Database Query.. to get
to the MS Query utility.

Select Excel as the datasource, navigate to the location for you workbook
and select it. Follow the wizard's steps to build the query.

"RD Wirr" wrote:

Hi Duke,

I have Excel 2003 and also Access 2002. For sure I know this is an
application better done in Access but for the moment moving it all to Access
will require a development project that I don't have time for right now. I am
Using Excel to develop some processes and once they are working we will port
this over to Access. For now however, I have to make it work in Excel.

Thanks,
RDW

"Duke Carey" wrote:

What version of Excel do you have?
Do you have MS Access?

"RD Wirr" wrote:


I have a list of data organized in columns with 1000's of rows. One of the
columns contains Serial Numbers which can be present multiple times referring
to a Lot of material and also batches that are taken from that same Lot of
Material. When a Lot is used up there is another "Complete" column that marks
the lot=TRUE. I need to extract a list of the Serial Numbers that are not yet
complete. Just the unique numbers rather than the entire list of instances.
So I have two ways to show that the Lot is complete: One of the records with
the specific S/N is Marked TRUE or the sum of the transactions for that
specific S/N =0. The data looks something like this.

Item Qty Stock S/N Type Complete
Item1 0.2 10-01 Lot
Item2 0.3 10-02 Lot
Item1 -0.2 10-01 Lot TRUE
Item4 10 10-01 Batch

I need to extract this data dynamically since the database is always growing
and this query is used in a Validation Dropdown List.

I have tried Filters which couldn't really get a unique list with these
conditions and also Pivot tables which came close but I couldn't find a way
to hide the S/Ns with 0 values. If anyone has an idea how to do this, I will
very much appreciate leaning about it.

Thanks in advance,
RDW