Pulling Unique Data from a Table A Certain Number of Times (Advanced)

[NathanGMcG]

Hi Everyone,


I don't think this problem is actually very difficult. But I'm stumped. If you can take a look for me I'd really appreciate it.


Basically, I need to summarise all the labour hour costing for each month. I have a list of staff who, for their work each day, record the: jobs, codes for that job, and hours for that.


Sample Source Data:


Datestamp Name Job 1 Code 1 Hours
18/08/2017 Chainz Graham 247 - Dixon Stage 5 08-Other
19/08/2017 Jury Herewini 252 - Kay Rd Cutdown 02-Roading 8:00:00
20/08/2017 Adam Young 247 - Dixon Stage 5 08-Other 3:00:00
21/08/2017 Adam Young 250 - 132 Kay Rd Subdivision 03-Stormwater 9:30:00




At the end of each month I want to sum all the hours assigned to each code so that I end up with a tidy list.


Sample Desired Projected Data:


Adam Young 250 - 132 Kay Rd Subdivision 02-Roading 43
Adam Young 250 - 132 Kay Rd Subdivision 03-Stormwater 17
Adam Young 252 - Kay Rd Cutdown 03-Stormwater 40
Chainz Graham 250 - 132 Kay Rd Subdivision 02-Roading 38
Chainz Graham 250 - 132 Kay Rd Subdivision 03-Stormwater 42
Connor Herewini 250 - 132 Kay Rd Subdivision 06-Concrete 29


The difficult thing is that I only want the name, project and code to appear if the worker had hours assigned to that code for the month. So in the final list, I want three Adam's to appear if he worked on three different job codes, and so on for jobs and codes.


Please don't worry about the hours column, a simple SUMIF function can get that job done. I can also get the correct number of names to come up easily, but the project and code I'm having trouble with.




Here's a sample spreadsheet to work with below which you can download:


https://drive.google.com/file/d/0B3g0HucRa0WmX2o5RGwyU3VEYmM/view?usp=sharing




You will see the method I am attempting by the intermediate data in the sheet. You may find a better solution and ditch that method altogether. I'm open to suggestions.

 

[istiasztalos]

Hi Nathan,

I added a sheet to your file on Google Drive

Honestly, I think by far the best option would be to just use a Pivot table, summaries like this are the main feature of a pivot table and it takes just a few clicks to produce what you need.

In case formula solution is definitely needed, there's a neat way to use array formulas to create a distinct list from a data-set, that can be tweaked a bit to create the distinct list from concatenated rows. Once you have that, you can just separate the concatenated unique values.

This array formula (enter with CTRL+SHIFT+ENTER) gives you the list of unique concatenated values (pasted in cell J2 and dragged down)
{=IFERROR(INDEX(CONCATENATE(names,"",jobs,"",codes),MATCH(0,COUNTIF($J$1:J1,CONCATENATE(names,"",jobs,"",codes)),0)),"")}

(edit: I just realized this forum removes the \ characters from the formula above, it's there between each double " marks)

'names', 'jobs' and 'codes' and named ranges for the relevant columns

I used \ to link them, this can be used to separate the 3 columns afterwards using something like these
(K2)name =IFERROR(LEFT(J2,FIND("",J2)-1),"")
(L2)job =IFERROR(LEFT(SUBSTITUTE(J2,K2&"",""),FIND("",SUBSTITUTE(J2,K2&"",""))-1),"")
(M2)code =IFERROR(RIGHT(J2,LEN(J2)-LEN(K2)-LEN(L2)-2),"")