Is there an easy way to do this in Excel?

[Akumos]

Hi All

I have an excel sheet and wondered if there was an easy way to achive this:

The left column has employee numbers, the right column lists pay. Sometimes, an employee has more than one pay, so it lists directly underneath (without displaying the employee number again). I want the pays for each employee to add up and remove the space below. Is this possible? Thanks for looking. On the left is my original table, on the right is how i want it to appear:

|

 

[RCoon]

Look up Sub Totals and Grand Totals in excel, may be something that you are looking for. As for removing the blankety blanks, could be done with some kind of scripted macro.

 

[Akumos]

Look up Sub Totals and Grand Totals in excel, may be something that you are looking for. As for removing the blankety blanks, could be done with some kind of scripted macro.

Thanks.. will look into it.. Any more info on the removing blank rows after getting the totals would be great

 

[de.das.dude]

why not make another column(s) on the right with the extra pay, then display the total of the employee on the right!

 

[Akumos]

why not make another column(s) on the right with the extra pay, then display the total of the employee on the right!

Thanks for the reply.

This is how the data is exported. Is there a way to get the pay in a new column automatically?

 

[lilhasselhoffer]

Getting rid of the blanks is surprisingly easy.
1) Highlight the column
2) F5
3) Special
4) Blanks
5) Right Click on highlighted blank cell
6) Delete entire row


Getting the pay values to dynamically add is another issue. Can't say I'm 100 percent sure there.

 

[Akumos]

Awesome.. thanks for that Will be useful in the future too.

 

[patrico]

yeah i was gonna say the same that someone else has, can you not make extra payments go into next column ( ie 'C") or would that mess with sheet,, or hmm,, or maybe use a formula code that says if this cell is empty use above cell data,

hmm Ive got a big college folder of excell worksheets and see if I can a similar problem with an appropriate solution.

 

[Vario]

One way is:
If ID is blank, go up (vlookup the range above the cell) until ID is full. Fill vertically down so that all blanks are full. Then create a pivot table that summarizes all values for each ID.


edit: an easier way
http://www.techrepublic.com/blog/microsoft-office/quickly-fill-blank-cells-in-excel/


I'd still use a pivot table after you have ID fields populated.

 

[Drone]

You can use IF operator/function. Here's the thing:

for ith row
if ai is blank then bi-1:=bi-1+bi delete ith row

I'm not familiar how it works in excel but idea is right