2

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
crono
  • 123

2 Answers2

2

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.

assylias
  • 416
  • 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 02 '12 at 14:37
  • @Excellll absolutely, thanks. That's what happens when you don't test your code / formulae ;-) – assylias Jul 02 '12 at 14:51
0

I had the same issue. To solve it, I created a sort column four characters wide. By doing so I was able to sort keeping my paired rows in alphabetical sequence. For example:

  • APP1 for Apple Computer
  • APP2 for row attached to Apple Computer
  • APP3 for another row attached to Apple Computer, etc
  • BOA1 for Bank of America
  • BOA2 for row attached to Bank of America
Gareth
  • 18,809
Joe Reeves
  • 1
  • (1) It’s not entirely clear how this answers the question.  I can sort-of see where you’re going with this, but answers shouldn’t leave the reader guessing.  Please describe the steps that a user would need to follow to use this solution.  (2) This would be clearer if you used the data in the question rather than making up your own.   (3) What happens if there are more than nine (9) orders for a customer? – Scott - Слава Україні Oct 01 '18 at 17:43