VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns

smiledt06

New Member
Joined
Oct 31, 2022
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have data in sheet Check of the excel file .test file and expected result

Bot_Container_SizeBot_Container_HeightBot_Container_TypeBot_Remarks_By_ContainerInfoBot_Website_Gross_Weight_ConvertUnitBot_PickUp_Place
;20':20FT;40'GP:40FT;40'HC:40FT Small;20':Not Applicable;40'GP:OK to proceed;40'HC:Not Applicable;20':DRY;40'GP:DRY;40'HC:WET;20':beware;40'GP:fragile and luxury; 40'HC: Noted;20':30kg;40'GP:100kg plus 20kg; 40'HC: 10kgnot found
;20':20FT Scare;40'GP:40FT Happy;40'HC:40FT Large;20':Not Applicable;40'GP:OK to proceed;40'HC:OK;20':NORMAL;40'GP:NORMAL;40'HC:WET;20':request to check;40'GP:small and luxury; 40'HC: OKnot found
not found

I setup a dictionary Botcontainersize_Exist_dic contains 6 keys as also 6 columns header in the sheet Check. I setup an array Prefix(Extract_, Recalculate_,Check_) Cell (row 3, column of 1st key of dictionary Botcontainersize_Exist_dic) has a string with pattern like this ";20':20FT;40'GP:40FT;40'HC:40FT Small"

I want to:

  1. Get data from this cell (row 3, column of 1st key of dictionary Botcontainersize_Exist_dic) to split into substrings then get the keys into new dictionary ExtractFromBotcontainersize_Exist_dic. Keys are the substring between ";" and ":". So the new dictionary ExtractFromBotcontainersize_Exist_dic will now contains 3 keys: 20', 40'GP, 40'HC.
  2. Add group of columns next to each original column (which is each key in dictionary Botcontainersize_Exist_dic). The groups of column will based on combination of each item in dictionary ExtractFromBotcontainersize_Exist_dic and array Prefix. In other words, for each key in BotcontainerSize_Exists_dic:
  • look for the column named is the key
  • create NoOfColToBeAdd columns right next to the original column (which is also the key). For example, insert 9 columns right of column named Bot_Container_Size, then insert 9 columns right after column named Bot_Container_Height and so on. Do not change header or delete data in original columns but be.
  • Assign the header (row 1) of the newly created columns: The newly created columns will have the header name is the header and combination of each item in Prefix( 1st priority) and each item in ExtractFromBotcontainerSize_Exists_dic. The header name will have pattern like this [Prefix]key in [BotcontainerSize_Exists_dic] [ExtractFromBotcontainerSize_Exists_dic]
  • Add 9 newly created columns as new keys to dictionary BotcontainerSize_Exists_dic. Update the values of the keys in dictionary BotcontainerSize_Exists_dic with new column letters. For example, column named or key Bot_Container_Size has value “V”, column named or key “Extract_Bot_Container_Size” has value “W”, column named or key “Recalculate_Bot_Container_Size” has value “X”. So the result should be: Find the column named “Bot_Container_Size” in column “V”. Then add 9 columns right after this column. The 9 newly added column next to column named “Bot_Container_Size” will has below header.
  • Column 1: header will be Extract_Bot_Container_Size_20’
  • Column 2: header will be Recalculate_ Bot_Container_Size_20’
  • Column 3: header will be Check_Bot_Container_Size_20’
  • Column 4 header will be Extract_Bot_Container_Size_40’GP
  • Column 5 header will be Recalculate_Bot_Container_Size_40’GP
  • Column 6 header will be Check_Bot_Container_Size_40’GP And so on Then find column named “Bot_Container_Height”, now at 9 newly added column next to column named “Bot_Container_Height” will be
  • Column 1: Extract_Bot_Container_Height_20’
  • Column 2: Recalculate_ Bot_Container_Height_20’
  • Column 3: Check_Bot_Container_Height_20’
  • Column 4 header will be Extract_Bot_Container_Height_40’GP
  • Column 5 header will be Recalculate_Bot_Container_Height_40’GP
  • Column 6 header will be Check_Bot_Container_Height_40’GP And so on _ Start from row 3, for each row of each column header as key of dictionary ExtractFromBotcontainersize_Exist_dic, split the string into substring with ";" as delimitor. In each splitted substring, the substring before ":" is the key and substring after ":" is value. Then write the values to respective column match the keys with prefix Extract_. For example, string in cell row 3 of column Bot_Container_Height is ";20':Not Applicable;40'GP:OK to proceed;40'HC:Not Applicable". So the value in cell at row 3 column Extract_Bot_Container_Height_20’ is Not Applicable; value in cell at row 3 column Extract_Bot_Container_Height_40’GP is "OK to proceed" and value in cell at row 3 column Extract_Bot_Container_Height_40’HC is "Not Applicable". For groups of column with prefix Check_, insert fomular to check if value in column with prefix Extract_ match with value in column with prefix Recalculate_
Please see the expected result in sheet Expected result in the above link. Please help fix my VBA macro code in the file to get the expected result. Thank you.

Bot_Container_SizeExtract_Bot_Container_Size_20'Recalculate_Bot_Container_Size_20'Check_Bot_Container_Size_20'Extract_Bot_Container_Size_40'GPRecalculate_Bot_Container_Size_40'GPCheck_Bot_Container_Size_40'GPExtract_Bot_Container_Size_40'HCRecalculate_Bot_Container_Size_40'HCCheck_Bot_Container_Size_40'HCBot_Container_HeightExtract_Bot_Container_Height_20'Recalculate_Bot_Container_Height_20'Check_Bot_Container_Height_20'Extract_Bot_Container_Height_40'GPRecalculate_Bot_Container_Height_40'GPCheck_Bot_Container_Height_40'GPExtract_Bot_Container_Height_40'HCRecalculate_Bot_Container_Height_40'HCCheck_Bot_Container_Height_40'HCBot_Container_TypeExtract_Bot_Container_Type_20'Recalculate_Bot_Container_Type_20'Check_Bot_Container_Type_20'Extract_Bot_Container_Type_40'GPRecalculate_Bot_Container_Type_40'GPCheck_Bot_Container_Type_40'GPExtract_Bot_Container_Type_40'HCRecalculate_Bot_Container_Type_40'HCCheck_Bot_Container_Type_40'HCBot_Remarks_By_ContainerInfoExtract_Bot_Remarks_By_ContainerInfo_20'Recalculate_Bot_Remarks_By_ContainerInfo_20'Check_Bot_Remarks_By_ContainerInfo_20'Extract_Bot_Remarks_By_ContainerInfo_40'GPRecalculate_Bot_Remarks_By_ContainerInfo_40'GPCheck_Bot_Remarks_By_ContainerInfo_40'GPExtract_Bot_Remarks_By_ContainerInfo_40'HCRecalculate_Bot_Remarks_By_ContainerInfo_40'HCCheck_Bot_Remarks_By_ContainerInfo_40'HCBot_Website_Gross_Weight_ConvertUnitExtract_Bot_Website_Gross_Weight_ConvertUnit_20'Recalculate_Bot_Website_Gross_Weight_ConvertUnit_20'Check_Bot_Website_Gross_Weight_ConvertUnit_20'Extract_Bot_Website_Gross_Weight_ConvertUnit_40'GPRecalculate_Bot_Website_Gross_Weight_ConvertUnit_40'GPCheck_Bot_Website_Gross_Weight_ConvertUnit_40'GPExtract_Bot_Website_Gross_Weight_ConvertUnit_40'HCRecalculate_Bot_Website_Gross_Weight_ConvertUnit_40'HCCheck_Bot_Website_Gross_Weight_ConvertUnit_40'HCBot_PickUp_PlaceExtract_Bot_PickUp_Place_20'Recalculate_Bot_PickUp_Place_20'Check_Bot_PickUp_Place_20'Extract_Bot_PickUp_Place_40'GPRecalculate_Bot_PickUp_Place_40'GPCheck_Bot_PickUp_Place_40'GPExtract_Bot_PickUp_Place_40'HCRecalculate_Bot_PickUp_Place_40'HCCheck_Bot_PickUp_Place_40'HC
;20':20FT;40'GP:40FT;40'HC:40FT Small20FT
FALSE​
40FT
FALSE​
40FT Small
FALSE​
;20':Not Applicable;40'GP:OK to proceed;40'HC:Not ApplicableNot Applicable
FALSE​
OK to proceed
FALSE​
Not Applicable
FALSE​
;20':DRY;40'GP:DRY;40'HC:WETDRY
FALSE​
DRY
FALSE​
WET
FALSE​
;20':beware;40'GP:fragile and luxury; 40'HC: Notedbeware
FALSE​
fragile and luxury
FALSE​
Noted
FALSE​
;20':30kg;40'GP:100kg plus 20kg; 40'HC: 10kg30kg
FALSE​
100kg plus 20kg
FALSE​
20kg
FALSE​
not found
;20':20FT Scare;40'GP:40FT Happy;40'HC:40FT Large20FT Scare
FALSE​
40FT Happy
FALSE​
40FT Large
FALSE​
;20':Not Applicable;40'GP:OK to proceed;40'HC:OKNot Applicable
FALSE​
OK to proceed
FALSE​
OK
FALSE​
;20':NORMAL;40'GP:NORMAL BUT HUMID;40'HC:WETNORMAL
FALSE​
NORMAL BUT HUMID
FALSE​
WET
FALSE​
;20':request to check;40'GP:small and luxury; 40'HC: OKrequest to check
FALSE​
small and luxury
FALSE​
OK
FALSE​
not found
not found
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
This is a very confusing post. I think it will be better if you split what you want done into simple steps. The same goes for your macro in the workbook. It is very confusing, you are using variables that haven't been set to anything. Don't try do do everything in one go. Build it up. Get the first part working, then the next.

Looking at the start of your code i notice a few inconsistencies (see comments starting with <<<)

VBA Code:
    'A. Step 1: Identify the input column from the sheet Check
'<<< Confusing: the above comment doesn't match what you are doing next. Because you are now dealing with the output sheet headers
    Bot_lastColumn = Sheet2.Cells(1, Sheet2.Columns.count).End(xlToLeft).Column
    Set Bot_headerRange = Sheet2.Range(Sheet2.Cells(1, 1), Sheet2.Cells(1, Bot_lastColumn))
    
    'Identify the range to be resolved
    BotcontainerResolved_Array() = Array("Bot_Container_Size", "Bot_Container_Height", "Bot_Container_Type", "Bot_Remarks_By_ContainerInfo", "Bot_Website_Gross_Weight_ConvertUnit", "Bot_PickUp_Place")
    Set Bot_input = CreateObject("Scripting.Dictionary")
    
    'Loop through each cell in the header range to find matching values
    For Each Check_headerRange_cell In Check_headerRange
'<<< Check_headerRange has not been defined
        
        For Each BotcontainerResolved_Array_value In BotcontainerResolved_Array
            'Debug.Print CStr(BotcontainerResolved_Array_value)
            'Debug.Print CStr(Check_headerRange_cell)

Also the variable names you use are not always that meaningful. And because most start with Bot_ thay are difficult to keep apart.

I will post a proposal for the first part of your code soon
 
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,569
Members
452,926
Latest member
rows and columns

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top