[Solved] Sort by date within a period

[cephas]

Up until now, have maintained a Calc spreadsheet manually. Add/modify/delete rows, shift rows here and there. Can I sort the dates within a period somehow ?

See attached screendump. Yes, some of the dates don't fall within the correct period, but the objective is to be able to sort those dates within a period automatically. Can this be done with a Python script ?

cash_flow_budget.png (96.07 KiB) Viewed 1359 times

[FJCC]

I'm not sure I understand your goal. Maybe you can get what you want by copying down the start date, or end date, of the period and sorting on both that date and the date in column E. It would be easiest to put a header on each column and remove the blank rows.

[cephas]

If I sort B then E, it comes out as per screendump.

cash_flow_budget1.png (127.81 KiB) Viewed 1318 times

Somehow, the dates in E need to have a period assigned, so that a sort on Period (B) then date (E) will give the correct order. There is a sheet with the periods.

cash_flow_budget2.png (74.65 KiB) Viewed 1318 times

If I can use Python in a script to assign the period 'row number' to each row, where there is a date in E. That value would have to be inserted in a (new) Column A. There may be some other fine tuning of course.

[robleyd]

Filter then sort?

Or use a pivot table.

[cephas]

Filter then sort?

Or use a pivot table.

Thanks, I will look at Pivot table; have seen a few examples. Lookup may also work ? - https://wiki.openoffice.org/wiki/Docume ... P_function

[Alex1]

Create a (temporary) column with the dates from both B and E. If at least one of them is always empty, just add them, otherwise use a test to see if one is empty, then use the other one. Then use that column to sort.

[cephas]

The sheet named 'Periods' now has a row number by using =row()

cash_flow_budget3.png (113.76 KiB) Viewed 1227 times

I got an Err:502 when trying to lookup the periods sheet and return the row number. Obviously the syntax or referenced cells are incorrect

Code: Select all

=VLOOKUP(D26,$Periods.$B$2,$Periods.$C$113,0)

[robleyd]

Please note the syntax for VLOOKUP:
=VLOOKUP(SearchCriterion; Array; Index; SortOrder)
SearchCriterion is the value searched for in the first column of the array.
Array is the reference, which is to comprise at least two columns.
Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE or zero if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

[cephas]

The formula only works if there is an exact match on the date, otherwise it returns #N/A

Code: Select all

=VLOOKUP(E26,$Periods.$B$2:$Periods.$C$113,1,0)

I need to do a lookup on the date range, so that if the date is within the range of dates in the sheet "Periods" , it returns the value.

Have tried it with and without the $ sign , in reference to the sheet name and cells.

[FJCC]

This would be far easier to explain if you uploaded a spreadsheet instead of images. Make a copy of your document and delete the data in columns beyond E if you don't want to share that.

[robleyd]

#NA is the same as Error: Value Not Available mentioned in my previous post.

It appears that your first column in the Array may be sorted; in which case you might try setting the fourth parameter, SortOrder, to TRUE.

Although I am not sure that VLOOKUP is going to be a solution for your problem if you want a range of values to be returned; this function only returns a single value.

If you continue to have problems, it would be better if you could upload a sample spreadsheet file - not an image - containing sample data and a description of the result you are seeking. How to upload a file

[Lupp]

Inserting/attaching screenshots to a post instead of an example file is an ingenious way to give very little information using up huge areas and causing lots of net load.
Also volunteers of the forum should be glad to be invited to create themselves the examples needed to test ways to possible solutions.

Getting serious:
Screenshots should only be inserted if the post is about an issue with the view. Even in this case it's mostly better to insert the image into an example file which then is attached.

[cephas]

Thanks for all the help and replies. Point taken about using images. Created some test data.

test.ods

test data

(25.81 KiB) Downloaded 8 times

1. Sheet Periods - Contains the from and to dates of each period, and the row number (which for this exercise I will call the period number).

2. Use VLOOKUP (or some other function) to search through the Periods data. As you can see, it only works where the date is an exact match of a period 'from' or 'to' date.

3. Once the proper period number (row number from Periods sheet) is returned, a sort can be done. Most likely, column A - ascending, Column B - descending, column D - ascending

Checking if a value is within a range at viewtopic.php?t=90294

[Alex1]

A2: =B2+D2, then use the fill handle up to A19 and sort on column A.

[cephas]

A2: =B2+D2, then use the fill handle up to A19 and sort on column A.

I tested that on rows 2 to 6 and it does put the rows in the correct order. Can you explain the 'fill handle' please ? Where/how that is applied.

[robleyd]

fillhandle.png (17.63 KiB) Viewed 539 times

The small square at bottom right of the active cell. Hover the mouse pointer over it and the pointer will change; in my case to a cross. Click to grab the handle and drag as needed.

See also Speeding Up Data Entry.

[cephas]

The small square at bottom right of the active cell. Hover the mouse pointer over it and the pointer will change; in my case to a cross. Click to grab the handle and drag as needed.

See also Speeding Up Data Entry.

Great, thanks. That has the same effect as when I copy cell A and then paste down a column, from A3 to A19

The sort works perfectly, great.

So, in summary VLOOKUP is unable to handle a range of dates and return a value where the search item is WITHIN the range.

[FJCC]

By the way, you can get your period number from the Date column using a formula like this in row 3

Code: Select all

=INT((D3-43177)/28)

Your periods start every 28 days. Your period #0 stared March 18, 2018. That day has the value 43177, which is the number of days since 12/30/1899. Subtract 43177 from any date, divide by 28, take the integer part of the result, and you have the number of 28 day periods since March 18, 2018.

[cephas]

By the way, you can get your period number from the Date column using a formula like this in row 3
..........

Okay thanks, I'll try that.

[cephas]

Thanks for the help with this, it's working fine now.