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]

I have data in sheet Check of the excel file .test file and expected result

Bot_Container_Size	Bot_Container_Height	Bot_Container_Type	Bot_Remarks_By_ContainerInfo	Bot_Website_Gross_Weight_ConvertUnit	Bot_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: 10kg	not 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: OK	not 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_Size

Extract_Bot_Container_Size_20'

Recalculate_Bot_Container_Size_20'

Check_Bot_Container_Size_20'

Extract_Bot_Container_Size_40'GP

Recalculate_Bot_Container_Size_40'GP

Check_Bot_Container_Size_40'GP

Extract_Bot_Container_Size_40'HC

Recalculate_Bot_Container_Size_40'HC

Check_Bot_Container_Size_40'HC

Bot_Container_Height

Extract_Bot_Container_Height_20'

Recalculate_Bot_Container_Height_20'

Check_Bot_Container_Height_20'

Extract_Bot_Container_Height_40'GP

Recalculate_Bot_Container_Height_40'GP

Check_Bot_Container_Height_40'GP

Extract_Bot_Container_Height_40'HC

Recalculate_Bot_Container_Height_40'HC

Check_Bot_Container_Height_40'HC

Bot_Container_Type

Extract_Bot_Container_Type_20'

Recalculate_Bot_Container_Type_20'

Check_Bot_Container_Type_20'

Extract_Bot_Container_Type_40'GP

Recalculate_Bot_Container_Type_40'GP

Check_Bot_Container_Type_40'GP

Extract_Bot_Container_Type_40'HC

Recalculate_Bot_Container_Type_40'HC

Check_Bot_Container_Type_40'HC

Bot_Remarks_By_ContainerInfo

Extract_Bot_Remarks_By_ContainerInfo_20'

Recalculate_Bot_Remarks_By_ContainerInfo_20'

Check_Bot_Remarks_By_ContainerInfo_20'

Extract_Bot_Remarks_By_ContainerInfo_40'GP

Recalculate_Bot_Remarks_By_ContainerInfo_40'GP

Check_Bot_Remarks_By_ContainerInfo_40'GP

Extract_Bot_Remarks_By_ContainerInfo_40'HC

Recalculate_Bot_Remarks_By_ContainerInfo_40'HC

Check_Bot_Remarks_By_ContainerInfo_40'HC

Bot_Website_Gross_Weight_ConvertUnit

Extract_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'GP

Recalculate_Bot_Website_Gross_Weight_ConvertUnit_40'GP

Check_Bot_Website_Gross_Weight_ConvertUnit_40'GP

Extract_Bot_Website_Gross_Weight_ConvertUnit_40'HC

Recalculate_Bot_Website_Gross_Weight_ConvertUnit_40'HC

Check_Bot_Website_Gross_Weight_ConvertUnit_40'HC

Bot_PickUp_Place

Extract_Bot_PickUp_Place_20'

Recalculate_Bot_PickUp_Place_20'

Check_Bot_PickUp_Place_20'

Extract_Bot_PickUp_Place_40'GP

Recalculate_Bot_PickUp_Place_40'GP

Check_Bot_PickUp_Place_40'GP

Extract_Bot_PickUp_Place_40'HC

Recalculate_Bot_PickUp_Place_40'HC

Check_Bot_PickUp_Place_40'HC

;20':20FT;40'GP:40FT;40'HC:40FT Small

20FT

FALSE​

40FT

FALSE​

40FT Small

FALSE​

;20':Not Applicable;40'GP:OK to proceed;40'HC:Not Applicable

Not Applicable

FALSE​

OK to proceed

FALSE​

Not Applicable

FALSE​

;20':DRY;40'GP:DRY;40'HC:WET

DRY

FALSE​

DRY

FALSE​

WET

FALSE​

;20':beware;40'GP:fragile and luxury; 40'HC: Noted

beware

FALSE​

fragile and luxury

FALSE​

Noted

FALSE​

;20':30kg;40'GP:100kg plus 20kg; 40'HC: 10kg

30kg

FALSE​

100kg plus 20kg

FALSE​

20kg

FALSE​

not found

;20':20FT Scare;40'GP:40FT Happy;40'HC:40FT Large

20FT Scare

FALSE​

40FT Happy

FALSE​

40FT Large

FALSE​

;20':Not Applicable;40'GP:OK to proceed;40'HC:OK

Not Applicable

FALSE​

OK to proceed

FALSE​

OK

FALSE​

;20':NORMAL;40'GP:NORMAL BUT HUMID;40'HC:WET

NORMAL

FALSE​

NORMAL BUT HUMID

FALSE​

WET

FALSE​

;20':request to check;40'GP:small and luxury; 40'HC: OK

request to check

FALSE​

small and luxury

FALSE​

OK

FALSE​

not found

not found

 

[sijpie]

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 <<<)

    '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