Sorting Row-Paired Data (Macro?)

F

ForrestGump01

New Member
Joined
Mar 15, 2019
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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
Cell Formulas
RangeFormula
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.
 
Upvote 0
Peter_SSs said:
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
HeadersSource
Account 001 - Source 1Source 1
Account 001 - Source 2Source 2
Account 002 - Source 1Source 1
Account 002 - Source 2Source 2
Account 003 - Source 1Source 1
Account 003 - source 2source 2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]Apr[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]Jun[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]74[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]82[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]53[/TD]

</tbody>
Sort​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=TRIM(REPLACE(A2,1,FIND("-",A2),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




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

Excel 2016
ABCDEFGH
HeadersSource
Account 001 - Source 1Source 1
Account 002 - Source 1Source 1
Account 003 - Source 1Source 1
Account 001 - Source 2Source 2
Account 002 - Source 2Source 2
Account 003 - source 2source 2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]Apr[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]Jun[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]82[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]74[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]53[/TD]

</tbody>
Sort​



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

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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Trouble running an access query in excel vba
Replies
2
Views
217
Legal Tender
L
N
  • Solved
Command buttons not sorting - and sometimes jumping around on sort
2
Replies
10
Views
183
netxtown
N
J
  • Solved
Using Index/Match (or Something Else) to Return Row(s) of Data
Replies
4
Views
167
jbennett01
J
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
259
igold
igold
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
453
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top