Insert additional rows into data at specific points

[nick.williams]

I have a series of lines described by coordinates. Each row has a point number and x and y coordinates. I want to be able to insert 2 rows between each line - the first one should be blank and the next one should say "pline". Then I paste this into AutoCAD which plots all of the lines at once. Currently I do this ad hoc with VBA but sometimes it takes a little while and it feels like a formula option would be better. I can't seem to figure out how to make it work. Any suggestions? If the answer is that VBA is the best way, I'll continue to use that.

I've attached an example with 3 short lines. My real data can have a few thousand lines (current file around 5000) with around 100 points each (but variable), so speed could be an issue with a complex solution.

[JohnTopley]

'Nick: try

Please Login or Register  to view this content.

6000+ rows in test

[nick.williams]

Hi John,

I have been using the below. What I was hoping for was a formula solution. In part because I have to do this lots of times and manually filter data to extract each time. If the solution was a formula then it could all be built in more easily.

Please Login or Register  to view this content.

[Bo_Ry]

Please try at G2

=LET(z,A4:C15,r,ROWS(z),p,INDEX(z,,3),n,COUNTIFS(p,1),c,INDEX(z,,1)&","&INDEX(z,,2),y,SCAN(0,p,LAMBDA(c,v,c+(v=1))),s,SEQUENCE(r+n*2),SORTBY(IFS(s>r+n,"pline",s>r,"",1,c),IF(s>r,MOD(s-r-1,n)+1,y),,IFS(s>r+n,0,s>r,-1,1,p),))

[JohnTopley]

@Nick, Sorry - my mis-understanding but the cavalry (Bo_Ry) have stepped in!! (and I don't have 365).

Not sure why a formula makes this easier (no button to press!!) but I don't obviously know the context in which this is done

[nick.williams]

Thanks both. Bo that works great, thank you.

With my real data I am using FILTER with various criteria (that the user can edit) to pull certain lines from a large dataset. This caused your formula to break, but I was able to isolate it to the COUNTIFS function in the variable n, so I changed this to be SUM(IF(p=1,1,0)) instead which worked. The final version is in the form:

=LET(z,FILTER(CHOOSE({1,2,3},A4:A15,B4:B15,C4:C15),C4:C15>=1),r,ROWS(z),p,INDEX(z,,3),n,SUM(IF(p=1,1,0)),c,INDEX(z,,1)&","&INDEX(z,,2),y,SCAN(0,p,LAMBDA(c,v,c+(v=1))),s,SEQUENCE(r+n*2),SORTBY(IFS(s>r+n,"pline",s>r,"",1,c),IF(s>r,MOD(s-r-1,n)+1,y),,IFS(s>r+n,0,s>r,-1,1,p),))

(there will be various FILTER criteria, this example just includes everything)