Okay, so I can’t unfortunately go into too much detail due to the sensitive nature of the data, but the short of it is that I’ve got a table I paste into “Source” that I’d like to have two columns sorted out and displayed in “List”. It feels like the kind of thing that’s either way easier than I’m making it, or much harder than I realize. ;?
A gotcha is that the data I’m trying to sort is Time that crosses Midnight (Though I use the 24 Hour Clock) and needs to be displayed as an offset time (By a known total). The ‘Source’ Table can also be a varying number of rows, but I handle that by creating an excessively long ‘List’ using my hackneyed formulas.
An Example of the data would be:
| D | E | F |
|---|---|---|
| BlahxBlah 06:43 | GARBAGE DATA | TEXT X |
| BlahxBlah 02:20 | GARBAGE DATA | TEXT Q |
| BlahxBlah 05:01 | GARBAGE DATA | TEXT F |
| BlahxBlah 03:11 | GARBAGE DATA | TEXT H |
| BlahxBlah 03:11 | GARBAGE DATA | TEXT O |
| BlahxBlah 04:55 | GARBAGE DATA | TEXT E |
What I want is D & F sorted by D -5 Hours. I’ve got a formula to parse the data in D the way I want, but I know I’m doing the data for F Wrong because I want it attached to the data pulled for D.
Here’s what I’ve got so far:
New Column 1|New Column 2|
————|————|
=MOD(TIMEVALUE(RIGHT(‘Source’!D1,4))-TIME(5,0,0),1)|=’Source’!F1|
=MOD(TIMEVALUE(RIGHT(‘Source’!D2,4))-TIME(5,0,0),1)|=’Source’!F2|
=MOD(TIMEVALUE(RIGHT(‘Source’!D3,4))-TIME(5,0,0),1)|=’Source’!F3|
Etc.
Which gives me:
| A | B |
|---|---|
| 1:43 | TEXT X |
| 21:20 | TEXT Q |
| 0:01 | TEXT F |
| 22:11 | TEXT H |
| 22:11 | TEXT O |
| 23:55 | TEXT E |
What I’d prefer is to generate a table on ‘List’ that is the sorted data from ‘Source’. So:
| A | B |
|---|---|
| 21:20 | TEXT Q |
| 22:11 | TEXT H |
| 22:11 | TEXT O |
| 23:55 | TEXT E |
| 0:01 | TEXT F |
| 1:43 | TEXT X |
What I’ve been doing is pulling the data from ‘Source’ Column D without the offset:
=TIMEVALUE(RIGHT(D1,4)
With the column set to Number Type “Time” to get the results in the right format.
Copying and pasting the column “As Values” back into ‘Source’ over the original data. And then sorting D Ascending. At which point ‘List’ is right. Trust me, I know this is horrible and I’m sure there’s got to be a better way. O.o
Bonus: Every time I sort a column, the data in the very first row doesn’t sort properly, it just remains, even if I sort the column with a header (As I considered that it might have been an issue with sort thinking the first row was a header).
