Duplicate values

[digitalc40]

Hello everyone

Can anybody help me?

I have a sheet that has two columns. Column A has Employee names, Column B contains the number of hours they worked.

I need to eliminate duplicate employee entries and add all of their hours up.

Thanks to everyone, who will explain me, how to solve this!
Darko

 

[xenou]

Here's some ideas with Countif and Sumif. I also threw in a trim function in - so that leading/trailing spaces don't cause problems. I stepped it out so you could see the logic of some of the formulas. I'll bet there are many other solutions to be found here.

Book1
	A	B	C	D	E	F
1	Names	Hours	Count	Count	Sum	Sum
2	David	10	1	1	10	10
3	Laura	10	1	1	10	10
4	Mark	10	2	2	20	duplicate
5	Davit	10	1	1	10	10
6	Sam	10	3	3	30	duplicate
7	Sam	10	3	3	30	duplicate
8	Mark	10	2	2	20	duplicate
9	SAM	10	3	3	30	duplicate
10	Sam	10	1	3	30	duplicate
11	Sam	10	1	3	30	duplicate
Sheet1

 

[xenou]

note: I put a spelling error in too (Davit for David)...in other words, be careful! Excel is going to see spelling variations as unique values.

 

[digitalc40]

Hello again.

Thanks Alexander Barnes, but I don't know, how to get such form, that I want:

WHAT I HAVE: WHAT I WANT:

NAME HOURS NAME HOURS
JOHN 12 JOHN 20
MIKE 3 MIKE 3
SAM 6 SAM 6
JOHN 7 HELEN 10
HELEN 8 CINDY 18
CINDY 9
JOHN 1
CINDY 9
HELEN 2

Any additional help? Please!
Darko[/img]

 

[Norie]

Use Data>Pivot table...

 

[digitalc40]

Hello Norie.

I have never used Pivot table. Can you explain me, how to use it, to get such form. I need to get the result as a copy in new columns, so I can compare it with data from other system.

Thanks.

 

[xenou]

What are the columns all about? Or, rather, what is the form you want?

 

[xenou]

You might like to use the easyfilter add-in...See recommended add-ins at the top in this forum (at the top of the posts) and follow links/instructions.

You could get a list of all names with duplicates such as:
David(1) 10
David(2) 20
David(3) 10
Jill (unique) 10
Mark (unique) 10

Using easy filter, remember that you need an empty row above the data.

Pivot table help:
http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx
http://www.homeandlearn.co.uk/ME/mes9p4.html

 

[xenou]

If I understand, you want the old data in cols A-B and the new data in cols C-D.

This could probably be achieved in various ways with VBA or other tools. I would recommend however, that you just use the same formulas I first showed you, and then sort your data by name, if this is possible...you would then have all your david's together in Cols. A-B, and in Column C-D the same names identified as duplicates, plus the sum of hours shown by the first (non-duplicate) instance of the name:

JOHN 7 JOHN 20
JOHN 1 Duplicate
JOHN 12 Duplicate
MIKE 3 MIKE 3
SAM 6 SAM 6
HELEN 8 HELEN 18
HELEN 10 Duplicate
CINDY 4 CINDY 4

I guess at this point (if you need to) you could just delete the cells with "duplicate" in them in column 3...and the cell next to it in column 4.

Otherwise, you will need to use other tools - a macro maybe, or Microsoft Access.

That is, unless I have missed some easy way to approach this...

Regards.