Sort function with grouping

[thirdeye85]

I was wondering how to sort the data sets in rows so that when they are sorted they are placed alongside other columns according to ascending date (early to latest). I am having difficulty with this I believe because there are empty cells in my spreadsheets organization and I know excel does not always do well with empty cells. This is a bookkeeping spreadsheet so you can imagine the utility of grouping transactions by ascending dates; however, sorting would be useless if the rest of the row data does not come along with it.

Screen shot 1 is how I would like to keep the data organized and you can see screenshot 2 that although the data has been filtered in ascending order the other data has not been grouped along with it.

I guess if there would be a way to "group" rows 2,3 & 3,4 together and then the data is then filtered by DATE then that may work? Is this the best way or are there better ways?

 

[Peter_SSs]

Could you use a helper column like this and sort by Date2?

21 08 28.xlsm
	A	B	C	D	E	F
1	Notes	Date	Account	Debit	Credit	Date2
2		10/08/2021	Food	55		10/08/2021
3			CC		55	10/08/2021
4		1/08/2021	Food	55		1/08/2021
5			CC		55	1/08/2021
Sort help

Cell Formulas
Range	Formula
F2:F5	F2	=IF(B2="",F1,B2)

 

[thirdeye85]

It seems that this would technically work but it makes the process of data entry more tedious. Thanks for the reply.

 

[Peter_SSs]

but it makes the process of data entry more tedious.

How so? It is just a column of formulas.
The column could be pre-populated down as far as you like** with the modified formula below.

21 08 28.xlsm
	A	B	C	D	E	F
1	Notes	Date	Account	Debit	Credit	Date2
2		10/08/2021	Food	55		10/08/2021
3			CC		55	10/08/2021
4		1/08/2021	Food	55		1/08/2021
5			CC		55	1/08/2021
6
7
8
Sort help

Cell Formulas
Range	Formula
F2:F8	F2	=IF(C2="","",IF(B2="",F1,B2))

** Or if your data is in a formal table, as in your image it looks like it might be, the the formula would automatically populate down the table as new rows are added.

 

[thirdeye85]

I was unaware that you shared a Formula - thank you!

However, after trying it I have been unable to get the formula to work. What it does do is computes a number "44477" in the F2 column with the data you presented in YOUR example. Do I need to indicate B2 as a date or something so that it recognizes it? Am I missing something, I literally typed in your formula?

 

[Peter_SSs]

Do I need to indicate B2 as a date or something so that it recognizes it?

Excel uses numbers like that to represent dates so Excel will already be able to use the numbers to sort based on that column. However, it makes sense to format them (the formula results in column F) as dates so that they are meaningful to you.
Select the whole column by clicking the column label (F in my example)
Then press Ctrl+1 to bring up the Format Cells dialog. On the 'Number' tab of that dialog choose 'Date' and then whatever date form you want on the right hand side.
I chose this one in my sheet above

 

[thirdeye85]

Thanks Peter, I was able to implement your formula. Unfortunately, the problem still persists when I filter the date order. You can see the screen shots. I think the root of the issue is that the way the spreadsheet is organized. I'll keep trying different things in the meantime.

 

[Peter_SSs]

I was wondering how to sort the data sets in rows so that when they are sorted

the problem still persists when I filter the date order.

Are you trying to sort the data as stated in post 12 or are you trying to filter the data as stated in post 7?

If you are trying to sort by date, which I think you are, I think that you missed this point in my post 2

use a helper column like this and sort by Date2

In your images just posted, it looks like you are still trying to sort by the date column (column B), not by the Date2 column (column P) as I suggested.

 

[thirdeye85]

On the data tab next to the sort icon there are two buttons which indicate sort from newest to oldest. This is what I am trying to do. As I stated your Date2 column "works" but if you look at my post #7 all it does is sort the "Date1" or B column and C through O, which is the data I want to be moved with the sort function does not get allocated correctly. After following your steps I still am not able to sort this way.

 

[Peter_SSs]

if you look at my post #7 all it does is sort the "Date1" or B column and C through O

As I stated in my last post, looking at your post #7 image, shows that

- the data has been sorted by Date (Column B)

- the data is not sorted by Date2 (column P)

Here is my tiny sample sheet before sorting:

.. and after sorting: