Geek Documentation Logo

CVC Generator

Version 4.3


Introduction


If you have already purchased this Software, let me take a moment to thank you for being a loyal customer
You are entitled to free lifetime updates for ALL future builds

This documentation is to help you understand the Software and to give you a more meaningful insight into what it can do. Please go through the documentation and read it carefully · Basic Excel skills will be required

Requirements

You will need the following Excel Version & Prerequisite to use this Software

  1. Excel 2010 onwards (32bit or 64bit)
  2. Basic Excel skills
  3. Windows PC · NOT a Mac!

No support is provided for customization or development of this Software






About CVC Generator #back to top

This CVC Generator allows you to generate Combinations of CVC's (3 levels of different data) in Excel for APO Demand Planning

It takes 6 Columns of data and generates any of the Cells that are highlighted in the Odd Columns and then builds an output File from these selections (it can include the Descriptions too if you want). The Workbook allows you to create your own Table of data and format the output by sorting the Columns, sorting the final output, adding in Blank Columns, inserting new Columns with auto-populated Text Fields, renaming the Headers for the Columns and repeating Columns. The Table can be moved anywhere on the Worksheet so that you can design it for your own Company requirements. There are many features - please see the list below

APO allows you to create single and multiple CVC's (Characteristic Value Combinations) for CPG's (Customer Planning Groups) by APO Products by APO Locations. You can create CVC's directly in APO or via an Excel Workbook and then Paste them from the Clipboard or upload them from a flat file. The SAP Transaction Code is /n/SAPAPO/MC62 - Maintain Planning-Relevant Characteristic Combinations, Create Characteristic Combinations. CVC Generator makes this process even easier

Features include:

  • * Odd Column data can be highlighted for a Fill and Font Colour of your choice and is used to generate the Combinations
  • * Even Column data can be used for descriptions for your CVC's
  • * The data does not need to be highlighted in any special order & you can have Blank Rows inbetween CVC's
  • * The CVC Table can be moved anywhere in the Worsheet
  • * The CVC Output Columns can be replicated, reordered and include extra Blank Columns
  • * The CVC Output Headers can be renamed
  • * You can maintain lists of commonly used Output Column Orders & Headers
  • * You can insert extra Columns into the CVC Output data and auto-populate the Field with Text
  • * You can Sort the final CVC Output data by any number of Columns
  • * In Version 4.3 you can now select/deselect individual Cells in the Table Columns using a double-click





Getting Started #back to top

The download File

Open the 'CVCGenerator.xlsm' File. You will see that some Cells are selected by default for 'CVC1', 'CVC2' and 'CVC3'. Press the 'Generate Combinations' Button (RHS, not shown in the Image below)

CVC Generator Image

The Generated CVC'S

The "CVC'S" Worksheet will be displayed together with the 4 combinations built from the selected CVC'S from left to right, the first combination being '10001', 'B04', 'B012', the next being '10001', 'B04', 'B016' and so on and so forth. You may notice that one CVC Column has been repeated and that another Column has the Text "Hello World". This is because you can build up Columns of values from the selections made, inserting Blank Columns or Text. This is very useful if your values must always be in a specific format following the combination selection. You will notice that the Descriptions are ignored - they are there for you to identify your lists, for example a list of Products would most likely have recognisable Product Names as opposed to a series of Product Codes. You can however output the Descriptions too. The same can be said for a list of Locations or Plants. For more information, see The CVC's Worksheet

CVC Generator Default Combinations Image

Quick Steps to start using the CVC Generator

Follow these 5 quick steps to start using the CVC Generator or read more about the The CVC Generator Worksheet

Press the 'Clear All' Button on the "CVC Generator" Worksheet

Copy and Paste your required CVC Codes and/or Descriptions into the "CVC Generator" Worksheet

Tweak any of the options on the "Options" Worksheet, for example you may wish to add a separate
Column of specific data

Highlight the required CVC combinations and press the Generate Combinations Button

Copy the generated CVC's into APO or use for any other kind of generation where combinations
need to be spawned en masse
                            

The Options Worksheet #back to top

Options Table

Highlight Colours - used to set the Fill Colour and the Font Colour for the highlighted Cells in the Table on the "CVC Generator" Worksheet. No other Formats are picked up, but this would be easy to modify in the Code should you wish to change the Font Name for example

Column Output Order - (protected from deletion) used to specify how the output is made for the selected Combinations and the Descriptions. This setting is configured in the 'Lists Table' and you can then select one of your lists from the Drop-down in the Cell next to the option

Column Output Headers - (auto-populated from the 'Lists Table' and protected from deletion) used to specify the Headers for the output Columns. This setting is configured in the 'Lists Table' and a lookup Formula then retrieves the setting automatically, making it easy to switch settings whenever you like

Text List - (auto-populated from the 'Lists Table' and protected from deletion) used to specify the Text Fields whenever you 'insert' Columns into the 'Column Output Order' list. This setting is configured in the 'Lists Table' and a lookup Formula then retrieves the setting automatically, making it easy to switch settings whenever you like

Sort - (auto-populated from the 'Lists Table' and protected from deletion) used to specify how you would like to Sort the output data by Column with Sort direction. This setting is configured in the 'Lists Table' and a lookup Formula then retrieves the setting automatically, making it easy to switch settings whenever you like

Auto Column Fit - On/Off toggle. If this option is ticked then the output Columns will be automatically re-sized to fit the data and Headers width. If this option is not ticked, Column re-sizing will not be applied

Lists Table

Column Output Order List - used to store Column output lists that can be selected for the 'Column Output Order' option, detailed above. These can be lists of Columns. For example, you may want to change the order that the Colums are output or you may want to include Blank Columns. To do this you can set up a comma-delimited list like this '1,1,blank,3,5' which means output Column 1 from the Table for the first Column of the output. Then repeat this Column again for the second Column of the output. Then insert a Blank Column. Then output Column 3 from the Table as the fourth Column of the output. Lastly output Column 5 from the Table as the fith and last Column of the output. You can also use the 'insert' keyword to tell the Code that it needs to insert another Column at the point specified - whenever you do this remember to pass in a Text Field in the 'Text List' Option detailed below

Column Output Headers List - used to store Column output Header lists that will be automatically selected for the 'Column Output Headers' option, detailed above. If you only enter one Header, then that will be used for the first Column only. If you enter a comma-delimited list of Headers like this 'Product,CPG,Depot' then the first Column for the output will have the Header 'Product', the next Column for the ouput will have the Header 'CPG' and the last Column of the output will have the Header 'Depot'

Text List - used to store a comma-delimited list of Text Fields. You only need to add these if you use the 'insert' keyword in the 'Column Output Order List' ie. "1,1,blank,3,5,insert" whereby you then need to pass a single Text Field, for example: B001

Sort - used to Sort the final output of the data by Column as a pipe '|' and comma-delimited list - you can specify the Sort direction ie. "1|Ascending,5|Ascending" meaning Sort Column A, Ascending then Column E, Ascending

Pivot Table Slicer Button

The Buttons shown in the "CVC Generator" Worksheet use Slicers connected to the Pivot Tables in the "Options" Worksheet. The Buttons use a Cell to store the Name of the Button and a Cell to slightly reformat the Name to include padding to center the Text better in the Slicers. These Cells are protected but you can unprotect them in the Code and edit them if you wish (go into the Code Editor and into the Code Module for the "Options" Worksheet - comment out the Code). They also contain Data Validation which you must also clear before editing them. When you have edited a Button Cell, you will need to click in a Cell in the respective Table and use right-click, refresh to refresh the Pivot Table. The Code to capture Pivot Table Slicer clicks is in the 'ThisWorkbook' Code Module - it is fairly simple to understand and each click will run the appropriate Subroutine in the 'mdlSubroutines' Code Module

The CVC Generator Worksheet #back to top

The "CVC Generator" Worksheet is where you add your own specific Combinations of CVC's. These may be Products, Customer Planning Groups and Depots. A Table is provided for you to do this. The Table can be moved anywhere in the Worksheet and the Code will still work

The way that you create the Combinations is by selecting the Odd Column Cells for the Combinations that you want to generate permutations. So for instance in the first image at the top of this article I selected or highlighted 2 CVC1's (or Products - these Products can be in any order or be disparate). I then selected 3 CVC2's (or Customer Planning Groups) and then 5 CVC3's (or Depots). So I will be generating 2 x 3 x 5 = 30 Combinations

Once you have made your selections, press the 'Generate Combinations' Button to generate the Combinations. There are 'Clear' Buttons to clear the highlighted Cells in each Odd Column and a 'Clear All' Button to clear all highlighted Cells within the entire data Table. You can change the highlight Fill Colour and the highlight Font Colour on the "Options" Worksheet

The CVC's Worksheet #back to top

The "CVC'S" Worksheet is used for the output of the selected CVC's and the resulting Combinations. The first Row is set to Bold to highlight the Headers and has Freeze Panes set (you can remove this if you like). The data output can be formatted with Blank Columns and / or repeated Columns of generated Combinations. The headers can be renamed to anything that you like (they can even be the same name). The output can have Columns set to Autofit the width of the data and Headers. You can adjust the Zoom level if required and do some other formatting to this Worksheet, just remember each time Combinations are generated, the Cells contents will be cleared (I do not clear the formatting)

Screen Shots #back to top

This is a Screen Shot of the CVC Generator

I have selected some combinations by Highlighting the data in the 3 Columns. I will now press the 'Generate Combinations' Button to generate all of my chosen combinations

CVC Generator Example Select Combinations Image

This is a Screen Shot of the created Combinations

I have outlined Product '10001', CPG's 'B01->B05' and Depots 'B002', 'B008' and 'B016'

CVC Generator Example Combinations Image

This is a Screen Shot of the settings that I selected in the "Options" Worksheet

I chose to simply output the 6 Columns, '1,2,3,4,5,6' in consecutive order, which is what you see in the Image above for the created combinations

CVC Generator Example Options Image




There are currently no frequently asked questions about this Software that cannot be answered via this documentation






Support #back to top

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes:

* Responding to questions or problems regarding the Software and its features
* Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include:

* Customization and installation services
* Support for third party software or ANY kind of development whatsoever

Before seeking support, please...

* Make sure your question is a valid Software Issue and not a customization request
* Make sure you have read through the documentation and any related video guides before asking support on how to accomplish a task
* Make sure to double check the Software FAQs or online documentation
* Ensure that you access to the VBOM is allowed and that Macros can run in Excel
* Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
Contact Kubiszyn.co.uk via our Facebook Page - remember to be patient, if there has been an issue with your download, I will always respond within 48 hours and will Email you the File directly if neccessary or via Messanger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Version History (Changelog) #back to top

You can find the version history in the Code Module for any Macro-enabled Software or read more information below. The latest Version is always shown first


Changelog


09.06.2018 - (Version 4.3) added the ability to deselect an individual Cell formatting by 
double-clicking the Cell

20.04.2016 - (Version 4.2.1) removed some obsolete Defined Names.  Added some informative Text Boxes
on the "Options" Worksheet

08.03.2016 - (Version 4.2) changed the GUI slightly as almost half of the Screen in Excel is lost
when users enlarge their Screen resolution due to eye-sight issues.  also removed some of the over-use
of Colour Fill for the Titles & reduced the default Zoom level rendering a much nicer GUI

- as per Q & A from Website - added the ability to Sort the Columns post output and insert Columns into
the output where users may require additional Columns populated with a field value that will be copied
down the output Range (modified SortInsertArrayColumns() in Multiplex Class)
- protection applied to the Table Cells in the Options Table where you select your Column Output Order
List to prevent deletion of the Formula

03.03.2016 - (Version 4.1) added code to turn off screen drawing whilst outputting the cvc's
Use Cells.ClearContents as opposed to selecting the Cells first & then clearing the Selection
Use Cells.EntireColumn.AutoFit as opposed to selecting the Cells first & then performing autofit
on the Column Selections

- tweak to the main loop used to generate the CVC Combinations -  by using the Lenb() function
to check for Empty array slots & then exiting each For loop, I managed to speed up the main loop
efficiency by up to 50%

22.02.2016 - (Version 4) released