• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.

Thanks for all your help,
Josh

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

Attachments

  • chandooex1.xlsx
    8.6 KB · Views: 6
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.
 
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? )
 
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?
 
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

  • chandooex1.xlsx
    12.8 KB · Views: 4
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
 
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!
 
Formula solution as in :


61244

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

  • Difference between highest and lowest salary.xlsx
    11.4 KB · Views: 7
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
Copy Your data as in Your sample
and
Press [ DIF ]-button to get Your needed results.
( no need to other copies nor no matter number of rows )
With new data ... delete Your old data and copy Your new data ... and press [ DIF ]
 

Attachments

  • chandooex1.xlsb
    17 KB · Views: 4
Back
Top