Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Brian Walsh
  Where is Brian Walsh?
 Brian Walsh
Subject: SELECT DISTINCT in Excel
Thread ID: 70229 Message ID: 70229 # Views: 14 # Ratings: 0
Version: Visual FoxPro 6 Category: Microsoft Office Automation
Date: Wednesday, June 15, 2005 1:27:28 PM         

Hello All,

Part of one of my VFP apps exports all every item with a quantity in stock > 0 in the the inventory file to an Excel sheet (Excel 97), with cost related data so that inventory value can be determined at any time.
There is one row in the excel sheet for every item witgh a quantity in stock > 0 , with a total of approx. 2000 - 5000 rows, depending on the site.
This all works fine.

The problem is that the users are overwhelmed with all the information. The previous paper based reports grouped the items together based on the frst four characters of the item ID (which represent a single type of product.) They now want this information on a seperate worksheet in the same Excel file.

The item ids look like the:



The xxxxxxx part of the item ID contains dimensional information.

so, the desired excel sheet would contain the following rows, and columns
A - B
0011 - Total inv value for all 00011 items
0012 - Total inv value for all 00012 items

I can get the total inv value using the data exported from the first worksheet with the following array formula:
{=SUM(IF(LEFT('Normal Items'!A2:A2368,4)=A2,'Normal Items'!M$2:M$2368))}
Column A contains the four characters that I am grouping on: i.e. 0011, 0012
Normal Items is the name of the worksheet containg the detail information, and Column M contains the inventory value of each item.

My problem is this:
From VFP, how can I extract the unique four character groups from the the list of all Item IDs on the "Normal Items" worksheet?

Alternatively, I could use:

But this includes four character groups that don't have a qty in stock.

Any ideas would be appreciated.



SELECT DISTINCT in Excel Posted by Brian Walsh @ 6/15/2005 1:27:28 PM
RE: SELECT DISTINCT in Excel Posted by Ken Murphy @ 6/15/2005 2:10:36 PM
RE: SELECT DISTINCT in Excel Posted by Brian Walsh @ 6/16/2005 11:41:23 AM
RE: SELECT DISTINCT in Excel Posted by Ken Murphy @ 6/16/2005 3:05:10 PM