Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.