Duplicates in colulmn

[bobhc]

Good day all

Old man trying to learn new tricks to keep job, and I am struggling with a problem that I can not find the answer. I have a column with multiple same names in, I will be using this column to supply the data for a graph. How do I get rid of duplicates but in the next column have a cell with the true totals of all instances of the name in so that I can select both columns to make the graph.

Thank you for your time

 

[Luke M]

First, in 3rd column, put this formula:

=SUMIF(A:A,A2,B:B)

and copy formula down.

to get the total value needed for each name.


Second, copy the 3rd column, and Edit - Paste Special, Values only. Now the values are locked, so we can delete the extra data. You can delete column #2 at this point if you wish.


Third, in 4th column, use this formula:

=COUNTIF(A$2:A2,A2)>1

Copy this formula down, then filter on column for "TRUE". Delete all these rows.

You should now have a list of unique names with their total values.


ALTERNATIVE:

May not be as friendly to work with in a graph, but you could make a quick PivotTable to quickly generate the unique list and get the sums.

 

[bobhc]

Luke M

Thank you for your most speedy reply. I will lock my self-away and try out you answer, I will let you know of my success(or lack of)

 

[bobhc]

Luke M

I was doing some thing wrong with your formula and could not understand to get it to work. Your suggestion to use a Pivot-table worked,I think I lost it with the formula as the where I was supposed to paste the formulas.

Many thank for the help and answer

 

[SirJB7]

Hi, bobhc!

Very nice your introduction "Old man trying to learn new tricks to keep job", I liked it so much.

I was wondering if you could learn this first (I hope & wish) new trick... otherwise, just ask again.

Regards and good luck, old man! ;-)

 

[bobhc]

SirJB7

Have been playing around with pivot tables all day (I know it's sad, Saturday and I am trying to learn excel)I think I have a basic understanding and will be busy on Monday creating graphs, might even surprise some people.

One thing I am having some difficulty in understanding is the small area bottom right in a pivot table for report filtering, there are drop downs all over to filter so what is this for??

 

[SirJB7]

Hi, bobhc!

No matter the day neither the time, look at me, Saturday night / Sunday early morning and working. It all depends on how you've build the pivot table, filters may appear at different positions.

There are some links that may be useful for understanding how pivot tables work. Some of them... many here at this site.

The links:

http://chandoo.org/wp/2011/04/20/pivot-table-report-filters/

http://chandoo.org/wp/2011/04/27/update-report-filter-macro/

http://www.youtube.com/watch?v=FW89ITnAq2Q

http://www.pivot-table.com/

If you want, just upload your file (see http://chandoo.org/forums/topic/posting-a-sample-workbook), add a worksheet with your questions or doubts, and get it back soon... I hope that just in time for Monday morning bobhc's showtime ))

Regards!

 

[bobhc]

SirJB

First class links, learning a lot. Many thanks

 

[SirJB7]

Hi, bobhc!

You're welcome. Just advise if need anything Excel/Office related, old man.

Regards!