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?

 

[thirdeye85]

In the screenshots you are correct, I did not sort the appropriate column. I've supplied another screenshot for you with the Date2 sort and it still yields the same result. Here is a sample formula from the G14 column. "=IF($C14=G$1,$D14-$E14,0)" As this is a bookkeeping spreadsheet the formula is designed so that accounts are allocated in very specific ways to yield results of either debit or credit. If we flip it by sorting you can see that the numbers in many of the columns now yield different data. This is all based around the IF function that it was built from. The original reason for the post was to see if I could create static "groups" maybe based upon even / odd rows that would migrate when the sort function was utilized. Would it be easier if I uploaded a mini-sheet so that you can see the formulas?

 

[Peter_SSs]

From what I can see in the post 11 images, the only sorting that I can see is still in column B. The only difference to the original image is that the sorting in column B is now in descending order whereas it was in ascending order earlier.

Another thing that looks different is that now there appears to be a Total row just below the headings before the sorting. That is not apparent in the original images.

Would it be easier if I uploaded a mini-sheet ..

Yes it would!! That is virtually always better because we can also copy the data to test with.

Can you please also confirm whether or not the whole table is a 'formal' Excel table, usually created via the 'Insert' ribbon tab -> Table
It looks that way from the row banding. On the other hand when a new column is added on the right (like our Date2 column), that banding formatting is normally automatically extended to the new column but is not happening in your image.

 

[thirdeye85]

Double_Entry_21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Notes	Date	Account	Debit	Credit	$0.00	Income	South State	Capital One	Suntrust	Automobile	Food	House	Other	Wages	Date2
2						TOTAL ROW	$4,556.00	$14,754.49	-$0.04	$77,800.00	$34.00	$4.56	$0.00	$12.99	$3,000.00	1/1/21
3	Opening Balance	01/01/21	South State	$3,400.00			$0.00	$3,400.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/1/21
4			Opening Balance		$3,400.00		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/1/21
5	Opening Balance	01/01/21	Opening Balance	$350.00			$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/1/21
6			Capital One		$350.00		$0.00	$0.00	-$350.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/1/21
7	Opening Balance	01/01/21	Suntrust	$78,000.00			$0.00	$0.00	$0.00	$78,000.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/1/21
8			Opening Balance		$78,000.00		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/2/21
9	Date night hot dogs	01/02/21	Food	$4.56			$0.00	$0.00	$0.00	$0.00	$0.00	$4.56	$0.00	$0.00	$0.00	1/2/21
10			Capital One		$4.56		$0.00	$0.00	-$4.56	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/2/21
11	Flowers	01/02/21	Other	$12.99			$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$12.99	$0.00	1/2/21
12			Capital One		$12.99		$0.00	$0.00	-$12.99	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/3/21
13	Pay Day	01/03/21	South State	$3,456.00			$0.00	$3,456.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/3/21
14	Contract Work		Income		$3,456.00		-$3,456.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/3/21
15	Gas for visiting	01/03/21	Automobile	$34.00			$0.00	$0.00	$0.00	$0.00	$34.00	$0.00	$0.00	$0.00	$0.00	1/3/21
16			Capital One		$34.00		$0.00	$0.00	-$34.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/3/21
17	Money for savings	01/03/21	South State	$200.00			$0.00	$200.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/3/21
18			Suntrust		$200.00		$0.00	$0.00	$0.00	-$200.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/3/21
19	Paid off credit card	01/03/21	Capital One	$401.51			$0.00	$0.00	$401.51	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/3/21
20			South State		$401.51		$0.00	-$401.51	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/4/21
21	Test opening balance	01/04/21	South State	$10,000.00			$0.00	$10,000.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/4/21
22			Opening Balance		$10,000.00		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/5/21
23	Payroll	01/05/21	Wages	$3,000.00			$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$3,000.00	1/5/21
24	Pay Mr Bill		South State		$3,000.00		$0.00	-$3,000.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/5/21
25	Income	01/05/21	South State	$1,100.00			$0.00	$1,100.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/5/21
26	Optum		Income		$1,100.00		-$1,100.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	1/5/21
27							$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
General Ledger (Personal)

Cell Formulas
Range	Formula
F1	F1	=SUBTOTAL(9,D:D)-SUBTOTAL(9,E:E)
G2	G2	=-SUM(G3:G3000)
H2:O2	H2	=SUM(H3:H3000)
P2:P26	P2	=IF(B3="",P1,B3)
G3:O27	G3	=IF($C3=G$1,$D3-$E3,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A38:E1377,A3:D31,G4:N1377,F34:F1377,A33:B36,C36:E36,H3:O3,O4:O178	Expression	=ISEVEN(ROW())	text	NO
A38:E1377,A3:D31,G4:N1377,F34:F1377,A33:B36,C36:E36,H3:O3,O4:O178	Cell Value	=0	text	NO
E3:F31,F34,F37,F40,E43:F43,E46:F46,E49:F49,E52:F52,E55:F55,E58:F58,E61:F61,E64:F64,E67:F67,E70:F70,E73:F73,E76:F76,E79:F79,E82:F82,E85:F85,E88:F88,E91:F91,E94:F94,E97:F97,E100:F100,E103:F103,E106:F106,E109:F109,E112:F112,E115:F115,E118:F118,E121:F121	Expression	=ISEVEN(ROW())	text	NO
E3:F31,F34,F37,F40,E43:F43,E46:F46,E49:F49,E52:F52,E55:F55,E58:F58,E61:F61,E64:F64,E67:F67,E70:F70,E73:F73,E76:F76,E79:F79,E82:F82,E85:F85,E88:F88,E91:F91,E94:F94,E97:F97,E100:F100,E103:F103,E106:F106,E109:F109,E112:F112,E115:F115,E118:F118,E121:F121	Cell Value	=0	text	NO
G3:N1377,O3:O178	Expression	=ISEVEN(ROW())	text	NO
G3:N1377,O3:O178	Cell Value	=0	text	NO

 

[thirdeye85]

Can you please also confirm whether or not the whole table is a 'formal' Excel table, usually created via the 'Insert' ribbon tab -> Table

I am not the original author of the spreadsheet so I cannot confirm.
Thank you for your continued assistance!

 

[Peter_SSs]

Thanks for the XL2BB sample.

I am not the original author of the spreadsheet so I cannot confirm.

1. From the XL2BB information it looks like it is not a formal table. You could check by going to the Formula ribbon tab and click on Name Manager and see what you find in there, if anything.

2. When you sort, what do you want to happen with that 'TOTAL' row in row 2?

3. Row 27 looks like it is just a spare row with formulas? What do you want to happen with that row when the data is sorted?

4. The data looks like it is already sorted earliest to latest so what is it you are trying to achieve?

5. If we could do this better with a macro, would that be acceptable?

 

[thirdeye85]

Thanks for the XL2BB sample.

1. From the XL2BB information it looks like it is not a formal table. You could check by going to the Formula ribbon tab and click on Name Manager and see what you find in there, if anything.

2. When you sort, what do you want to happen with that 'TOTAL' row in row 2?

3. Row 27 looks like it is just a spare row with formulas? What do you want to happen with that row when the data is sorted?

4. The data looks like it is already sorted earliest to latest so what is it you are trying to achieve?

5. If we could do this better with a macro, would that be acceptable?

1. Formula > Define Name > Names in workbook yields no data. Are there benefits to creating a formal table vs this?

2. That's a great question and I appreciate you addressing that. I would prefer that the TOTAL row stay completely static when the data is sorted. Currently, the TOTAL row will shift depending on how it is sorted, which defeats the purpose of having it as a reference in the first place if it is always shifting.

3. Row 27 would technically be the start of the next transaction edited. I suppose it would make sense that when sorted that is not moved but rather static in this position.

4. You are correct, the data is already sorted earliest to latest in this example, and it is just that an example (although a poor one!). In the course of my bookkeeping entry sometimes transactions with earlier dates are added later sequentially and this makes cross referencing transactions cumbersome. I would like the ability be able to sort that data again so that it goes from earliest to latest.

5. I am not familiar with macros, are there limitations involves with macros vs tables? Yes, I am open to learning more about them.

Thank you!

 

[Peter_SSs]

In a copy of your workbook, give this macro a try.
- It swaps rows 1 and 2. This gets the Total row out of the sort area, and probably makes it stand out a little more anyway.
- It creates data in column P that you can use to test if the sorting (ascending or descending) by that column is any use to you.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select this macro name & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub PrepareForDateSorting()
  Application.ScreenUpdating = False
  ActiveSheet.AutoFilterMode = False
  Rows(2).Cut
  Rows(1).Insert
  With Range("P2:P" & Range("O" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(C2="""","""",IF(B2="""",P1,TEXT(B2,""yyyymmdd"")&TEXT(COUNTIF(B$2:B2,B2),""-00000"")))"
    .Value = .Value
    .Cells(1).Value = "Sort Helper"
    .Columns.AutoFit
    .EntireRow.Resize(, .Column).AutoFilter
  End With
  Application.ScreenUpdating = True
End Sub

 

[thirdeye85]

Getting closer, did come up with some errors though.

 

[Peter_SSs]

I missed before that you were using Mac and I think that might be the reason. I don't use a Mac so I can't debug there.

Can you click Debug on that error and confirm that it is this line causing the error?

.EntireRow.Resize(, .Column).AutoFilter

Can you also confirm whether or not your data already has AutoFilter set up?

 

[thirdeye85]

I missed before that you were using Mac and I think that might be the reason. I don't use a Mac so I can't debug there.

Can you click Debug on that error and confirm that it is this line causing the error?

.EntireRow.Resize(, .Column).AutoFilter

Can you also confirm whether or not your data already has AutoFilter set up?

I can confirm that is the line causing the error.

By autofilter you are referring to home > sort & filter. Filter is checked and everything on row 1 can be clicked and the filter can be accessed. So yes, I believe autofilter is on if that is what you are referring to.

On a happier note, I have since figured out that by simply creating a table and using the the table ribbon to uncheck Total Row, it removes the row and allows me to insert data without "right clicking" and inserting all of the time. Afterwards, I simply reenable the row and it applies the formula all the way down. I am also just using 1 line of data entry and 1 additional column instead of creating 2 separate rows. This allows sort to work!

If you like we can continue to troubleshoot; however currently everything is working as anticipated!

Thank you so much for your help..