How to match up independently-sorted columns again in Calc

[muddpie]

I had a spreadsheet with 8 columns and over 700 rows which I have frequently re-sorted using the “Data → Sort…” option. I decided that I no longer had need of column “D” and blanked out the contents by selecting the column and pressing “delete”. This left a blank column between columns “C” and “E”.

I then selected some cell somewhere and used “Data → Sort…” to sort the table without thinking, not noticing that Calc was including only some of the columns in the data set to be sorted. So, now, columns “A” through “C” match with each other and “E” through “G” match with each other, but between these two groups of columns, there’s a mismatch.

By the time I realized what had happened, I had saved and closed the file. No, I didn’t have “always save a new version on closing” selected in “File → Versions…”. I also did not have “Always create backup copy” selected in “Tools → Options → Load/Save → General”. (Both are selected now, but that doesn’t solve my immediate problem.) I don’t believe there’s any way I can sort the individual groups such that they match up again (such as, say, “timestamp added” and an auto-incrementing integer field that would always have the same sort order.) I also don’t believe I have any other way of recovering an old version of the file outside of LibreOffice Calc (I’m on Arch Linux, so Windows’ older file version feature isn’t applicable here) though if any of you have any ideas I haven’t considered, I’d love any such suggestion.

I think you probably see my problem, but just to be explicit about my request, Is there any way to go about restoring the match between the two groups of columns (for all or even just a portion of the rows) without having to go through all of them and manually match them back up by hand from information outside of LibreOffice? I have all of the information I need to match them up written by hand in ball-point pen ink on hundreds of popsicle sticks – don’t ask – but I don’t have this information in digital form (unless it’s buried somewhere in some magical metadata LibreOffice Calc keeps somewhere) and I don’t relish the thought of spending hours manually copy-pasting data between spreadsheets row-by-row (or rather half-row-by-half-row) while juggling several hundred popsicle sticks.

Failing some magical solution that just fixes things with a mouse click or two, a way to automate some of the cumbersome copying/pasting and shuffling between spreadsheets would be appreciated. Maybe some process that for each half-row lets me simply select the matching set of cells from the other columns from a drop-down list (maybe with some intelligent type-ahead suggestions or some such) would be a lot better than nothing.

Thanks for taking the time to read through all of this! All suggestions greatly appreciated!

Edit: Should have thought to mention the LibreOffice version number:
Version: 4.4.3.2
Build ID: 4.4.3.2 Arch Linux build-1
Locale: en_US

[LKeithJordan]

There is no easy solution, but I may have a couple of ideas that could help.

First, if there is any way to match the rows in the two blocks of data by visual inspection alone, try to use that ability to your advantage. See if you can create a sort key column for the second block based on the first block using VLOOKUP or other formula-based approaches.

Second, if the first idea is not possible, create a sort key column for each of the data blocks. Assign a key to each row of the first block. This can be as simple as entering the row number or other sequential number (which you could do by formula or autofill).

Next, use whatever method is possible to match rows in the second block to rows in the first block. For each match, enter the sort key of the first block into the corresponding column and row of the second block.

When you are done with whichever of the two approaches you choose, sort the second block on the sort key column. This should re-sync the second block with the first block. Now you can delete the sort keys and re-join the two data blocks.

As you can see, either approach is going to take some work – but hopefully less than consulting your popsicle sticks and copying and pasting half-rows of data.

(Please click the check mark next to my post if you believe it to be the best answer to your question.)