A
Alan
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.
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.