CVC Generator

Generate Combinations of CVC's in Excel for APO

Version 4.2.1
20Apr 2016

CVC Generator Documentation

By: Mark Kubiszyn
CVC Generator Excel Screenshot

CVC Generator Options Excel Screenshot

CVC Generator CVC's Excel Screenshot


This new CVC Generator allows you to generate Combinations of CVC's in Excel and builds upon Version 3.1

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

- 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

REQUIRED

Excel 2010 or Excel 2013

THE CVC GENERATOR WORKSHEET

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

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 remeber each time Combinations are generated, the Cells contents will be cleared (I do not clear the formatting).

THE OPTIONS WORKSHEET

The "Options" Worksheet is used to set or edit any available options for the CVC Generator. Available options are:

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 - (new for version 4.2) 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 - (new for version 4.2) 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 Buttons

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.

Q&A

Q. Can I add more Rows to the Table?
A. Yes, add as many as you like by dragging the Table handle (bottom right-hand side) to extend the Rows. You can also right-click in a Cell within the Table and choose 'Insert->Table Rows Above' or 'Insert->Table Rows Below'. If you Tab through the Cells in the Table, the Table will automatically grow to accept another Row. If you Paste in data the Table will grow to accept the range of data.

Q. I changed the highlight Colour and the Combinations cannot be generated - why is this happening?
A. You can only have one highlight Colour at a time, so if you highlighted CVC's with one Colour and then changed the Colour and highlighted more CVC's the Code will only work from the new Colour. The solution is to simply use the 'Clear All' Button and then use your new highlight Colour

Q. Can I delete the License Worksheet?
A. Yes, once you have purchased CVC Generator and read the Terms & Conditions of the License, you may remove it - please try to ensure that somewhere in your Workbook you leave an attribution to the author as follows:

Attribution to software creator must remain in situ:
© Copyright/Author:
Mark Kubiszyn 2016
A License is required to use CVC Generator
Website:
http://www.kubiszyn.co.uk/
 

Q. I need to add another Column to the output with repeated Text - is this possible?
A. Yes, this is now available in Version 4.2 - you can insert as many Columns as you like each with a different Text Field

Q. Can I add more CVC Columns into the Table?
A. You can but you will have to adjust the Code to add another 'For' loop as the Workbook does not use any recursive Subroutines or Functions. You will also need to replicate the Code to 'Clear' another CVC Column and add another Pivot Table and Button, but this is fairly straightforward.

Q. Can the output be sorted?
A. Yes, this is now available in Version 4.2 - you can Sort any number of output Columns either Ascending or Descending

Q. How fast is the Generation?
A. The output speed will vary from computer to computer. The Code uses my Multiplex Class to manipulate arrays in memory so it is fast. For data shown in the Workbook the output is instant. For larger data, say 50 Columns x 50 Columns x 50 Columns which is 125,000 Combinations, these are output in 1-2 seconds. The Sort is fast, however for extremely large output, try not using the Sort to see if any further speed increase may be made

Changelog

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, we speed up the main loop efficiency by up to 50%...

22.02.2016 - (Version 4) released