Excel duplicate line meny at the same time and change the signs in a series of doublings.pervasive

[Bjarkiwaage]

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)

[#2 · Accepted solution]

@Bjarkiwaage 

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

[#3]

@Bjarkiwaage 

An alternative could be Power Query.

[#4]

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.)

[#5]

@Bjarkiwaage 

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 ...

[#6]

@Hans Vogelaar

Data start in row 2 and colum "G" ( so "A" to "G" )

I cant put in screen shot,,

I get this error message in run of marco

compile error: Invalid next control variable reference ..

Next c  ( c is colored )

[#7]

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

[#8]

@Bjarkiwaage 

Sorry about the mistake, I'll correct my reply.

[#9]

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. 

there is no way i could have written this now but with your tip I was able to fix it

Regards Bjarki W

[#10]

@Bjarkiwaage 

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.

[#11]

Thank you. I like the sort code it interesting and will keep in mind to try

[#12]

@Peter Bartholomew 

As a comment, that works with vector only

[#13]

thank you,, vector I don't understand

[#14]

the one above is old school and it works, but if it's shorter and doesn't use sub codes, maybe it's better

[#15]

@Sergei Baklan 

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.

[#16]

@Peter Bartholomew 

As variant

=LET(k, SEQUENCE( ROWS(data)), CHOOSEROWS( data, SORT( VSTACK(k,k)  )) )