Converting data extract to individual line items

[dan_adelais]

Hi,

I'm trying to build a macro with the following data...

What I want to do is to transform the above into this:

Basically, what I'm trying to accomplish is to take the data set and transform it into individual line items. Account numbers, names, and risk types repeat, but LOB and Net Loss are unique. I'm at a loss - any help is much appreciated.

Thank you!

 

[DanteAmor]

Many account names can be associated with one risk type. Example below. Is this what you are asking?

Yes that's what I mean.

The account number is always the last 5 characters of the string.

Your example is not consistent with your description:

But I guess in your actual data you have 5 characters.
________________________________________________________________________

Account numbers, names, and Risk Types will not change. Therefore, I created a table of static data to start with.

In the image I do not see the rows, nor the columns nor the name of the sheet.
This part will remain pending in the macro.
________________________________________________________________________

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
________________________________________________________________________

Sub Transform_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim col As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim ar As Range
  
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Sheet1")                                          'Origen Sheet
  Set sh2 = Sheets("Sheet2")                                          'Destination Sheet
  col = Array("C", "G", "Q", "V", "Z", "AC", "AF", "AG", "AO", "AP")  'columns
  
  k = 2
  For Each ar In sh1.Range("A7", sh1.Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants).Areas
    For i = ar.Cells(1).Row + 1 To ar.Cells(ar.Rows.Count).Row
      For j = 0 To UBound(col)
        sh2.Range("A" & k).Value = Right(sh1.Range("A" & i), 5)
        sh2.Range("B" & k).Value = Left(sh1.Range("A" & i), Len(sh1.Range("A" & i)) - 5)
        sh2.Range("C" & k).Value = sh1.Range("A" & ar.Cells(1).Row)
        sh2.Range("D" & k).Value = ""
        sh2.Range("E" & k).Value = sh1.Range(col(j) & 5)
        sh2.Range("F" & k).Value = sh1.Range(col(j) & i)
        k = k + 1
      Next
    Next
  Next
End Sub

 

[dan_adelais]

Yes that's what I mean.


Your example is not consistent with your description:
View attachment 43249
But I guess in your actual data you have 5 characters.
________________________________________________________________________


In the image I do not see the rows, nor the columns nor the name of the sheet.
This part will remain pending in the macro.
________________________________________________________________________

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
________________________________________________________________________

Sub Transform_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim col As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim ar As Range
 
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Sheet1")                                          'Origen Sheet
  Set sh2 = Sheets("Sheet2")                                          'Destination Sheet
  col = Array("C", "G", "Q", "V", "Z", "AC", "AF", "AG", "AO", "AP")  'columns
 
  k = 2
  For Each ar In sh1.Range("A7", sh1.Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants).Areas
    For i = ar.Cells(1).Row + 1 To ar.Cells(ar.Rows.Count).Row
      For j = 0 To UBound(col)
        sh2.Range("A" & k).Value = Right(sh1.Range("A" & i), 5)
        sh2.Range("B" & k).Value = Left(sh1.Range("A" & i), Len(sh1.Range("A" & i)) - 5)
        sh2.Range("C" & k).Value = sh1.Range("A" & ar.Cells(1).Row)
        sh2.Range("D" & k).Value = ""
        sh2.Range("E" & k).Value = sh1.Range(col(j) & 5)
        sh2.Range("F" & k).Value = sh1.Range(col(j) & i)
        k = k + 1
      Next
    Next
  Next
End Sub

Thank you so, so, much. I can't tell you how much I appreciate your help (and for putting up with my...shall we say, ill-formed question. Heard loud and clear and I will certainly be more mindful in the future.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.