Results 1 to 4 of 4

Thread: Sorting Row-Paired Data (Macro?)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Sorting Row-Paired Data (Macro?)

    Hello All,

    I've got a very large data set (setup layout below), which I am trying to sort without disrupting the adjacent column references... Column "A" has the headers, while columns B+ have successive months worth of data (e.g. Jan, Feb, Mar, etc), however, the data is being sourced from two different places and being compared (testing for variance). I want to parse the two sources of data without losing the links/references in the adjacent columns, and because many of the links in those columns are not absolute, I cannot copy/paste, only cut. There are hundreds of accounts, each currently comprised of 2

    Ex.
    Account 001 - Source 1
    Account 001 - Source 2
    Account 002 - Source 1
    Account 002 - Source 2
    Account 003 - Source 1
    Account 003 - source 2

    I'm trying to split all source 1 and 2 rows apart, so it can look like this:

    Account 001 - Source 1
    Account 002 - Source 1
    Account 003 - Source 1

    Account 001 - Source 2
    Account 002 - Source 2
    Account 003 - Source 2

    Right now I am relying on manually cutting, row by row, the entire row for each "account" source and pasting to a new range, but as you can imagine even using key commands, on a large data set this is taking a lot of time and is prone to manual error.

    Any suggestions for accomplishing this task? Maybe a macro that loops?

    Thank you in advance.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Sorting Row-Paired Data (Macro?)

    This could be done with a macro, but would this manual approach be any use?

    1. Use a vacant column (I have inserted a new column B) & insert a formula like my B2 and copy down.

    Excel 2016
    ABCDEFGH
    1HeadersSourceJanFebMarAprMayJun
    2Account 001 - Source 1Source 1165357579418
    3Account 001 - Source 2Source 2288149605674
    4Account 002 - Source 1Source 1151066679817
    5Account 002 - Source 2Source 2808080808080
    6Account 003 - Source 1Source 1824534203455
    7Account 003 - source 2source 2487411389453

    Sort



    Worksheet Formulas
    CellFormula
    B2=TRIM(REPLACE(A2,1,FIND("-",A2),""))




    2. Now sort the whole table based on that new column.

    Excel 2016
    ABCDEFGH
    1HeadersSourceJanFebMarAprMayJun
    2Account 001 - Source 1Source 1165357579418
    3Account 002 - Source 1Source 1151066679817
    4Account 003 - Source 1Source 1824534203455
    5Account 001 - Source 2Source 2288149605674
    6Account 002 - Source 2Source 2808080808080
    7Account 003 - source 2source 2487411389453

    Sort





    3. Then, if you still need to physically separate the the two groups, cut and paste the second group.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting Row-Paired Data (Macro?)

    Quote Originally Posted by Peter_SSs View Post
    This could be done with a macro, but would this manual approach be any use?

    1. Use a vacant column (I have inserted a new column B) & insert a formula like my B2 and copy down.

    Excel 2016
    A B C D E F G H
    1 Headers Source Jan Feb Mar Apr May Jun
    2 Account 001 - Source 1 Source 1 16 53 57 57 94 18
    3 Account 001 - Source 2 Source 2 28 81 49 60 56 74
    4 Account 002 - Source 1 Source 1 15 10 66 67 98 17
    5 Account 002 - Source 2 Source 2 80 80 80 80 80 80
    6 Account 003 - Source 1 Source 1 82 45 34 20 34 55
    7 Account 003 - source 2 source 2 48 74 11 38 94 53
    Sort

    Worksheet Formulas
    Cell Formula
    B2 =TRIM(REPLACE(A2,1,FIND("-",A2),""))




    2. Now sort the whole table based on that new column.

    Excel 2016
    A B C D E F G H
    1 Headers Source Jan Feb Mar Apr May Jun
    2 Account 001 - Source 1 Source 1 16 53 57 57 94 18
    3 Account 002 - Source 1 Source 1 15 10 66 67 98 17
    4 Account 003 - Source 1 Source 1 82 45 34 20 34 55
    5 Account 001 - Source 2 Source 2 28 81 49 60 56 74
    6 Account 002 - Source 2 Source 2 80 80 80 80 80 80
    7 Account 003 - source 2 source 2 48 74 11 38 94 53
    Sort



    3. Then, if you still need to physically separate the the two groups, cut and paste the second group.
    Hi Peter,

    Thank you for the reply. I wound up doing something similar to this. However, was unable to sort the data by the grouping due to relative references in the subsequent columns (e.g. Account 12345 source 2 might be referencing Account 98765 source 2 located in row 105 without absolute references, so when I would resort the data it would not maintain the proper relative linking). I've already finished manually cut/pasting the data (I used sort/filter to sort by "Source 1", cut/pasted them all individually into a new range, and then using a macro deleted all the newly created blank rows in the remaining "Source 2" range...

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Sorting Row-Paired Data (Macro?)

    Glad you something to work for you.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •