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]

You could specify the following data:

 

[dan_adelais]

You could specify the following data:
View attachment 43090

There is always a line of separation between Risk Types. The relationship can be many account names to one risk type. In terms of column patterns, I need to include only the columns shown. B, G, Q, etc. All else should not be included.

 

[dan_adelais]

Hello,

I posted a thread yesterday but I've refined what I'm looking for. Below is my data extract.

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

What I need to do is to write a script that will create one row per account number per LOB, and provide the net loss number per that LOB. Example below. Note that there are hidden columns within the data extract by default, but the 10 shown are the only ones I need.

Any help is very much appreciated.

 

[DanteAmor]

Row numbers are not visible in the image.
Could you comment in which row the headers are "LOB1 LOB2 ..."
And in which row is the first "Risk type"

Note:
In the future, to help you in a more practical way and to be able to reproduce your example in our excel sheet, if it is possible for you, put the examples here using the XL2BB tool minisheets.

___

 

[DanteAmor]

Assuming the headers are in row 4 and the data starts in row 6.
Also assuming the account and account name are separated by a hyphen.

Try this, Data on sheet1, results on sheet2.

Sub Transform_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim col As Variant
  Dim i As Long, j As Long, k As Long
  
  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 i = 6 To sh1.Range("A" & Rows.Count).End(3).Row Step 3
    For j = 0 To UBound(col)
      sh2.Range("A" & k).Value = Split(sh1.Range("A" & i + 1), "-")(1)
      sh2.Range("B" & k).Value = Split(sh1.Range("A" & i + 1), "-")(0)
      sh2.Range("C" & k).Value = sh1.Range(col(j) & 4)
      sh2.Range("D" & k).Value = sh1.Range(col(j) & i + 1)
      sh2.Range("E" & k).Value = sh1.Range("A" & i)
      k = k + 1
    Next
  Next
End Sub

 

[Joe4]

I merged your two threads together.

In future, please do not post questions of the same nature multiple times. Clarifications/refinements to a question should be posted in a follow-up response to the original question. Per Forum Rules (#12), posts of a duplicate nature will typically be locked or deleted.

Note that Dante's responses in this thread were in relation to your original question.

 

[dan_adelais]

Assuming the headers are in row 4 and the data starts in row 6.
Also assuming the account and account name are separated by a hyphen.
View attachment 43185

Try this, Data on sheet1, results on sheet2.

Sub Transform_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim col As Variant
  Dim i As Long, j As Long, k As Long
 
  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 i = 6 To sh1.Range("A" & Rows.Count).End(3).Row Step 3
    For j = 0 To UBound(col)
      sh2.Range("A" & k).Value = Split(sh1.Range("A" & i + 1), "-")(1)
      sh2.Range("B" & k).Value = Split(sh1.Range("A" & i + 1), "-")(0)
      sh2.Range("C" & k).Value = sh1.Range(col(j) & 4)
      sh2.Range("D" & k).Value = sh1.Range(col(j) & i + 1)
      sh2.Range("E" & k).Value = sh1.Range("A" & i)
      k = k + 1
    Next
  Next
End Sub

Joe,

My apologies.

Dante,

Thank you so much. The code works partially. My headers are on row 5 and my data goes through row 77. The code stops at row 32 for some reason. Row 33 has no unique attributes. I am working on figuring out why. Does my above post, starting with that static list, make more sense? Also, there are a couple instances of account names themselves containing a hyphen. The account number is always the last 5 characters of the string.

 

[DanteAmor]

The macro will not work, because in the image of post #1 I asked you if they came in pairs. You actually didn't answer.

Row numbers are not visible in the images.

You could put your examples using Xl2BB tool misheet.

 

[dan_adelais]

The macro will not work, because in the image of post #1 I asked you if they came in pairs. You actually didn't answer.

Row numbers are not visible in the images.

You could put your examples using Xl2BB tool misheet.

I'm sorry if I did not understand your question. My company will not allow me to download add-ins unfortunately.

Are you asking if Risk Type and Account Name always have a one to one relationship? No. Many account names can be associated with one risk type. Example below. Is this what you are asking? Apologies if I am misunderstanding.