Sorting Row-Paired Data (Macro?)

[ForrestGump01]

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.

 

[Peter_SSs]

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

Cell Formulas
Range	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.

 

[ForrestGump01]

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
Headers	Source
Account 001 - Source 1	Source 1
Account 001 - Source 2	Source 2
Account 002 - Source 1	Source 1
Account 002 - Source 2	Source 2
Account 003 - Source 1	Source 1
Account 003 - source 2	source 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

	A	B	C	D	E	F	G	H
Headers	Source
Account 001 - Source 1	Source 1
Account 002 - Source 1	Source 1
Account 003 - Source 1	Source 1
Account 001 - Source 2	Source 2
Account 002 - Source 2	Source 2
Account 003 - source 2	source 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.

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

 

[Peter_SSs]

Glad you something to work for you.