Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Frequency of Unique Values in a cloumn BetaDocuments New Users to Excel 2 October 6th 11 02:04 PM
listing unique values AJSloss Excel Discussion (Misc queries) 1 November 11th 09 01:15 PM
Maintaining integrity while referencing data TheBuckStoppedHere Excel Discussion (Misc queries) 2 April 8th 09 02:27 PM
Sorting and maintaining formula integrity Ed[_4_] Excel Worksheet Functions 5 September 16th 08 07:11 AM
Counting unique values + frequency Mike Ferguson Excel Worksheet Functions 3 November 24th 04 07:31 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017