Line Break after each unique entry

[garypea123]

Good Afternoon,

Unfortunately I do not have XLSBB on computer (due to restrictions)

In C4 I have my list of numbers

In D4 I would like a formula which will look at the data from C4# and create a break after each unique entry.

Have a great weekend all

 

[smozgur]

1. A recent Lambda function I submitted might help as a new approach:
   
   

   INSERTBLANKAT

   INSERTBLANKAT function inserts blank cells at every X row in a given range. The mini-sheet - G:O columns - shows the steps to build the final Lambda formula. =LAMBDA(data,atRow, LET(rows, ROWS(data), cols, COLUMNS(data), rowsWithBlanks, SEQUENCE(rows+rows/(atRow-1))...

   www.mrexcel.com
   
   
2. A function that takes C4:C12 as the source range:
   

   =LET(range, C4:C12,
          rows, ROWS(range)*2,
         row, SEQUENCE(rows),
         IF(MOD(row,2)=1,INDEX(range,(row+1)/2),""))

   Book1.xlsm
   	C	D
   4	3867292	3867292
   5	4854915
   6	4854915	4854915
   7	1692828
   8	3449503	4854915
   9	3449503
   10	754347	1692828
   11	4501923076
   12	4501923076	3449503
   13
   14		3449503
   15
   16		754347
   17
   18		4501923076
   19
   20		4501923076
   21
   Sheet3

   Cell Formulas
   Range	Formula
   D4:D21	D4	=LET(range, C4:C12, rows, ROWS(range)*2, row, SEQUENCE(rows), IF(MOD(row,2)=1,INDEX(range,(row+1)/2),""))
   Dynamic array formulas.

3. A User Defined Function that returns an array that you can copy and paste into a standard module, and use in the cell by providing the range (this could be also turned to a macro instead of UDF):
   

   Function BlankAtEvenRow(rng As Range)
   Dim cll As Range
   Dim i As Long
   ReDim ret(1 To rng.Rows.Count * 2)  
       For Each cll In rng.Cells
           i = 2 * (cll.Row - rng.Row + 1) - 1
           ret(i) = cll.Value
           ret(i + 1) = ""
       Next cll
       BlankAtEvenRow = WorksheetFunction.Transpose(ret)  
   End Function

   Book1.xlsm
   	C	D
   4	3867292	3867292
   5	4854915
   6	4854915	4854915
   7	1692828
   8	3449503	4854915
   9	3449503
   10	754347	1692828
   11	4501923076
   12	4501923076	3449503
   13
   14		3449503
   15
   16		754347
   17
   18		4501923076
   19
   20		4501923076
   21
   Sheet3

   Cell Formulas
   Range	Formula
   D4:D21	D4	=BlankAtEvenRow(C4:C12)
   Dynamic array formulas.

 

[Fluff]

My take on this

Fluff.xlsm
	A	B	C	D
1
2
3
4			3867292	3867292
5			4854915
6			4854915	4854915
7			1692828	4854915
8			3449503
9			3449503	1692828
10			754347
11			4501923076	3449503
12			4501923076	3449503
13
14				754347
15
16				4501923076
17				4501923076
Main

Cell Formulas
Range	Formula
D4:D17	D4	=LET(u,UNIQUE(C4:C12),DROP(DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,FILTER(C4:C12,C4:C12=b),""))),1),-1))
Dynamic array formulas.

 

[smozgur]

Ah! I just saw the "each unique entry" in the original question. I think I was too lazy looking at the screenshot only

Thanks, @Fluff!

 

[JEC]

Maybe this works

Sub jec()
 Dim cl
 With CreateObject("scripting.dictionary")
    For Each cl In Range("C5", Range("C" & Rows.Count).End(xlUp))
      If Not .exists(cl.Value) Then .Item(cl.Value) = .Item(cl.Value) & IIf(.exists(cl.Value), ",", "") & cl.Row & ":" & cl.Row
    Next
    Range(Join(.items, ",")).Insert
 End With
End Sub

 

[garypea123]

My take on this

Fluff.xlsm
	A	B	C	D
1
2
3
4			3867292	3867292
5			4854915
6			4854915	4854915
7			1692828	4854915
8			3449503
9			3449503	1692828
10			754347
11			4501923076	3449503
12			4501923076	3449503
13
14				754347
15
16				4501923076
17				4501923076
Main

Cell Formulas
Range	Formula
D4:D17	D4	=LET(u,UNIQUE(C4:C12),DROP(DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,FILTER(C4:C12,C4:C12=b),""))),1),-1))
Dynamic array formulas.

I think I may be using an excel version (Microsoft® Excel® for Microsoft 365 MSO) which is too old as it does not like the formula.

I am getting a #NAME? Result

 

[garypea123]

Maybe this works

Sub jec()
 Dim cl
 With CreateObject("scripting.dictionary")
    For Each cl In Range("C5", Range("C" & Rows.Count).End(xlUp))
      If Not .exists(cl.Value) Then .Item(cl.Value) = .Item(cl.Value) & IIf(.exists(cl.Value), ",", "") & cl.Row & ":" & cl.Row
    Next
    Range(Join(.items, ",")).Insert
 End With
End Sub

I have tried this, but it gives a blank after each item, rather after each unique line.

 

[JEC]

It works fine here. The code is bases on column C.
In your picture its column D. Maybe change this line to

For Each cl In Range("D5", Range("D" & Rows.Count).End(xlUp))

 

[garypea123]

It works fine here. The code is bases on column C.
In your picture its column D. Maybe change this line to

For Each cl In Range("D5", Range("D" & Rows.Count).End(xlUp))

Apologies, I was actually using the VBA from Smogcur.

Yes, I can see that your one works for that purpose. However, I was trying to avoid excel creating / inserting new rows as this will affect other formulas.

In an ideal work I had the raw data in Column C, and then I would create some kind of filter which would take this raw data and generate (In column D) the same table but with breaks after each unique entry. However, I would like the raw data to remain as it was.

 

[Fluff]

I am getting a #NAME? Result

Ok, how about

=LET(u,UNIQUE(C4:C12),DROP(DROP(REDUCE("",u,LAMBDA(a,b,LET(f,FILTER(C4:C12,C4:C12=b),r,ROWS(a),s,SEQUENCE(r+ROWS(f)+1),IFERROR(IF(s<=r,a,INDEX(f,s-r)),"")))),1),-1))