Sorting column by contents of every 7th row

**excelname** · 12-17-2009, 11:47 AM

I have inherited a list of contacts which is very messy.

It looks about like the attached sample except it is very long.

I would like to sort the list so that it is aphabetical according to the contents of the cells immediately under each tan-colored cell, which I will refer to here as the "Business Name" cells.

I would like to bring along in the sort the contents of the five cells under each "Business Name" cell. That is to say that the unsystematic address, phone, etc. information which appears under each business name should still appear under the same business name after the sorting process.

Thank you for considering my problem.

**martindwilson** · 12-17-2009, 01:08 PM

one way put this in b2 and drag down
=INDIRECT("A"&ROWS($A$1:A1)-MOD(ROWS($A$1:A1),7)+2)
then sort by column b

**excelname** · 12-17-2009, 02:17 PM

OK I solved my own problem but I suspect there was an easier way.

What I did:

1. Recorded a (relative) macro which took the 5 fields under each Business Name and arrayed them in the cells to the right of the Business name (and ran the macro over and over; gotta look up how to loop macros!).

2. Sorted the entire array by the Business Name column to get the order I wanted.

3. Recorded a (relative) macro which inserted 5 enpty rows beneath the first Business Name and then brought the data, now arrayed off to the left, back under the Business Name as it had originally been, then moved to the next Business Name down the list (and ran the macro over and over.)

If anyone knows a more elegant way to do this I'd be interested to hear it.

Thanks.