Remember Me?

#1
September 7th 11, 06:58 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2011 Posts: 1
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
-----
25
4
12
1
DEPCOD02 CNC DEPT
----

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
25 25
4 8
12 36
1 1
3 6

"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
2 DEPCOD02 CNC DEPT

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post BetaDocuments New Users to Excel 2 October 6th 11 02:04 PM AJSloss Excel Discussion (Misc queries) 1 November 11th 09 01:15 PM TheBuckStoppedHere Excel Discussion (Misc queries) 2 April 8th 09 02:27 PM Ed[_4_] Excel Worksheet Functions 5 September 16th 08 07:11 AM Mike Ferguson Excel Worksheet Functions 3 November 24th 04 07:31 AM

All times are GMT +1. The time now is 10:36 AM.

The comments are property of their posters.