Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I get a XLS-File as a database report. The File is in the following format:

   | Customer | Name | ... | Orders
 1 |     6    | ...  | ... | 1234
 2 |          |      |     | 4567
 3 |          |      |     | 8910
 4 |     3    | ...  | ... | 3210
 5 |          |      |     | 8765
 6 |     1    | ...  | ... | 1000
 7 |          |      |     | 1001

I need to sort this thing on a column which is only "filled" in the first line of a "record" (here: Line 1-3, 4+5, 6+7) like "Customer" in this example. Is there a way (without falling back to VBA) to keep the lines together which form a "record" while sorting on them. I know, this is abusing Excel but I have no other choise here.

The expected output after sorting on "Customer" would be:

   | Customer | Name | ... | Orders
 1 |     1    | ...  | ... | 1000
 2 |          |      |     | 1001
 3 |     3    | ...  | ... | 3210
 4 |          |      |     | 8765
 5 |     6    | ...  | ... | 1234
 6 |          |      |     | 4567
 7 |          |      |     | 8910
share|improve this question
up vote 2 down vote accepted

You could create an additional column that fills in the blank and use it for sorting purposes - assuming customer is in column A and the new column in column E, you can put in E2:

=IF(A2="",E1,A2)

and drag the formula down.

Every time you change the data, you would need to make sure the formula is copied to the bottom of the range containing data.

share|improve this answer
1  
This won't work if the record includes more than 2 orders; the third order would return a blank. I think it would work if you used =IF(A2="",E1,A2) instead. – Excellll Jul 2 '12 at 14:37
    
@Excellll absolutely, thanks. That's what happens when you don't test your code / formulae ;-) – assylias Jul 2 '12 at 14:51
    
Thanks I will give it a try tomorrow :-) – crono Jul 2 '12 at 23:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .