CVC Generator

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

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

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

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
                            

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