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:

[TABLE="width: 320"]
<tbody>[TR]
[TD]name[/TD]
[TD]job[/TD]
[TD]cost[/TD]
[TD]date[/TD]
[TD]days[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]hurst[/TD]
[TD]35[/TD]
[TD]3-Jan[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]miller[/TD]
[TD]72[/TD]
[TD]9-Jan[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]race[/TD]
[TD]54[/TD]
[TD]13-Jan[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD]hurst[/TD]
[TD]68[/TD]
[TD]18-Jan[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]race[/TD]
[TD]52[/TD]
[TD]22-Jan[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]stone[/TD]
[TD]65[/TD]
[TD]23-Jan[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD]hurst[/TD]
[TD]31[/TD]
[TD]1-Feb[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]hurst[/TD]
[TD]58[/TD]
[TD]3-Feb[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]miller[/TD]
[TD]16[/TD]
[TD]15-Feb[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]miller[/TD]
[TD]16[/TD]
[TD]22-Feb[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]miller[/TD]
[TD]87[/TD]
[TD]3-Mar[/TD]
[TD]39[/TD]
[/TR]
</tbody>[/TABLE]

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.)

[TABLE="width: 392"]
<tbody>[TR]
[TD]name[/TD]
[TD]job list[/TD]
[TD]total job cost[/TD]
[TD]Latest date of job[/TD]
[TD]days since job[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]hurst[/TD]
[TD]93[/TD]
[TD]3-Feb[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]miller[/TD]
[TD]191[/TD]
[TD]3-Mar[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]john [/TD]
[TD]race[/TD]
[TD]106[/TD]
[TD]22-Jan[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD]hurst[/TD]
[TD]99[/TD]
[TD]1-Feb[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]john [/TD]
[TD]stone[/TD]
[TD]65[/TD]
[TD]23-Jan[/TD]
[TD]79[/TD]
[/TR]
</tbody>[/TABLE]

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.