fastest way to combine amounts from two diff cells

  • Thread starter Thread starter andy2112
  • Start date Start date
A

andy2112

I've just inherited a spreadsheet which has three columns o
information: Name, Employee ID and Amount.

Since the employees have two pay periods there are duplicates. I'v
been asked to filter the duplicate names and ID, but the problem is t
total the two pay periods into one sum. There are 2000+ ppl and I'
prefer not to retype and recalculate.

I can Advance filter Name and ID for duplicates but it's this sum tota
for pay periods that's got me.


For example A1 and A2 are the same name. B1 and B2 are the same ID, bu
C1 and C2 are two different amounts.

Any suggestions on how to sum total C1 and C2 into C1. This patter
will apply to the 2000+ employees.

And
 
Since the employees have two pay periods there are duplicates. I've
been asked to filter the duplicate names and ID, but the problem is to
total the two pay periods into one sum. There are 2000+ ppl and I'd
prefer not to retype and recalculate.
Click to expand...

Take a look at Data/Subtotals.
 
I've attempted to apply the data/subtotal application, however it's not
applying to the specific cells.

Essentially, I need to applying a sum formula that will skip the
previous cell when using the fill feature.

For example: (Before)

Jake Smith 4356 459.96
Jake Smith 4356 396.23

(After)

Jake Smith 4356 459.96
396.23

I'm trying to combine these two amount together in one cell. However
this pattern applys for the whole column.

Andy
 
Andy

How about in D1 enter =C1+C2

Select D1 and D2(which is blank) and drag/copy down Column D.

Copy Column D and Paste Special>Values(in place)

Delete Column C.

Now do your Advanced filter for unique names.

Gord Dibben Excel MVP
 
Hi,

I just got a solution submitted that helped me a lot. If I understand
correctly you are dealing with a comparable situation.
See my(VILLABILLA) threat called 'Looking up and filtering out' from
21/1 answered by Frank.

I am not an expert so don't blame me if this is bull...

Try to use the SUMIF function to summarize all money amounts on the
hand of the name or id.

You would just need a list with all id's and then with the SUMIF
function you find all amounts related to the specific id's or name and
the amounts are summed in the cell next to the id's or names.

I hope it is of any help

Robby
 
Thanks Robby and everyone for your suggestions,

The SUMIF is exactly what I needed for it to work!

Cheers,

Andy
 
Back
Top