I need to add a line between different account numbers after sorting.

Hi @Larry Harrison

See if the following formula accomplishes what you are looking for. I have applied the formula to cell H2.

=LET(
     data, SORT(B2:E14, 2),
      acc, CHOOSECOLS(data, 2),
     calc, TEXTSPLIT(TEXTJOIN(";",, BYCOL(IF(acc = TOROW(UNIQUE(acc)), BYROW(data, ARRAYTOTEXT), NA()),
                     LAMBDA(a, TEXTJOIN(";",, TOCOL(a, 3)))) & ";"), ", ", ";"),
           IFNA(IFERROR(--calc, calc), "")
)

HTH

IlirU

As this forum is a public platform, we’ve taken steps to help protect your privacy by removing your organization’s domain name from your message. To ensure your data remains secure, we kindly recommend avoiding the inclusion of personal or organizational details such as domain names or screenshots with sensitive information in future posts.  

Hi @Larry Harrison, 

Thank you for your detailed information. 

To get this working correctly for your situation, please follow these steps carefully: 

• Press Alt + F11 to open VBA editor. Select Insert > Module > paste this script into it and close the VBA editor (script em de o duoi) 
• Save the file as Macro-enabled Excel file then press Alt + F8 (or go to Developer tab > Macros) > select the macro and press Run 

Note: the script date ranges are based on your sample data structure as the Account# column C and the data range from column A to column E. If you actual worksheet has different structure, please modify the ranges (those inside " ") to ensure the script can operate properly.  

Sub SortByAccountAndInsertBlankRows() 
    Dim ws As Worksheet     
    Dim lastRow As Long     
    Dim i As Long     
    Set ws = ActiveSheet     
    'Find last used row based on Account # column C     
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row     
    'Sort data by Account # column while still keeping header row     
     With ws.Sort         
        .SortFields.Clear         
        .SortFields.Add Key:=ws.Range("C2:C" & lastRow), _             
           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal        
          .SetRange ws.Range("A1:E" & lastRow)         
          .Header = xlYes         
         .Apply     
End With     
'Insert blank rows between different Account # values
 
For i = lastRow To 3 Step -1         
    If ws.Cells(i, "C").Value <> ws.Cells(i - 1, "C").Value Then             
       ws.Rows(i).Insert Shift:=xlDown         
   End If     
  Next i     
  MsgBox "Data sorted by Account # and blank rows inserted.", vbInformation 
End Sub

I hope the information I shared earlier was somewhat helpful in addressing your issue. If you have any further questions or updates, please don’t hesitate to share. I’m always happy to assist further if needed.      

Thank you for your patience and your understanding. I look forward to continuing the conversation. 

What I need is to sort the data by the account numbers then add a blank line between the different account numbers.

Hi @Larry Harrison, 

I hope you are doing well today.  

Based on the information you shared that you would like to sort a table by account numbers and then automatically insert a blank row between each group of account numbers using a macro in Excel (Microsoft 365). 

This is certainly something that can be achieved, and I would be more than happy to assist you with building the right solution. However, in order to make sure the macro is tailored accurately to your specific file and data structure, I would like to gather a few more details before moving forward.  

Could you kindly help me with the following questions? 

1. Could you please share the sample file you mentioned or a screenshot of your data so I can better understand the layout? 
2. In terms of grouping, what criteria do you use to determine that two account numbers belong to different groups? For example, is it based on a completely different account number value or a specific pattern within the number? 
3. Could you also describe or illustrate what the expected output should look like after the macro runs, particularly where the blank rows should appear in relation to your sample data? 

Once I have these details, I will be able to prepare a complete and accurate solution that works seamlessly for your situation. 

Please understand that my initial response may not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.  

Thank you for your understanding and cooperation. I look forward to hearing from you.  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.