Finding differences in large data

J.ly.94

New Member
Hi there,

Browser of the forums for a while, first time poster.

I've hit a roadblock and my head is completely puzzled, I don't know how to word my request and I've searched everything to no avail.

I have a data set of ~2000, with columns set out per the example spreadsheet.

What I need is for the names in column B to be grouped, and then if there is any change in the salary from column C, the total difference between the salary from the top row to the bottom row.

For example, Kelly Jones has three entries, the salary in C6 = 50000 , the salary in C8 (which is the final iteration of her name in column B) is 51000 therefore the difference is 1000.

There aren't the same amount of records for every employee. The LIVE data I am using has dates in, too, if that helps. but I've left this out as the dates are within a set range.

I can think of no way to get this difference, I have tried to use the subtotal but can't see a difference option, var wasn't especially helpful either.

Bearing in mind there is ~2000 lines to trying to do this manually would take ages.

Josh

P.s. The data I am using is mock data with made up names and amounts.

Attachments

• 8.6 KB Views: 5

vletm

Excel Ninja
J.ly.94
What is expected result for 'Mike Johnson'?
... and how do You get it?

J.ly.94

New Member
Hi, so M. Johnson would be 500.
Basically, all I'm doing is taking the first and the last salary and finding the difference, New - Old = difference.

vletm

Excel Ninja
J.ly.94
Okay, You do that way...
So? What would mean Your sentence?
the total difference between the salary from the top row to the bottom row.
Values are 65000 | 68500 | 62100 | 65500
(68500 - 65000) + (62100 - 68500) + ( 65500 - 62100 ) =
Should this solve as above? ... as I understand Your needed result.
If there is one value ... do it give 'correct' result?
Do You know, what really needs? ... need to get?
( above could give something - the results could be positive ... but the key is, what do someone really need? )

J.ly.94

New Member
Sorry, I'm not explaining myself well, apologies...

I need to find the total difference between the highest most value for an employee versus the lowest most value for the employee, for every employee, there are around 350 employees with around 2000 lines of data, and I would need to go through each employee individually to get this, but is there a quick way to find this?

vletm

Excel Ninja
J.ly.94
This way few minutes, if wants to format those numbers.
( time is ... from open file to save it )
How long time it should take?

Attachments

• 12.8 KB Views: 4

J.ly.94

New Member
Did you use an automated function for this or manual?

vletm

Excel Ninja
J.ly.94
Something like below ... manually:
1) open file
2) select columns A-C
3) Data > Pivottable > select Location F1
4) Pull 'Full Name' to 'Row Labels'
5) Pull 'Annual Salary' TWICE to 'Values'
6) Edit both above 'Values'
6a) for Summarize by Max & Min
7) write formula to cell i3 = g3-h3 and copy down as needed

J.ly.94

New Member
This might be exactly what I needed, I thought about using pivot table, but haven't fully discovered their uses. I am out of office tonight, but I will give it a shot tomorrow and let you know. Thanks so much!

bosco_yip

Excel Ninja
Formula solution as in :

1] In E2, copied down :

=IFERROR(INDEX(B\$2:B\$22,MATCH(0,INDEX(COUNTIF(E\$1:E1,B\$2:B\$22),0),0)),"")

2] In F2, copied down :

=IF(E2="","",SUM(AGGREGATE({14,15},6,C\$2:C\$22/(B\$2:B\$22=E2),1)*{1,-1}))

Regards
Bosco

Attachments

• 11.4 KB Views: 5

J.ly.94

New Member
Bosco, you're bloody fantastic! I'd never have thought about using that formula, since working low-level with excel my skills have diminished. But still, that formula is over my head. I need to brush up on my skills!

Thanks v much

J.ly.94