Forum Discussion
Excel duplicate line meny at the same time and change the signs in a series of doublings.pervasive
I have excel sheet with 500 lines of data how can i duplicate all lines so that there will be the same data in every two (1 and 2 the same 3 and 4 the same) so instead of 500 lines there are 1000 every 2 with the same .
next issu
and change the signs in a series of doublings in some row.
I want to put it all in text here but it's not possible, I think you have to look at what works perfectly in this thread where these down positions are based on Hans Vogelaar answers
works for excel.csv for accounting progarms .
which is a based example of pervasive or similar.
hope that someone who reads this solves simple problems that take hundreds of keystrokes or more because there is time to hit the keyboard too often if you don't need to hit it that often (very often if we are talking about 5000 and not 500)
Run this macro:
Sub Dup() Dim r As Long Dim m As Long Application.ScreenUpdating = False m = Range("A" & Rows.Count).End(xlUp).Row For r = m To 1 Step -1 Range("A" & r).EntireRow.Copy Range("A" & r + 1).EntireRow.Insert Next r Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
15 Replies
A 365 formula that will do the job is
= LET( n, ROWS(Value), k, SEQUENCE(2*n,1,2), idx, QUOTIENT(k,2), INDEX(Value, idx) )With the latest 365 version, it gets better
= TOCOL(HSTACK(Value,Value))The formula creates two value columns side by side and then scans across to create a single column.
As a comment, that works with vector only
It gets more complicated but
= LET( n, COLUMNS(Array), triplicated, HSTACK(Array,Array,Array), singleColumn, TOCOL(triplicated), WRAPROWS(singleColumn,n))triplicates every column of an Array; though the INDEX based formula may be as good.
- Thank you. I like the sort code it interesting and will keep in mind to try
Run this macro:
Sub Dup() Dim r As Long Dim m As Long Application.ScreenUpdating = False m = Range("A" & Rows.Count).End(xlUp).Row For r = m To 1 Step -1 Range("A" & r).EntireRow.Copy Range("A" & r + 1).EntireRow.Insert Next r Application.CutCopyMode = False Application.ScreenUpdating = True End SubTank you, it is easy when you know how.
this works perfectly.
Sub Dup()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Range("A" & Rows.Count).End(xlUp).Row
For r = m To 1 Step -1
Range("A" & r).EntireRow.Copy
Range("A" & r + 1).EntireRow.Insert
Next r
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
......
now I have column with 1000 duplicate numbers every 2 are the same no matter if they have a minus or not. how to make the upper the master and the next down oppsit .( master is minus then the duplicate below plus) and (the other direction if master is plus.)If your data begin in row 1:
Sub Opposite() Const c = "A" ' column with data Dim r As Long Dim m As Long Application.ScreenUpdating = False m = Cells(Rows.Count, c).End(xlUp).Row For r = 1 To m Step 2 Cells(r + 1, c).Value = -Cells(r, c).Value Next r Application.ScreenUpdating = True End SubIf your data begin in row 2, change For r = 1 To ... to For r = 2 To ...
