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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sort​

Worksheet Formulas Cell Formula B2 =TRIM(REPLACE(A2,1,FIND("-",A2),"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.