Excel Question

[alohaakamai3]

I have some data being output into a CSV from an application that looks something like this:

EmpNum Name Category Price

300 Bill Hot Dogs 13.00
530 Ron Burgers 5.00
300 Bill Burgers 18.00
530 Ron Hot Dogs 25.00

What I would like to accomplish is to basically consolidate the data so that functions can be performed on it (mostly just averages or sums). In plain English, I am imaging a function that went something like this:

In each instance where the Employee number is the 300 and the category is Hot dogs, add them together in this sell labeled TOTAL HOTDOG SALES.

In other words, I have 2 criteria that need to be checked (and employee number and a category). I know the SUMIFS will do something exactly like this, but there is a snag in this case. The employee number could be anything from 1-1000 in the csv we will be using for input, so I really need a function that will do the above example every time a number is the same and a certain category is present, without my having to specify exactly what the employee number might be. So in english, my function would not read similar to this:

In each instance where employee numbers are the same and category is equal to "Hot Dogs", add them together as TOTAL HOTDOG SALES.
In each instance where employee numbers are the same and category is equal to "Burgers", add them together as TOTAL HAMBURGER SALES.
etc...
etc..

The category field will be relatively constant- for example, there might be Hot dogs, hamburgers, salads, beverages, etc. So that part could actually be a literal string/specific value in my functions, since I will want a separate function to report on data for each category anyway.

Trying to not get too fancy here and would prefer to stick with just Excel, but I appreciate any input and/or help.

 

[SkipVought]

Hi,

Use a Pivot Table. Sum on Category.

 

[alohaakamai3]

Thanks Skip, I think a pivot table is the way to go to, but to make it work, I had to reorder the spreadsheet manually to get the format I wanted in terms of output. And it did do pretty much what I wanted to do, but the problem is the actual csv that the application spits out doesn't look anything like it.

I will play around with it some more thought, and try using that function. Is there a decent, somewhat easy-to-use utility you can recommend to parse or reorder the original csv in case I still have to end up manipulating it?

 

[alohaakamai3]

Also if you could post an example of the function that would be most helpful. I have found multiple solutions that look they are designed to do what I want, but problem is very few of the samples online deal with a dynamic data range (in my case employee number) that's value is "unknown" or dynamic at the time when the function is written.

 

[SkipVought]

Excel has many built-in features. Two in particular would be helpful.

1) Structured Tables: Insert > Tables > Table. This will create a logical table reference that the Pivot Table will use to "automatically" adjust as rows are added or removed.

2) Pivot Tables: Insert > Tables > PivotTable. This is a powerful analytical and reporting tool. With it you will be able to report the Sum of Price for each EmpNum, Name, Category.

Use Excel HELP to discover how these featurs can make many analysis & reporting tasks SIMPLE.

No function required.