EXCEL: VBA Coding Required For Formatting Raw Data As Per Requirement

I suppose the first question I'd ask is from where do you get the raw data and can you get it in a other format, perhaps a csv for instance?

The layout of sheet1 doesn't seem consistent. The Init codes for most brands are in the first line of the block, but for Brand 5 they're in the 2nd.
And what causes the Brand block to vary in size.

It suggests to me that someone has manually created the sheet1 stuff or at least hacked it around after copying it from somewhere else. If that's the case then I suggest you address the layout of sheet1 and make it more consistent before we spend time coding for something that may not be necessary.
i.e. create a 2 column Table for

Brand
InitCode

Similar question applies to Sheet2. How has this been created? the fact that it has merged cells suggests that this has been manually created, and if so the frst thing to do is change the layout of the data that is collected so that for instance you have a four column table for

Brand
Site
ABC- or whatever you call this data tyoe
Value

Hi,

Please try this code, it is created based on sample file on post #1.

Sub MakeTable()
  Dim mtxSite(), mtxInitCode, mtxBrandSite(), mtxTemp(), temp, cell As Range

  strSite = "Sheet1!K3:L13"
  strInitCode = "Sheet1!A3:B36"
  strBrandSite = "Sheet1!D2:I22"
  strOutput = "Sheet1!S3"

  'Get mtxSite
  mtxSite = Range(strSite).Value

  'Get mtxInitCode
  mtxTemp = Range(strInitCode).Value
  For i = 1 To UBound(mtxTemp, 1)
      If mtxTemp(i, 1) = "" Then
         mtxTemp(i, 1) = mtxTemp(i - 1, 1)
      End If
      If mtxTemp(i, 2) <> "" Then
         str1 = str1 & "," & mtxTemp(i, 2)
      End If
  Next i
  temp = Split("ZZZ" & str1, ",")
  ReDim mtxInitCode(1 To UBound(temp), 1 To 2)
  For i = 1 To UBound(temp)
      mtxInitCode(i, 1) = temp(i)
      For j = 1 To UBound(mtxTemp, 1)
          If InStr(1, mtxTemp(j, 2), temp(i)) > 0 Then
             mtxInitCode(i, 2) = mtxTemp(j, 1)
             Exit For
          End If
      Next j
  Next i

  'Get mtxBrandSite
  mtxBrandSite = Range(strBrandSite).Value

  'Process
  k = UBound(mtxSite, 1)
  l = UBound(mtxInitCode, 1)
  ReDim mtxTemp(1 To (k * l), 1 To 4)
  
  For i = 1 To UBound(mtxTemp)
      j = i Mod k: If j = 0 Then j = k
      mtxTemp(i, 1) = mtxSite(j, 1)
      mtxTemp(i, 2) = mtxSite(j, 2)

      j = Int((i - 1) / k) + 1
      mtxTemp(i, 3) = mtxInitCode(j, 1)
      
      For m = 1 To UBound(mtxBrandSite, 2)
          If mtxBrandSite(1, m) = mtxTemp(i, 1) Then
             For n = 2 To UBound(mtxBrandSite, 1)
                 If mtxBrandSite(n, 1) = mtxInitCode(j, 2) Then
                    mtxTemp(i, 4) = mtxBrandSite(n + 1, m)
                    Exit For
                 End If
             Next n
             Exit For
          End If
      Next m
  Next i

  'Dump to screen
  Range(strOutput).Resize(UBound(mtxTemp, 1), UBound(mtxTemp, 2)) = mtxTemp
End Sub

Regards

Please try this :

Sub MakeTable()
  Dim mtxSite(), mtxInitCode, mtxBrandSite(), mtxTemp(), temp, rng As Range
  Dim strSite As String, strInitCode As String, strBrandSite As String, str1 As String
  Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long

  strSite = "Sheet3"
  strInitCode = "Sheet1"
  strBrandSite = "Sheet2"

  'Get mtxSite
  Set rng = Range(strSite & "!A1").CurrentRegion.Offset(1)
  mtxSite = rng.Resize(rng.Rows.Count - 1).Value

  'Get mtxInitCode
  Set rng = Range(strInitCode & "!A2")
  mtxTemp = Range(rng, rng.SpecialCells(xlCellTypeLastCell)).Value
  For i = 1 To UBound(mtxTemp, 1)
      If mtxTemp(i, 1) = "" Then
         mtxTemp(i, 1) = mtxTemp(i - 1, 1)
      End If
      If mtxTemp(i, 2) <> "" Then
         str1 = str1 & "," & mtxTemp(i, 2)
      End If
  Next i
  temp = Split("ZZZ" & str1, ",")
  ReDim mtxInitCode(1 To UBound(temp), 1 To 2)
  For i = 1 To UBound(temp)
      mtxInitCode(i, 1) = temp(i)
      For j = 1 To UBound(mtxTemp, 1)
          If InStr(1, mtxTemp(j, 2), temp(i)) > 0 Then
             mtxInitCode(i, 2) = mtxTemp(j, 1)
             Exit For
          End If
      Next j
  Next i

  'Get mtxBrandSite
  Set rng = Range(strBrandSite & "!A1").CurrentRegion
  mtxBrandSite = rng.Value

  'Process
  k = UBound(mtxSite, 1)
  l = UBound(mtxInitCode, 1)
  ReDim mtxTemp(1 To (k * l), 1 To 4)

  For i = 1 To UBound(mtxTemp)
      j = i Mod k: If j = 0 Then j = k
      mtxTemp(i, 1) = mtxSite(j, 1)
      mtxTemp(i, 2) = mtxSite(j, 2)

      j = Int((i - 1) / k) + 1
      mtxTemp(i, 3) = mtxInitCode(j, 1)

      For m = 1 To UBound(mtxBrandSite, 2)
          If mtxBrandSite(1, m) = mtxTemp(i, 1) Then
             For n = 2 To UBound(mtxBrandSite, 1)
                 If mtxBrandSite(n, 1) = mtxInitCode(j, 2) Then
                    mtxTemp(i, 4) = mtxBrandSite(n + 1, m)
                    Exit For
                 End If
             Next n
             Exit For
          End If
      Next m
  Next i

  'Dump to screen
  ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
  Range("A1:D1").Value = Array("Sites", "SiteCode", "InitCode", "Values")
  Range("A2").Resize(UBound(mtxTemp, 1), UBound(mtxTemp, 2)) = mtxTemp
End Sub

Regards

Thanks KareDog,

The format is as required and accurate, but my concern as I' mentioned in above revert is, in case if I' insert columns in Sheet2 within the Sites it is generating blank as it seems hard coded on column number and not working as match of the headers i.e. Site1, Site2, etc.

Same for other 2 sheets also,

I' required the feasibility where the user can insert the Columns within the existing data and still the format work according to the header matches for all 3 sheets..

DETAILS:
Sheet1 - Can have more Brands by appending the existing records or inserting Rows in between to insert more records. Hence there should be feasibility in code to increase the row numbers or to change the range and it should work according matching the brands and not on row number.

Sheet2 - Can have more Brands by appending the existing records or inserting Rows in between to insert more records as well as Can have more Sites by inserting Columns in between to insert more records. Hence there should be feasibility in code to increase the Column numbers or to change the range and it should work according matching the Sites + Brands and not on row/column number.

Sheet3 - Can have more Sites by appending the existing records or inserting Rows in between to insert more records. Hence there should be feasibility in code to increase the row numbers or to change the range and it should work according matching the brands and not on row number.


Thanks
MG

Originally Posted by gaikwad.mm

... as it seems hard coded on column number and not working as match of the headers i.e. Site1, Site2, etc.

No, it is not. It is working based on comparing data and headers.

Originally Posted by gaikwad.mm

DETAILS:
Sheet1 - Can have more Brands by appending the existing records or inserting Rows in between to insert more records. Hence there should be feasibility in code to increase the row numbers or to change the range and it should work according matching the brands and not on row number.

Sheet2 - Can have more Brands by appending the existing records or inserting Rows in between to insert more records as well as Can have more Sites by inserting Columns in between to insert more records. Hence there should be feasibility in code to increase the Column numbers or to change the range and it should work according matching the Sites + Brands and not on row/column number.

Sheet3 - Can have more Sites by appending the existing records or inserting Rows in between to insert more records. Hence there should be feasibility in code to increase the row numbers or to change the range and it should work according matching the brands and not on row number.

Please look at this conditions :

- All data must start at cell A1

- No blank row(s) or column(s) is allowed, except for Sheet1 (only blank row(s) is allowed)

- Row label must be placed in row 1, the row label cannot blank, and must persistent between sheets

- For sheet1 and sheet3, number of columns cannot be changed, means no other add/insert column (s) is allowed.
For sheet2, add/insert column(s) only allowed begin from column B, and no blank column(s) is allowed

- If any data is inserted/added, the data must be in consistent format with other data, for example, if new data is inserted in sheet2, column A is two merged cells, column B to F data is pairs of ABC and valuexx.


If you believe your workbook meets these conditions, and the macro is not worked as expected, then upload the unworking workbook so I can make some adjustments, as long as above conditions are still met.


Regards