4

I have 2 parallel (side by side) same-height Excel tables with a completely blank column (no data, nor header) in between them. The rows of a table correspond to the same rows of the other table (so they must stay parallel), but no formulas link them together.

What is the simplest way to sort the entire range (both tables) by any column in either table?

Background: the document is regularly uploaded into a program in which only the left table is needed. The program starts at A1 and stops the upload range at the first blank column, so the column in between must be blank.

EDIT: Simple example - the left table gets automatically uploaded and the right table is there for additional information (for the internal group that has access to the source files). The rows correspond to each other, so sorting by any column in either table should also sort the other table.

External & Internal Data in one spreadsheet

And these must be formatted as tables for the benefit of structured references (which are obviously more complex than this simple example):

Table's Structured-Reference Formulas

CURRENT WORKAROUND: Keep data in simple ranges, allowing to filter across entire range including the blank columns.

enter image description here

Unfortunately, it does sacrifice readability of structured references:

enter image description here

  • Do you mean that if you sort table 1 (occupying columns A-E for example) by column B, then table 2 (occupying columns G-K) must sort by column H? Why don't you select the first table, sort it, and then select the second table, and sort it by its corresponding column? – Paul Nov 9 '17 at 0:29
  • Do you mean that the second table isn't in the workbook but you need to sort both tables? That would be really tough. If you do have both tables, see @Sir 's answer. – Bandersnatch Nov 9 '17 at 18:21
  • @bandersnatch OP said they were side by side with a blank column separating them, indicating they are on the same sheet of the same workbook – Sir Adelaide Nov 10 '17 at 2:46
  • Howdy, @Sir. Yes I got that, but the "background" info talks about only one table being uploaded. I guess I thought there must be something that made this task difficult enough to post the question. But maybe not. :-) I like your answer. – Bandersnatch Nov 10 '17 at 6:43
  • Fellows, I hope my edit clears things up. I went with a workaround, which doesn't fulfill requirements to keep tables. – SAL Nov 20 '17 at 22:49
1

Select the whole lot, then use the sort tools from the ribbon.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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