Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selective extraction of data | Excel Discussion (Misc queries) | |||
data extraction | Excel Discussion (Misc queries) | |||
data extraction | New Users to Excel | |||
data extraction from excel | New Users to Excel | |||
Data Extraction | Setting up and Configuration of Excel |