Need to summarize data by date and multiple criteria

[barnett2000]

Hello.

The attached files shows a table of raw data. I need to summarize the data by Company, Date, City, and State, so I can then use that to populate use tax reports. The first tab has the raw data, the second tab is an example of what the output needs to look like.

Each Company needs to have a list by date of the city, state, and amount. The raw data may have multiple dates and lines for each date.

Date Company City State Amount
5/9/2022 ABC Inc Colorado Springs CO 67,686.50
5/18/2022 ABC Inc Colorado Springs CO 14,801.04
5/24/2022 ABC Inc Colorado Springs CO 27,955.13
5/27/2022 ABC Inc Colorado Springs CO 33,223.47


Thanks in advance!

David

[Squeaky]

Hi barnett2000,
Here is my solution. I put some sorting formulas in the grey boxes. Since the date, company, city, and state are consistent I simply merged those into a single column (q), then pulled the price as is (r). In (s) I used a formula that counted the duplicates, where the initial instance is always number 1. The (t) column sums the identical instances but only if it has a 1. On the Output tab I set up a simple vlookup using the same merging technique, looking at column (q) on the ED tab, pulling the total from (t).

Also, if you go to the output tab and insert columns between the date and company and the company and the city, it will match the layout from the export data tab. Then you can just copy paste the date, company, city, and state, directly from the export data tab into the output at the first available row, then copy down the formula in (g) to get its total.

Cheers.
-Squeaky.

[barnett2000]

Thanks, Squeaky. The output tab was just a sample of what the output should look like. A vlookup won't work, because there wouldn't be any "output" to do a lookup to until it was created.

However, I think what you did on the first tab might help me solve the issue. I appreciate your idea and I'll work on it more this weekend.

[Squeaky]

Hmm. As an experiment if you go to the output tab and drag the formula in e14 down a bit like 20 rows, it represents a vlookup formula with nothing to look up, which results in blanks. Now copy the information in cells a5 to d 14 into a15 to d24. The vlookups will populate. So I do not understand your statement about output needing to be there first.

Squeaky.

[alansidman]

An alternative solution is with Power Query

Please Login or Register  to view this content.

Excel 2016 (Windows) 64 bit

	G	H	I	J	K
4	Date	Company	State	City	Totao
5	5/2/2022	GHI Corporation	CO	Aurora	78.61
6	5/2/2022	ABC Inc	CO	Denver	837.9
7	5/3/2022	ABC Inc	CO	Denver	-495
8	5/9/2022	ABC Inc	CO	Colorado Springs	67686.5
9	5/9/2022	ABC Inc	CO	Denver	555.9
10	5/10/2022	GHI Corporation	CO	Canon City	16639.73
11	5/11/2022	ABC Inc	CO	Arvada	208
12	5/16/2022	ABC Inc	CO	Vail	15208.82
13	5/16/2022	ABC Inc	CO	Avon	25961.4
14	5/16/2022	ABC Inc	CO	Littleton	243.53
15	5/17/2022	GHI Corporation	CO	Sterling	12132.79
16	5/17/2022	ABC Inc	CO	Arvada	-208
17	5/18/2022	ABC Inc	CO	Colorado Springs	14801.04
18	5/18/2022	ABC Inc	CO	Highlands Ranch	15127.12
19	5/18/2022	ABC Inc	CO	Denver	13414.78
20	5/19/2022	ABC Inc	CO	Carbondale	837.9
21	5/24/2022	ABC Inc	WY	Casper	14734.01
22	5/24/2022	ABC Inc	CO	Frisco	16865.06
23	5/24/2022	ABC Inc	CO	Edwards	24629
24	5/24/2022	ABC Inc	CO	Colorado Springs	27955.13
25	5/24/2022	ABC Inc	CO	Monument	14168.89
26	5/25/2022	ABC Inc	CO	Cotopaxi	41.16
27	5/26/2022	ABC Inc	CO	Estes Park	298.65
28	5/27/2022	ABC Inc	CO	Berthoud	16057.89
29	5/27/2022	ABC Inc	CO	Boulder	15757.17
30	5/27/2022	ABC Inc	CO	Colorado Springs	33223.47
31	5/31/2022	GHI Corporation	CO	Estes Park	18237.11

Sheet: Output

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[oeldere]

With a pivot table.

See the sheet output.

See the attached file.

[barnett2000]

Let's say that there was no output tab. How would I get the information I want from the first tab into a format similar to that in the output tab?

[Limor_OP]

Hi,
Maybe something like that?

G5:
=UNIQUE(FILTER(SORT((FILTER('Export Data'!E7:J75,'Export Data'!E7:E75<>""))),{1,0,1,0,1,1}))


K5 and down:
=SUMIFS('Export Data'!$M$7:$M$75,'Export Data'!$E$7:$E$75,Output!G5,'Export Data'!$G$7:$G$75,Output!H5,'Export Data'!$I$7:$I$75,Output!I5)

[Squeaky]

With my solution there are several ways to approach it. One would be to create your output tab, then copy/paste the formula from the "template" into the same column and row you copied it from, then drag down. (So on the Export Data tab, copy from Q7-T7 and paste it into Q7-T7 on the new Export Data tab, then drag down.) For the formulas in the Output tab, if you copy/paste it will take the link to the original Export data tab with it so instead you can highlight the formula without the equals sign, copy it, then in the new Output tab in the same cell, enter an equal sign and paste in the rest. Hit enter then drag down. If that is too much bother a macro can be set up so you can push a macro button and all of this will put itself in, but then you will have a macro document.
-Squeaky

[Limor_OP]

Squeaky - I think that columns A-D "Output" tab are not supposed to be manual, he wants all of them automated as well (based on "Export Data")