Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Listing unique values, frequency, and maintaining list order integrity
I am attempting to modify an existing workbook that is designed to
take a database of products and their respective information, and create, as an end result, a sheet which can be imported into our accounting software (Sage MAS 200). Currently there are 4 main sheets in the workbook: 1) The database of products 2) The scan sheet -- In this sheet a customer will, using a barcode scanner, scan in their purchase order. Each scan correlates to a preset quantity of a given product. Multiple scans of the same barcode, increases the total order quantity. For this specific customer I also need to be able to add in the department to which the product is allocated. This will be done by setting up the department as a "comment line" to be scanned in before all applicable product. Scan# Description Scan Value DEPCOD01 RM DEPT ----- ACRSS0001 08960 SWIFTACH 1" (5000/BOX) 25 ACRSS0002 BURNISHING PAD 2 MICROBEAD 4 ACRSS0003 BURNISHING PAD 2.875X4.25 LARG 12 ACRSS0003 BURNISHING PAD 2.875X4.25 LARG 1 DEPCOD02 CNC DEPT ---- ACRSS0004 BURNISHING PAD 4" LARGE CIRCLE 3 ACRSS0004 BURNISHING PAD 4" LARGE CIRCLE 3 The description and scan value only appear on this sheet so that our customer knows what and how much of an item they have scanned. 3) The calculator -- this sheet must determine how much, and of what has been ordered. By multiplying the number of scans against the value of each scan. It must display unique values, in the order in which items were scanned, and keep the department labels in the correct location on the order. Scanned Unique Values Frequency Scan Value Total Ordered DEPCOD01 DEPCOD01 1 1 1 ACRSS0001 ACRSS0001 1 25 25 ACRSS0002 ACRSS0002 2 4 8 ACRSS0003 ACRSS0003 3 12 36 ACRSS0003 DEPCOD02 1 1 1 DEPCOD02 ACRSS0004 2 3 6 ACRSS0004 ACRSS0004 "Scanned" uses simple cell references to recreate the list from the scan sheet for easy troubleshooting. It is imperative that the department codes appear in their proper position in the final list. I also need the final list to have no spaces / blank cells, between unique values. 4) The import sheet -- this sheet is pre-formatted to match our software's import structure. It uses simple cell references and vlookup to determine the necessary values. H 0 ACRPLA 2 DEPCOD01 RM DEPT 1 ACRSS0001 MS-08-960 08960 SWIFTACH 1" (5000/BOX) 25 1 ACRSS0002 MIGSCBP-MB BURNISHING PAD 2 MICROBEAD 8 1 ACRSS0003 USMIGLRBP BURNISHING PAD 2.875X4.25 LARG 36 2 DEPCOD02 CNC DEPT 1 ACRSS0004 USMIGLCBP BURNISHING PAD 4" LARGE CIRCLE 6 The Issue: I have been unsuccessful in making my "Calculator Sheet" display unique values, and the frequency of these values, with the department codes in their original order. In the end the import sheet needs to look like the example above. It is the addition of the department codes that has made this difficult, previous versions have not required this information, and as such the order of the information had not been significant. It is important that everything utilizes standard excel functions if possible. As this file will be sent to my customers, and I cannot easily install supplementary functions on their PCs nor expect them to run VB. I greatly appreciate any help you can provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Frequency of Unique Values in a cloumn | New Users to Excel | |||
listing unique values | Excel Discussion (Misc queries) | |||
Maintaining integrity while referencing data | Excel Discussion (Misc queries) | |||
Sorting and maintaining formula integrity | Excel Worksheet Functions | |||
Counting unique values + frequency | Excel Worksheet Functions |