Summary List

[downwithjunk]

Hey....I'm trying to figure out the best way to make a summary sheet from an export. The software exports the date similarly to the data below:

name	job	cost	date	days
bob	hurst	35	3-Jan	99
bob	miller	72	9-Jan	93
john	race	54	13-Jan	89
steve	hurst	68	18-Jan	84
john	race	52	22-Jan	80
john	stone	65	23-Jan	79
steve	hurst	31	1-Feb	70
bob	hurst	58	3-Feb	68
bob	miller	16	15-Feb	56
bob	miller	16	22-Feb	49
bob	miller	87	3-Mar	39

<tbody>
</tbody>

What I'm looking to do is copy this data into a "data" worksheet, and let a "summary" worksheet crunch the data.

I'm needing 1.) a unique list of name and job, 2.) a summary of job cost for each name/job, and 3.) the latest date on the job with the total number of days from "today". The results I'm looking for is below (though calculated by hand of course.)

name	job list	total job cost	Latest date of job	days since job
bob	hurst	93	3-Feb	68
bob	miller	191	3-Mar	39
john	race	106	22-Jan	80
steve	hurst	99	1-Feb	70
john	stone	65	23-Jan	79

<tbody>
</tbody>

I can figure out bits and piece, but can seem to put them all together. Ideally, I can copy/paste my data into the spreadsheet each week and have the summary worksheet automatically recalculate the info I need.

Any help would be GREATLY appreciated!

 

[DRSteele]

I have a solution for you. I'll paste it here when I'm at a desktop computer. Cheers!

 

[downwithjunk]

Great! Thank you!

 

[AlanY]

this solution with a helper column C;

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	name	job		cost	date	days		name	total job cost	Latest date of job	days since job
2	bob	hurst	bob hurst	35	03-Jan	99		bob hurst	93	03-Feb	70
3	bob	miller	bob miller	72	09-Jan	93		bob miller	191	03-Mar	41
4	john	race	john race	54	13-Jan	89		john race	106	22-Jan	82
5	steve	hurst	steve hurst	68	18-Jan	84		steve hurst	99	01-Feb	72
6	john	race	john race	52	22-Jan	80		john stone	65	23-Jan	81
7	john	stone	john stone	65	23-Jan	79
8	steve	hurst	steve hurst	31	01-Feb	70
9	bob	hurst	bob hurst	58	03-Feb	68
10	bob	miller	bob miller	16	15-Feb	56
11	bob	miller	bob miller	16	22-Feb	49
12	bob	miller	bob miller	87	03-Mar	39
Sheet8

Cell Formulas
Range	Formula
C2	=A2&" "&B2
I2	=SUMIF($C$2:$C$12,H2,$D$2:$D$12)
K2	=TODAY()-J2
H2	{=INDEX($C$2:$C$12, MATCH(0, COUNTIF(H$1:$H1, $C$2:$C$12), 0))}
J2	{=MAX(IF($C$2:$C$12=H2,$E$2:$E$12))}
Press CTRL+SHIFT+ENTER to enter array formulas.