Use a Macro to format report to reduce number of lines used

[MrPink1986]

Hi,

I have quite a large report where a lot of the data is represented in rows and duplicated. The report has 17 columns and ~5100 rows. Within these columns only a couple has data that is different to the row above. I want to be able to reduce the number of rows used in the report and where the data is different include into one row using the alt space command to make the row wider. The first column A dictates what s common to each row.
So I want column A to read only one instance from the data (this will vary throughout the column) and I want the variable items to represented within a row where they are associated to the value and common in column A.

 

[MrPink1986]

I have figured out that if I use the following formula - =CONCATENATE(E2,CHAR(10),E3,CHAR(10),E4,CHAR(10)) and format the text to Wrap I can achieve the end goal. How can I automate this into a macro which can detect the number of lines in the column to apply the formula to?
In the example provided this would be to Row 15 for google

 

[MrPink1986]

I have also come across this macro which seems to identify the variable in column A but cannot seem to figure out where I would add or how to add my concat formula - =CONCATENATE(E2,CHAR(10),E3,CHAR(10),E4,CHAR(10))

Any ideas?

Dim row As Integer
Dim col As Integer
Dim working_row As Integer
Dim rowVal As String, myStr As String
rowVal = ""
row = 1
col = 5
While Cells(row, 1).Value <> ""
If Cells(row, 1).Value <> rowVal Then
myStr = ""
working_row = row
rowVal = Cells(row, 1).Value
End If
myStr = myStr & CStr(Cells(row, col).Value)
Cells(working_row, col + 1).Value = myStr
row = row + 1
Wend
End Sub

 

[Fluff]

Assuming your data is sorted on col A, as per you image, try

Sub MrPink()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = 2 To UBound(Ary)
      If Ary(r, 1) <> Ary(r - 1, 1) Then
         nr = nr + 1
         For c = 1 To UBound(Ary, 2)
            Nary(nr, c) = Ary(r, c)
         Next c
      Else
         For c = 1 To UBound(Ary, 2)
            If InStr(1, Nary(nr, c), Ary(r, c), 1) = 0 Then Nary(nr, c) = Nary(nr, c) & vbLf & Ary(r, c)
         Next c
      End If
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, UBound(Nary, 2)).Value = Nary
End Sub

Change sheet names to suit

 

[MrPink1986]

Assuming your data is sorted on col A, as per you image, try

Sub MrPink()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
  
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = 2 To UBound(Ary)
      If Ary(r, 1) <> Ary(r - 1, 1) Then
         nr = nr + 1
         For c = 1 To UBound(Ary, 2)
            Nary(nr, c) = Ary(r, c)
         Next c
      Else
         For c = 1 To UBound(Ary, 2)
            If InStr(1, Nary(nr, c), Ary(r, c), 1) = 0 Then Nary(nr, c) = Nary(nr, c) & vbLf & Ary(r, c)
         Next c
      End If
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, UBound(Nary, 2)).Value = Nary
End Sub

Change sheet names to suit

This works thank you. Couple of quick explanations on your code please if you will
My data is sorted on column A however I may have the need to change the column where I am combining values in rows - what is the point in the code I need to change the column ref to?
Also if I needed to run this on for example two columns in my example E&H how could I do this?

Thanks in advance.

 

[Fluff]

If your data starts in (say C6) change this line as shown

Ary = Sheets("Sheet1").Range("C6").CurrentRegion.Value2

That pulls all the data from the current region based on the cell reference.

 

[MrPink1986]

Perfect this is fantastic - thanks for your help

 

[Fluff]

You're welcome & thanks for the feedback.