Results 1 to 8 of 8

Thread: Rearrange my numbers

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Rearrange my numbers

    Hello,

    I need to rearrange the column D & E numbers in the column G & H as per example attached below.

    Example explain rearrange column D number in column G same procedure will be followed by rearrange column E number in column H

    Starting numbers is in D6 = 5 in this number (ADD 5 UPPER EMPTY ROWS) result 5+5 =10 copy this number to in column G row 10 in the cells G10

    For the second number is in D7 = 12 (add last total 10 + 12) result 22 copy this number to in column G row 22 in the cells G22

    For the third number is in D8 = 11 (add last total 22 + 11) result 23 copy this number to in column G row 33 in the cells G33

    And continue till last number find in column D using same method as describe. And same for the column E

    Sheet1 Example

    #E0E0F0 " />#E0E0F0 ;text-align: center;color: #161120">
    ABCDEFGH
    1
    2
    3
    4
    5JorTempEM1C1C2EM2R1R2
    6170/71511
    7270/71123
    8370/71113
    9470/7144
    10570/71595
    11670/715
    12770/717
    13870/716
    14970/71
    151070/71
    161170/7111
    171270/71
    181370/71
    191470/713
    201570/71
    211670/71
    221770/71123
    231870/71
    241970/71
    252070/71
    262170/714
    272270/71
    282370/71
    292470/71
    302570/71
    312670/71
    322770/71
    332870/7111
    342970/71
    353070/719
    363170/71
    373270/714
    383370/71
    393470/71
    403570/71
    413670/71
    423770/715
    433870/71
    443970/71
    454070/71
    46171/725
    47271/72
    48371/72
    49471/72
    50571/72
    51671/72
    52771/72
    53871/72
    54971/727
    551071/72
    561171/72
    571271/72
    581371/72
    591471/72
    601571/726
    611671/72
    621771/72
    631871/72
    641971/72
    652071/72

    #E0E0F0 ;color: #161120">Sheet1





    Thank you all
    Excel 2000
    Regards,
    Moti

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,651
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Rearrange my numbers

    A couple of options. If you want a formula solution, put this formula in G6

    =IF(ROW()=LOOKUP(2,1/(G$5:G5<>""),ROW(G$5:G5))+INDEX(D$6:D$100,SUMPRODUCT(--(G$5:G5<>""))),INDEX(D$6:D$100,SUMPRODUCT(--(G$5:G5<>""))),"")

    copy it to H6 and drag both down the columns as far as needed.

    If you prefer a macro, try:

    Code:
    Sub PutVals()
    Dim c As Long, LR As Long, r1 As Long, r2 As Long
    
        For c = 4 To 5
            LR = Cells(Rows.Count, c).End(xlUp).Row
            r2 = 5
            For r1 = 6 To LR
                r2 = r2 + Cells(r1, c)
                Cells(r2, c + 3) = Cells(r1, c)
            Next r1
        Next c
        
    End Sub
    Both tested in Excel 2000.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearrange my numbers

    Quote Originally Posted by Eric W View Post
    A couple of options. If you want a formula solution, put this formula in G6

    =IF(ROW()=LOOKUP(2,1/(G$5:G5<>""),ROW(G$5:G5))+INDEX(D$6:D$100,SUMPRODUCT(--(G$5:G5<>""))),INDEX(D$6:D$100,SUMPRODUCT(--(G$5:G5<>""))),"")

    copy it to H6 and drag both down the columns as far as needed.

    If you prefer a macro, try:

    Code:
    Sub PutVals()
    Dim c As Long, LR As Long, r1 As Long, r2 As Long
    
        For c = 4 To 5
            LR = Cells(Rows.Count, c).End(xlUp).Row
            r2 = 5
            For r1 = 6 To LR
                r2 = r2 + Cells(r1, c)
                Cells(r2, c + 3) = Cells(r1, c)
            Next r1
        Next c
        
    End Sub
    Both tested in Excel 2000.
    Great!! Eric, both the options are producing results as required.

    Thank you for the support.

    Regards,
    Moti

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Rearrange my numbers

    Here is another macro that you can consider...
    Code:
    Sub PutVals() Range("G6:G" & Application.Sum([D:D])) = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF(@="""","""",REPT("","",@-1)&@)", "@", "D6:D" & Cells(Rows.Count, "D").End(xlUp).Row))), ","), ",")) Range("H6:H" & Application.Sum([E:E])) = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF(@="""","""",REPT("","",@-1)&@)", "@", "E6:E" & Cells(Rows.Count, "E").End(xlUp).Row))), ","), ",")) End Sub
    Last edited by Rick Rothstein; Oct 26th, 2017 at 10:57 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Rearrange my numbers

    Quote Originally Posted by Rick Rothstein View Post
    Here is another macro that you can consider...
    Code:
    Sub PutVals() Range("G6:G" & Application.Sum([D:D])) = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF(@="""","""",REPT("","",@-1)&@)", "@", "D6:D" & Cells(Rows.Count, "D").End(xlUp).Row))), ","), ",")) Range("H6:H" & Application.Sum([E:E])) = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF(@="""","""",REPT("","",@-1)&@)", "@", "E6:E" & Cells(Rows.Count, "E").End(xlUp).Row))), ","), ",")) End Sub
    It just occurred to me that I can make those code lines slightly shorter (about 20 characters or so each)...
    Code:
    Sub PutVals() Range("G6:G" & Application.Sum([D:D])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "D6:D" & Cells(Rows.Count, "D").End(xlUp).Row))))) Range("H6:H" & Application.Sum([E:E])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "E6:E" & Cells(Rows.Count, "E").End(xlUp).Row))))) End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearrange my numbers

    Quote Originally Posted by Rick Rothstein View Post
    It just occurred to me that I can make those code lines slightly shorter (about 20 characters or so each)...
    Code:
    Sub PutVals() Range("G6:G" & Application.Sum([D:D])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "D6:D" & Cells(Rows.Count, "D").End(xlUp).Row))))) Range("H6:H" & Application.Sum([E:E])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "E6:E" & Cells(Rows.Count, "E").End(xlUp).Row))))) End Sub
    Nice Sub!! Rick, Thank you

    Please can you check? It is not transposing last values (6 & 9) are find in column D & E

    Regards,
    Moti
    Last edited by motilulla; Oct 27th, 2017 at 06:39 AM.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Rearrange my numbers

    Quote Originally Posted by motilulla View Post
    Nice Sub!! Rick, Thank you

    Please can you check? It is not transposing last values (6 & 9) are find in column D & E
    Sorry, I completely missed that I had not made the receiving range (left side of the code line) large enough to recieve all of the values that the right side of the code line calculated. Here is the corrected code that now includes all of the values...
    Code:
    Sub PutVals() Range("G6").Resize(Application.Sum([D:D])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "D6:D" & Cells(Rows.Count, "D").End(xlUp).Row))))) Range("H6").Resize(Application.Sum([E:E])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "E6:E" & Cells(Rows.Count, "E").End(xlUp).Row))))) End Sub
    Last edited by Rick Rothstein; Oct 27th, 2017 at 10:28 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearrange my numbers

    Quote Originally Posted by Rick Rothstein View Post
    Sorry, I completely missed that I had not made the receiving range (left side of the code line) large enough to recieve all of the values that the right side of the code line calculated. Here is the corrected code that now includes all of the values...
    Code:
    Sub PutVals() Range("G6").Resize(Application.Sum([D:D])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "D6:D" & Cells(Rows.Count, "D").End(xlUp).Row))))) Range("H6").Resize(Application.Sum([E:E])) = Application.Transpose(Split(Join(Evaluate(Replace("TRANSPOSE(IF(@="""","""",REPT("" "",@-1)&@))", "@", "E6:E" & Cells(Rows.Count, "E").End(xlUp).Row))))) End Sub
    Great!! Rick, many thanks now it is running flawless!!

    Have a good day

    Regards,
    Moti

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •