Geek Documentation Logo

CVC Generator

Version 5.1


Introduction

  • Purchase : CVC Generator
  • Software Name : CVC Generator · Generate Combinations of CVC's in Excel for APO
  • Software Version : v 5.1
  • Website : Kubiszyn.co.uk/

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 a lot 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
  • * Works in SharePoint - whenever a user is in the file it will ask you whether you want to view just your own changes for filtering or everybody's
  • * In Version 4.3 you can now select/deselect individual Cells in the Table Columns using a double-click
  • * In Version 5 you can set up Buttons to pre-highlight Ranges of Level 3 CVC's - easily modifable if you need to extend the usage
  • * In Version 5 you can Copy & Past a list of Level 3 CVC's into the Table to highlight - easily modifable if you need to extend the usage
  • * In Version 5 you can delete a CVC1 ie. Product Code and the underlying Format will also be removed. Typically CVC's are built using a list of Product Codes and then selected other values ie. Plant and / or Customer





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

CVC Generator 5 Screen Shot

The Generated CVC'S

The "CVC'S" Worksheet will be displayed together with the generated combinations built from the selected CVC'S from left to right, the first combination being '12345', 'B2P', 'BA06', the next being '12345', 'B2P', 'BA08' and so on and so forth. The format that these are displayed in has been specifically designed by adjusting the settings on the Options Sheet, so Location, Customer Planning Group, then 3 Columns of Product Codes but with different Headers, a Product Sales Org. and a Sales Organisation. You can can also 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)

Videos #back to top

Videos of CVC Generator

Here is a Video of the latest version of CVC Generator in use to create combinations for APO. Music is 'Streets Of Punjab by Hanu Dixit'

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

Use the Scrollbar at the bottom to scroll right to read the Changelog

07.11.2023 - (Version 5) added the ability to remove the formatting in the first Table Column if the delete button is pressed.  Tidied up a small bug whereby the automatic Copy feature added in 4.4 (Not released) did not highlight a single Row of combinations.  Added the ability to maintain lists in the Code so that when a button is pressed, Codes are automatically highlighted in a Table Column (very useful when making supersessions in APO when you need to replicate all of the CVC's of another Product).  Added the ability to Copy & Paste a list of CVC2 Codes (I chose CPG or Customer Planning Group Codes)into the Table to highlight the values - these can be adjusted to highlight other Columns if needed.  Did a Rubberduck on the Code Indentations & Insections but due to how old the Code is now, I merely ignored or supressed most of the warnings - this Software has been operating day in and day out at the Company I work for, for about 10 years without any issues with multiple users going into the Workbook at once (so I don't imagine many issues / bugs persist)    

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