All,
I need help building a macro or formula to insert two new blank rows after every new name. In some cases names will be listed more than once. Ex: below?
Thanks!
Capture excel.PNG
All,
I need help building a macro or formula to insert two new blank rows after every new name. In some cases names will be listed more than once. Ex: below?
Thanks!
Capture excel.PNG
So this would be the end goal... I toyed around and the furthest I got was:
In cell I4 -> =A3=A4 -> Which gives "TRUE" as an answer; then pulling it down to the whole spreadsheet column of names. -> Then searching for "FALSE" -> selecting all and inserting sheet rows.
This will only enter ONE blank row after each name. Wondering if there's a better way...
Attachment 804366
attached spreadsheet...
One way:
Please tryIf you remove the space after Andrew in row 5 you can also remove 3 times TRIM from the formula.Formula:=LET(t,A3:H20,n,INDEX(t,,1),h,SCAN(-2,MAP(n,OFFSET(n,-1,0),LAMBDA(x,y,TRIM(x)<>TRIM(y))),LAMBDA(x,y,x+1+2*y)),MAKEARRAY(ROWS(t)-2+2*COUNTA(UNIQUE(TRIM(n))),COLUMNS(t),LAMBDA(x,y,IFNA(INDEX(t,MATCH(x,h,0),y),""))))
Last edited by HansDouwe; 11-09-2022 at 03:57 PM.
vba
Sub test() With Sheets("sheet1") .Columns(1).Insert With .Range("b4", .Range("b" & Rows.Count).End(xlUp)) On Error Resume Next .SpecialCells(4).EntireRow.Delete On Error GoTo 0 With .Columns(0) .Formula = "=if(trim(b3)<>trim(b4),if(a3=1,""a"",1),"""")" .Value = .Value On Error Resume Next .SpecialCells(2, 1).EntireRow.Insert .SpecialCells(2, 2).EntireRow.Insert On Error GoTo 0 End With End With .Columns(1).Delete End With End Sub
You have a trailing space in row 5 ... Andrew
Sub J3v16() Dim i As Long, rw As Long With Range("A:A"): rw = .Find(Cells(3, 1), Cells(1), , , , xlPrevious).Row: End With For i = Cells(Rows.Count, 1).End(xlUp).Row To rw Step -1 If Cells(i - 1, 1) <> Cells(i, 1) Then Rows(i).Resize(2).Insert Next i End Sub
Last edited by sintek; 11-10-2022 at 02:46 AM.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks