Summarising Data using a unique list formula

[pauldaddyadams]

Hi,

I am trying to summarize data using formulas making the process as automated as possible.

Currently I have a list of Branches, Cost Codes and then Values.

I need to be able to split the values out by either contract or Job. In order to do this if the cost code is either 1104 or 1108 then it is Job, if it isn’t it is contract (a left filter will need to be applied to the cost code as it contains 6 digits as I only need it to look in the first four numbers).

Once it is split by Job and Contract I then need it summarized by branch giving me the total job value and total contact value by branch.

I have completed the attached which does it however I am wondering if there is an easier way to do this?

In green I have used helper columns to split out if its contract or job.

The red hightailed cells then summarize the data.

In particular what I would like is a formula which creates a unique list of branches as I had to use an advanced filter – ideally I would like this automated. This is in column R.

For any formula I would like it to pick up the last row automatically rather than using defined ranges as the number or rows will change each month. Ideally I would also like it to be achieved without the use of macros.

Any suggestions would be welcome.

[oeldere]

Just added an new column (see yellow data) in to make the choise.

After that you only have to make a table of it.

Excel 2007 => insert => table.

After that make an pivot table of it.

See the attached file.

[pauldaddyadams]

Hi,

Thanks for replying. If possible I want to try and avoid pivot tables as I do not tend to use them much. Saying that I may have to in the future so I will explore more tomorrow.

I have found a formula which will give me a unique list - See attached.

The spreadsheet works fine but I want to see if there is a cleaner way of achieving the end result without the need of so many columns?

[BB1972]

Paul,

One approach you could use to make a unique list using a formula, as you have asked to do, is the following:

Please Login or Register  to view this content.

in R13, copied down, adjusting ranges as necessary. (It's shaded green on the attached.)

Hope this helps.

[oeldere]

@pauldaddyadams

Just 3 (help)columns and a pivot table.

I doubt if you can make it faster and more reliable.

If you put Branch in column L and Choise in column M you only have 2 helpcolumn.

With the columns KLM (Dutch National Airplane ) you can make a pivot table.

[FDibbins]

If you want a once-off quick and nasty unique list, highlight the list, copy it off to the side, then use DATA/REMOVE DUPLICATES

[pauldaddyadams]

Thanks Brendan - thats the same formula I used.

@FDibbins, Ideally I want a static list

@oeldere I am very new to pivot tables. Can you reference to them from other workbooks easily? This summary table (or your pivot) is going to be the basis of a lookup from another sheet. In the past when i have referenced to pivot tables using vlookups I always had problems.

[oeldere]

It can be done, but I won't advice you to lookup in the pivot table.

You have to explain more, what you want to achieve, by looking up from another sheet or workbook.

You learn pivot table, by using it.

Once you learned it you will be very, very, happy you know all the possibilities of pivot table.

It is a very, very, powerfull tool in Excel.

And MS Excel expants the functionalities on every new version.

So I suggest take a try, and if you don't get the answer you want to achieve, you know where to get that answer.

[pauldaddyadams]

Going to try and use the pivot table feature more then if it is so powerful. I think its because I use to use it in an early version of excel and it wasnt very good then.

In terms of my problem. I want to try and create the pivot table output but using formulas - is that possible?

In terms of the look up from the difference work book. I have a spreadsheet which lists branches and I pull various information to it from a number of spreadsheets. One of them will be this, pulling in the job and contract revenue by branch.

[oeldere]

Personaly I think PT (pivot table) 2007 is much easier than PT 2003.

Try to get alle data in one werkbook; it's much easier to analize the data.

[newdoverman]

I see that you don't want/like Pivot Tables but they are very useful to learn. Here is another way to arrange your data and use a Pivot Table, eliminating some "clutter" and the summary table.

I hope that the file uploaded properly as I had a terrible time with the uploader.

[oeldere]

@newdoverman

Thanks for uploading your file (it worked as you can see).

Thanks for the support to learn pivot table; I realy liked it.


in #5 I stated

Please Login or Register  to view this content.

Personaly I think this is a better way of storing the data, when you want to use pivot table.

[Fotis1991]

I hope to have no problem to give a suggestion using formulas without helper columns.

=IFERROR(INDEX(LEFT($G$13:$G$1000;3);MATCH(0;INDEX(COUNTIF($R$12:R12;LEFT($G$13:$G$1000;3));0;0);0));0)

=SUMPRODUCT((LEFT($H$13:$H$550;4)="1104")*($J$13:$J$550="")*(LEFT($G$13:$G$550;3)=$R13)*($K$13:$K$550))+SUMPRODUCT((LEFT($H$13:$H$550;4)="1108")*($J$13:$J$550="")*(LEFT($G$13:$G$550;3)=$R13)*($K$13:$K$550))

=(SUMPRODUCT((LEFT($G$13:$G$550;3)=$R13)*($K$13:$K$550))-SUMPRODUCT((LEFT($H$13:$H$550;4)="1104")*((LEFT($G$13:$G$550;3)=$R13)*($K$13:$K$550))))-SUMPRODUCT((LEFT($H$13:$H$550;4)="1108")*((LEFT($G$13:$G$550;3)=$R13)*($K$13:$K$550)))

[oeldere]

@fortis1991

Please Login or Register  to view this content.

Of course not, fortis1991, just join the club.

If I had to choose between your beautiful formula, or (in my option) a simple pivot table I would know what to choose.

But maybe that's just me, whose thinking that way.

Of course we let the OP decide which solution he / she chooses.

[BB1972]

...give a suggestion using formulas without helper columns.

.... and that's why you're a guru, Fotis! (Or at least, it's partly why ) Great stuff.

[newdoverman]

Thank you for the feedback.

The more ways that you learn to solve a problem, the better off you are.

Pivot tables can be confusing but they are very powerful for creating summarizing.

Good luck.

[newdoverman]

Thank you for the feedback.

The more ways that you learn to solve a problem, the better off you are.

Pivot tables can be confusing but they are very powerful for creating summarizing.

Good luck.

[Fotis1991]

.... and that's why you're a guru, Fotis! (Or at least, it's partly why ) Great stuff.

Thank you for your kind words!

@newdoverman

I agree with you. Point is that everyone of us prefer-like to work better using a way. Some we prefer formulas, some Pivots, some vba.. etc...

I know very well that Pivots are a great Excel tool, but never used to work with these and it's difficult to start now.

Βesides being create a new Formula is something that excites me.

[oeldere]

@fotis1991

Please Login or Register  to view this content.

It's not diffucult to start now.

Just start and ask. I will guide you trough the (your) difficulties.

[Fotis1991]

@ oeldere

Thank you for the offer..

I said:

..but never used to work with these and it's difficult to start now

..meaning that i don't like to work with these. I didn't say that i don't know to work with these.

@ pauldaddyadams

We are waiting to tell us what did you did with our suggestions!

[pauldaddyadams]

@Fortis - I didn't receive notification of your post (or any others) but will work on this tonight!

I assumed it couldnt be done without the use of a pivot table so I am impressed it can be!

[Fotis1991]

Paul you are an old member here so to say the true i did''t like at all that we didn't have a feed back from you!

You are welcome and thanks for the feed back.