Automatically Copy Data to Another Sheet and Then Sort and Subtotal

[nolass]

I have an Excel spreadsheet with raw data. My manager wants to see the data sorted 3 different ways--(1) by payment date, (2) by payee, (3) by payer. (1) needs to be subtotaled by quarter and then year. (2) needs to be subtotaled by payee, then payment date, then quarter, then year. (3) needs to be subtotaled by payer, then payment date, then quarter, then year.

Right now I'm doing this manually and it's time consuming. Is there a way to do this in Excel by automating things? I have Excel 2010 and 2016.

I realize this would be easier in Access, but most of the staff is not Access savvy.

I need something that, although it may be "complicated" for me to put together, it is very easy for them to access and analyze.


(cross posting from Redirecting)

 

[Trevor G]

I would look at using multiple Pivot tables. One for each of the required options. As always without seeing an example of your data (without anything sensitive) and how its laid out it's difficult to give you a precise suggestion/solution. Also you could look at some of the Power tools in 2016.

 

[nolass]

I would look at using multiple Pivot tables. One for each of the required options. As always without seeing an example of your data (without anything sensitive) and how its laid out it's difficult to give you a precise suggestion/solution. Also you could look at some of the Power tools in 2016.

Would Pivot Tables give the full details, or just summaries?

I will see if there is a way to remove the sensitive details from the list and then upload later today, if possible.

 

[Trevor G]

You would be able to see all data and then the individual subtotals that could either be collapsed to hide data or extend as necessary. To give an extra polished option you could add Slicers and Pivot Charts and a very interactive dashboard can be created and easily used.

 

[nolass]

Here's an example of how the data is laid out. I need 3 different tabs thereafter: (1) Sorted by payment date and subtotaled by year, (2) sorted by vendor, subtotaled by vendor and year of payment to each vendor, (3) Sorter by Payer, then subtotaled by Payer and year of payment.

 

[Trevor G]

You can do this via a Pivot Table or use Power Tools, what version of Excel are you using? As an example with your raw data with non of your totals added. Select a cell in the table and then select:
Insert Tab and Pivot Table (Select the Table/Range option
Once it shows you it has selected the range/table either you select the table/range or accept and then use the New Worksheet option
From the Field List select the Fields you want to see and include the fields you want to total by dragging them into the totals.
You can then sort and use the subtotals as necessary.

Although you have added some images it would have been useful to have some data to provide examples. If your allowed upload an example workbook to something like OneDrive and provide a link and then an example of a result can be passed back. Alternatively look on YouTube how to create a Pivot Table with Totals etc.

 

[nolass]

Example.xlsx
	B	C	D	E	F	G	H	I
1	Invoice Date	Dt Pd	ABCeck #	Company	Amt	Description	Paid By	CAT
2	4/24/17	4/25/17	4603	Architect	1,000.00		Inv	Arch
3	8/30/17	5/16/18	4666	Engineer	1,350.00		Inv	Arch
4	10/9/17	11/9/17		Engineer	1,150.00	Review Changes	Inv	Arch
5	11/11/19	11/27/19	2685	Architect	3,000.00		Inv	Arch
6	9/1/22	9/2/22	5022	Architect	1,000.00		Inv	Arch
7	9/23/22	9/23/22	CC1234	Reviewing Authority	45.00	Review Fee	Inv	Regulatory
8	9/23/22	9/23/22	CC1234	Reviewing Authority	1.19	Review Fee	Inv	Regulatory
9	10/5/22	10/5/22	5046	Clerk of Court	105.00	Registration	Inv	Regulatory
10	12/9/22	12/9/22	CC1234	Surveyour	7,700.00	Survey of elevation points	Inv	Arch
11	1/24/23	1/24/23	5038	Canoe Construction	10,000.00		Inv	Contractor
12	12/22/22	1/26/23	5040	Architect	1,000.00		Inv	Arch
13	2/24/23	2/24/23	5044	Canoe Construction	77,000.00		Inv	Contractor
14	3/17/23	4/21/23	6789	Windows R US	177,500.00	Windows	SB	Contractor
15	3/17/23	4/21/23	LALA1004	Windows R US	5,500.00	Windows	SB	Contractor
16	4/25/23	4/28/23	5049	Landscape Outdoors	700.00	Landscape Design	Inv	Contractor
17	5/12/23	5/16/23	5053	Canoe Construction	9,000.00		Inv	Contractor
18	5/12/23	5/17/23	5054	Canoe Construction	5,000.00		Inv	Contractor
19	5/12/23	5/17/23	5055	Canoe Construction	5,000.00		Inv	Contractor
20	5/23/23	6/5/23	5060	Canoe Construction Extra	17,700.00		Inv	Contractor
21	6/5/23	6/8/23	5061	Canoe Construction	9,000.00		Inv	Contractor
22	6/5/23	6/9/23	5062	Canoe Construction	577,554.55		Inv	Contractor
23	6/16/23	6/23/23	5063	Landscape Outdoors	1,577.95	Outdoor Lighting and Irrigation Sleeving	Inv	Contractor
24	7/5/23	7/7/23	Fid1214	Lumber & Supply	10,000.00	Framing lumber and sheetrock	SB	Supplier
25	7/5/23	7/7/23	LALA1012	Lumber & Supply	15,000.00	Framing lumber and sheetrock	SB	Supplier
26	7/5/23	7/7/23	ABC 7976	Lumber & Supply	177,745.55	Framing lumber and sheetrock	SB	Supplier
27	8/1/23	8/1/23	Fid1214	Lumber & Supply	17,714.15	Framing lumber and sheetrock	SB	Supplier
28	8/3/23	8/3/23	Fid1214	Lumber & Supply	1,701.55	Framing lumber and sheetrock	SB	Supplier
29	8/10/23	8/10/23	6789	Lumber & Supply	1,515.45	Framing lumber and sheetrock	SB	Supplier
30	8/10/23	8/10/23	6789	Lumber & Supply	540.11	Framing lumber and sheetrock	SB	Supplier
31	7/5/23	8/14/23	6789	Lumber & Supply	(471.71)	Framing lumber and sheetrock	SB	Supplier
32	8/14/23	8/15/23	6789	Lumber & Supply	11,577.31	Framing lumber and sheetrock	SB	Supplier
33	8/23/23	8/23/23	6789	Lumber & Supply	(45.30)	Framing lumber and sheetrock	SB	Supplier
34	8/24/23	8/24/23	CC1234	Lumber & Supply	711.78	Framing lumber and sheetrock	Inv	Supplier
35	8/31/23	9/4/23	CC1234	Lumber & Supply	11,577.01	Framing lumber and sheetrock	Inv	Supplier
36	9/6/23	9/13/23	5070	Canoe Construction	10,000.00		Inv	Contractor
37	9/6/23	9/13/23	5072	Canoe Construction	15,000.00		Inv	Contractor
38	9/5/23	9/19/23	AKSI Whit 9904	Roofing Supply	9,491.50	Roofing Supplies	ABC	Supplier
39	9/12/23	9/22/23	CC1234	Roofing Supply	417,777.57	Metalsdownspouts	Inv	Supplier
40	10/6/23	10/6/23	6789	Lumber & Supply	505.11	Tyvek Wrap	SB	Supplier
41	9/25/23	10/18/23	Aksi ABCCC 3011	Roofing Supply	335.00	Delivery fee	ABC	Supplier
42	10/19/23	10/19/23	ABCCC 9018	Roofing Supply	77,591.70	Roofing Insulation	ABC	Supplier
43	10/12/23	10/20/23	5080	Canoe Construction	71,770.78		Inv	Contractor
44	10/23/23	10/24/23	ABCCC 9018	Roofing Supply	5,955.78	Roofing Insulation	ABC	Supplier
45	10/27/23	10/24/23	6789	Lumber & Supply	(753.57)	Framing lumber and sheetrock	SB	Supplier
46	10/31/23	10/31/23	ABCCC 9018	Roofing Supply	7,794.11	Roofing Supplies	ABC	Supplier
47	11/3/23	11/3/23	5082	Canoe Construction	10,000.00		Inv	Contractor
48	11/3/23	11/3/23	5083	Canoe Construction	15,000.00		Inv	Contractor
49	11/1/23	11/17/23	ABCCC 9018	Windows R US	10,000.00	Windows	ABC	Supplier
50	11/1/23	11/17/23	9904	Windows R US	3,545.00	Windows	ABC	Supplier
51	11/1/23	11/21/23	9896	Windows R US	9,000.00	Windows	ABC	Supplier
52	11/1/23	11/21/23	ABCCC 9018	Windows R US	1,000.00	Windows	ABC	Supplier
53	11/24/23	11/30/23	5087	Canoe Construction	10,000.00		Inv	Contractor
54	11/24/23	11/30/23	5088	Canoe Construction	15,000.00		Inv	Contractor
55	12/1/23	12/4/23	ABCCC 9018	Roofing Supply	(1,939.48)	Roofing Supplies	ABC	Supplier
56	12/14/23	12/15/23	5091	Canoe Construction	3,000.00		Inv	Contractor
57	12/7/23	12/15/23	5090	Canoe Construction Extra	1,300.00		Inv	Contractor
58	1/11/23	1/11/24	YZ 2910	Sand Pumping	1,000.00	(4) loads of sand	Inv	Contractor
59	2/21/24	2/21/24	5097	Arctic Aire	5,000.00	Air Supply Install #1	Inv	Contractor
60	2/21/24	2/21/24	5098	Arctic Aire	5,000.00	Air Supply Install #2	Inv	Contractor
61	2/23/24	2/23/24	ABCCC 9018	Arctic Aire	7,151.93	Air Supply Install #3	ABC	Supplier
62	2/26/24	2/26/24	5100	Canoe Construction	10,000.00		Inv	Contractor
63	3/11/24	3/11/24	5103	Canoe Construction	5,000.00		Inv	Contractor
64	3/12/24	3/12/24	Wh5104	Arctic Aire	4,000.00	Air Supply Install #4	Inv	Contractor
65	3/18/24	3/21/24	5106	Canoe Construction	10,000.00		Inv	Contractor
66	3/18/24	3/19/24	5107	Arctic Aire	400.00	Air Supply Install #5	Inv	Contractor
67	3/21/24	3/22/24	5110	Arctic Aire	17,700.00	Air Supply Install #6	Inv	Contractor
68	3/21/24	3/22/24	5111	Arctic Aire	77,000.00	Air Supply Install #7	Inv	Contractor
69	3/28/24	3/28/24	5113	Canoe Construction	10,000.00		Inv	Contractor
70	4/9/24	4/12/24	5116	Canoe Construction	10,000.00		Inv	Contractor
71	4/12/24	4/12/24	ABC 3011	Camera Co	3,079.23	Cameras	ABC	Supplier
72	4/17/24	4/18/24	ABC 3011	Camera Co	5,770.55	Wires	ABC	Supplier
73	4/18/24	4/18/24	ABC 9018	Security Co	1,594.01	Deposit	ABC	Contractor
74	4/19/24	4/19/24	5119	Camera Co	1,335.55	More Wires	Inv	Contractor
75	5/6/24	5/6/24	ABC 9018	Arctic Aire	3,530.18	Air Supply Install #8	ABC	Supplier
76	4/15/24	5/13/24	ABC 9018	Windows R US	15,000.00	Doors	ABC	Supplier
77	5/21/24	5/21/24	5122	Canoe Construction	10,040.75		Inv	Contractor
78	5/29/24	5/29/24	ABC 9018	Arctic Aire	11,777.34	Air Supply Install #9	ABC	Supplier
Raw

Cell Formulas
Range	Formula
F71	F71	=39.99*77

 

[Trevor G]

Slight delay in replying. What I have looked to do is copy the data to a new sheet then sort by the data column create the year the year column by selecting the date column and reformatting it to just show the year. Then sort the data by year and then use the Data Tab --> In the Outline Group select Sub Totals and then from the options select the Year by Amount it then gives you the first answer to question one then adapt the same for question two and finally question three. It will use the outlining tools and in the screen to the left of the row numbers you will see the grouping tools.

An example of the finished but not necessarily polished solution is shown below:

You can also download the workbook via OneDrive Totals Download

 

[nolass]

Thanks. Sorry for taking so long to get back to you.

I'm familiar with this method.

Is there a way to automate this? Do I just record a macro of me copying, pasting, sorting and then subtotaling? Since I'll need different sheets for year, company and paid by, I'm trying to reduce the amount of manual work I have to do on this, mainly because doing this manually could lead to inconsistencies between the different sheets.