Summarising Data using a unique list formula

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.

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.

@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.

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

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.

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.

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.

@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.

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)))

@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.

Originally Posted by Fotis1991

...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.

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.

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.

Originally Posted by BB1972

.... 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.

@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.

@ 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!

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.