Aug 16 2022 05:55 AM - edited Aug 16 2022 03:40 PM
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)
Aug 16 2022 06:16 AM
SolutionRun 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
Aug 16 2022 06:53 AM
Aug 16 2022 07:45 AM - edited Aug 16 2022 03:15 PM
Tank 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.)
Aug 16 2022 07:54 AM - edited Aug 16 2022 12:09 PM
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 Sub
If your data begin in row 2, change For r = 1 To ... to For r = 2 To ...
Aug 16 2022 09:24 AM - edited Aug 16 2022 09:58 AM
Data start in row 2 and colum "G" ( so "A" to "G" )
I cant put in screen shot,,
compile error: Invalid next control variable reference ..
Next c ( c is colored )
Aug 16 2022 09:58 AM - edited Aug 16 2022 03:13 PM
I found it c should be r
This works perfectly
Sub Opposite()
Const c = "G" ' column with data
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Cells(Rows.Count, c).End(xlUp).Row
For r = 2 To m Step 2
Cells(r + 1, c).Value = -Cells(r, c).Value
Next r
Application.ScreenUpdating = True
End Sub
....
this code works for line 2 and column G, targeting doubling of all lines, no matter how long they are.
.for those who are looking at this, you need to read and change "A" to the appropriate column and (r) to the line where your data starts reading the comment above
thank you
Aug 16 2022 12:09 PM
Sorry about the mistake, I'll correct my reply.
Aug 16 2022 01:50 PM - edited Aug 16 2022 03:37 PM
thank you,, thank you for the update on programming, it's been a long time since I've been there. and become more than forgetful.
Regards Bjarki W
Aug 16 2022 01:52 PM
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.
Aug 16 2022 02:11 PM
Aug 16 2022 02:28 PM
As a comment, that works with vector only
Aug 16 2022 02:37 PM
Aug 16 2022 02:42 PM
Aug 17 2022 04:59 AM
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.
Aug 17 2022 07:34 AM
As variant
=LET(k, SEQUENCE( ROWS(data)), CHOOSEROWS( data, SORT( VSTACK(k,k) )) )
Aug 16 2022 06:16 AM
SolutionRun 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