Sort data contained in blocks in excel

Question

I have a large amount of reference data in excel, which I am trying to manipulate in a variety of ways. I'm having some problems with the way it is structured and sorting into a more manageable format.

Problem number 1: I have three columns. Column A contains first a date, and then a designator of high or low. Column B contains times, Column C contains heights.

I would like to sort the data by column B (easy enough) EXCEPT I would like the date headings in Column A preserved. It's almost as though I have 365 tables, each with between 3 and 5 pieces of data - I'm looking to sort the 3 - 5 pieces of data within each date only.

This is what I have currently:

There's no issue with me taking the data and manipulating it some other way first - this is ultimately around me being able to take a batch of data (5x different reference points, each for 365 days) and develop a process to sanitise it and get it displayed in time order, as well as being able to get it into a usable format for problem 2 (I need to adjust some other data points by the sorted data once I have it).

This is what I would like it to look like (I manually went through each of these blocks and sorted them):

you mentioned you want the data sorted by Column C, but the sample output doesn't seem to be sorted by this column. Would you clarify that? Thanks — David Leal, Nov 28 '22 at 15:57
Thanks David - for a pivot table I would pivot it, then sort it? You're correct on the column c issue - that should have read column b. It needs to be sorted by column b. — James Davies, Nov 28 '22 at 16:04
A pivot table doesn't seem to work - because it's seeing the date as a 'heading' for column A, and thus I'd need to put headings on to columns b and c somehow. — James Davies, Nov 28 '22 at 16:10
thanks, yes the output you need is similar to a Pivot table, but it is true that your **input is not in the structure a Pivot expects**. So maybe use Power Query to accommodate the result in the way you wanted. — David Leal, Nov 28 '22 at 16:11
Thanks David. Any pointers on how to get going with a Power Query much appreciated - I looked earlier and the issue seemed to be that I couldn't select my data as an input. I might try saving it as a separate file to work with? — James Davies, Nov 28 '22 at 16:41
add a column for date on each record. then you simply sort by both columns — xQbert, Nov 28 '22 at 17:37

David Leal · Accepted Answer · 2022-11-28T23:27:47.900

2

It is possible to do it in Excel as follows in cell E2:

=LET(rng, A1:C11, set, FILTER(rng, (INDEX(rng,,1) <>"")),
  dates, SCAN("", INDEX(set,,1), LAMBDA(acc, item, IF(ISNUMBER(item), item, acc))),
  in, FILTER(HSTACK(dates, set), INDEX(set,,2)<>""), inDates, INDEX(in,,1),
  out, REDUCE("", UNIQUE(inDates), LAMBDA(acc, date,
    LET(sorted, VSTACK(date, DROP(SORT(FILTER(in, inDates = date),3),,1), {"","",""}),
    VSTACK(acc, sorted)
  ))), IFERROR(DROP(DROP(out,1),-1),"")
)

Here is the output:

You can avoid the clean-up process except for removing the last row as follow:

=LET(rng, A1:C11, set, FILTER(rng, (INDEX(rng,,1) <>"")),
  dates, SCAN("", INDEX(set,,1), LAMBDA(acc, item, IF(ISNUMBER(item), item, acc))),
  in, FILTER(HSTACK(dates, set), INDEX(set,,2)<>""), inDates, INDEX(in,,1),
  out, REDUCE("", UNIQUE(inDates), LAMBDA(acc, date,
    LET(sorted, VSTACK(HSTACK(date,"",""), DROP(SORT(FILTER(in, inDates = date),3),,1), 
      {"","",""}), IF(MAX(LEN(acc))=0, sorted, VSTACK(acc, sorted))
  ))), DROP(out, -1)
)

Explanation

Basically is to carry out the manual steps but using excel functions. The name set, is the same as the input data (rng) but we removed the empty rows. The name dates, is a column with the same size as rng, repeating all the dates. The condition in the SCAN function to identify a new date is ISNUMBER because dates are stored in Excel as whole numbers. The name in has the data in the format we want for doing the sorting and filter by date removing the date header and adding as the first column the dates.

Now we use DROP/REDUCE/VSTACK pattern (check the answer to the question: how to transform a table in Excel from vertical to horizontal but with different length provided by David Leal) to append each sorted data for a given unique date. We add the date as the first row, then sorted data, and finally an empty row to separate each group of data. Finally, we do a clean-up via IFERROR/DROP to remove the #N/A values and the first and the last empty row.

edited Nov 28 '22 at 23:27

answered Nov 28 '22 at 17:21

David Leal

6,373
4
29
56

1

Superb. That does exactly what I wanted. Very very many thanks. – James Davies Nov 28 '22 at 18:52
1

a whole new level of excel understanding. – xQbert Nov 28 '22 at 22:43
Thanks @xQbert just taking advantage of the [new excel functions](https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066) – David Leal Nov 29 '22 at 02:18
@DavidLeal can I use the same sort of method/incorporate into the process the adding of the 'date' into each of the 'times'? Because they originated from plain text, the date for all the cells in column B is 00/01/1900. It would help on the later data use if I could combine them with the actual date (and also if I did that separately, it would mean they sorted logically anyway). – James Davies Nov 29 '22 at 10:39
1

yes, you can try the following: `=LET(rng, A1:C11, set, FILTER(rng, (INDEX(rng,,1) <>"")), dates, SCAN("", INDEX(set,,1), LAMBDA(acc,item, IF(ISNUMBER(item), item, acc))), in, FILTER(HSTACK(dates, set), INDEX(set,,2)<>""), inDates, INDEX(in,,1), out, REDUCE("", UNIQUE(inDates), LAMBDA(acc,date, LET(sorted, DROP(SORT(FILTER(in, inDates = date),3),,1), addDateInfo, HSTACK(INDEX(sorted,,1), INDEX(sorted,,2)+ date, INDEX(sorted,,3)), blockInfo, VSTACK(HSTACK(date,"",""), addDateInfo, {"","",""}), IF(MAX(LEN(acc))=0, blockInfo, VSTACK(acc, blockInfo)) ))), DROP(out, -1) )` – David Leal Nov 29 '22 at 15:22
1

Words cannot express how grateful I am @DavidLeal. That has just made a really laborious task 100000 simpler. Works like a charm. – James Davies Nov 29 '22 at 15:59
1

You are welcome @JamesDavies, today I was helping you in the future you can help others, in the same way, other people from this community helped me a lot in the past. I am glad it worked. – David Leal Nov 29 '22 at 16:01

Sort data contained in blocks in excel

1 Answers1

Explanation