Comparing Differences Between Entries based on Two Other Columns

[cgfrank]

So I've got a complicated spreadsheet.
Column A is a name.
Column E is a field saying "first" "second" "third", etc, based on the number of interactions we do with that person.
Column K is a numerical points value.

Some people from Column A have multiple interactions with us, and each one is labelled first, second, third, etc, and each one will end up with a separate point value in column K, based on their score on the inspection we do.

Now, I'd like to add a column or two in that show the difference between the 1st/2nd/3rd point values based on the name in Column A.

IE
Column A has Joey getting 3 inspections. Column E says 1st, 2nd 3rd. Column K has point values that say 1, 18, 32.

I'd like a column that would be labelled 1-2 (difference between first and second inspections) and the entry be calculated as the difference between the two, so +17. And then a 2-3 column would show a value of +14.

Am I nuts? Is this possible?

 

[cgfrank]

This is a snapshot of the spreadsheet. I want the 2nd last column to show the differences between the last column, based on the 1/2/3 entry in the 5th column.

Column A has hundreds of names in the list, so the formula has to work for the entire sheet. Every person will have multiple visits, so showing the point difference would need to be based on that specific persons points.

[/IMG]

 

[Snakehips]

cgfrank,

Welcome to MrExcel.

Just posting this as your post #2 came up but will post this anyway for comment.

Because your original referred to 1st, 2nd, etc rather than First , Second as text, I have used the former.

Assummes you enter the instance as 1st, 2nd, 3rd etc, which you could control using a data validation drop down list if you wish.

Then the Difference column headrers need to be 1st - 2nd , 2nd - 3rd , 3rd - 4th etc as far as you wish up to a max of 88th - 89th (highly unlikely to be required?)
Note that the instances MUST be separated by a strict space dash space ' - '
Assumes row 1 has headers.
Then you can enter the formula in L2 and drag it down and across as required.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name				Instance						Score	1st - 2nd	2nd - 3rd	3rd - 4th
2	Sid				1st						22
3	Joey				1st						1
4	George				1st						7
5	Sid				2nd						14	-8
6	Joey				2nd						18	17
7	Jack				1st						21
8	Joey				3rd						32		14
9	Joey				4th						23			-9
Sheet9

Cell Formulas
Range	Formula
L2	=IF($E2=TRIM(RIGHT(L$1,4)),SUMIFS($K$2:$K$10000,$A$2:$A$10000,$A2,$E$2:$E$10000,TRIM(RIGHT(L$1,4)))-SUMIFS($K$2:$K$10000,$A$2:$A$10000,$A2,$E$2:$E$10000,TRIM(LEFT(L$1,4))),"")

Hope that helps.

 

[cgfrank]

Ok thats a start....I've hid some rows and columns but heres an idea of what the spreadsheet looks like. I can't get your formula to work. Note that my headers are actually row 3, and data begins in row 4. I tried changing a few characters in your formula to make it work but when I would drag it down/across it would come up with errors in the formula.


By the way, thank you for the help!

[/IMG]

 

[Snakehips]

I see also that you now have the points value in column J rather than in K as previously stated!!

Try this revised formula...

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Name				Instance					Score	1st - 2nd	2nd - 3rd	3rd - 4th
4	Sid				1st					22
5	Joey				1st					1
6	George				1st					7
7	Sid				2nd					14	-8
8	Joey				2nd					18	17
9	Jack				1st					21
10	Joey				3rd					32		14
11	Joey				4th					23			-9
Sheet9

Cell Formulas
Range	Formula
K4	=IF($E4=TRIM(RIGHT(K$3,4)),SUMIFS($J$4:$J$10002,$A$4:$A$10002,$A4,$E$4:$E$10002,TRIM(RIGHT(K$3,4)))-SUMIFS($J$4:$J$10002,$A$4:$A$10002,$A4,$E$4:$E$10002,TRIM(LEFT(K$3,4))),"")

 

[cgfrank]

Ha! Works like a charm! Thanks! Sorry for the initial confusion. I don't know how guys on this site come up with some of the formulas they do....yourself included.

 

[cgfrank]

Okay one more question. I want to conditionally format those 3 columns (K L M) based on value. negatives are green, positives red. However, conditional formatting treats the formula in every blank cell as a number higher than any real number, so it formats those too. How do I make conditional formatting ignore the blanks?

 

[cgfrank]

So basically, the blank cells (which still have the formula) should have no formatting. Just be white.

 

[Snakehips]

Select to use formula to determine cells to conditionally format.

Formula for red... =AND(ISNUMBER(K4),K4>0)

Formula for green... =K4<0

 

[cgfrank]

You're an amazing help. I really appreciate it.

How about another question. On the same spreadsheet, I'd like to add a column between O and P to indicate total number of interactions with each person. So if Joey has 3 rows, 1st, 2nd, and 3rd, I'd like Column P to indicate 3. If Sid had 1st and 2nd interactions, I'd like Column P to say 2.

Simplest way to do this I think is to count the number of times that persons name appears in Column A, or the number of times their employee # appears in column B. This is likely better, as we do have a couple guys with the same first AND last names. Employee #'s are unique.