Creating lists from Raw Data

Good Morning,

Hopefully this is not a repeat post, but I honestly do not know where to even start with my slight dilemma! What I have is a raw data spreadsheet summarizing 2013 work completed. Essentially each line is a work order that was completed. I am trying to do a count and figure out which contractors are consistently working, and which ones are cherry picking work, so I would like to take the raw data I have and create a list by each day number of the year. I have added formulas to my raw data that give me the day number, and now I would like to summarize by contractor name.

I create a pivot table and do a count of the work orders, however this creates an obnoxious table that will be a hassle to sort through. Is there any way to force the pivot table to put contractor name instead of the count field? I can work with that and sort it very easily. Right now I am essentially taking the pivot table, pasting it into another sheet, and highlighting each line, and doing a replace 0 with contractor name. This is a daunting tasks with 300 contractors, and 8 different locations, and I can't help but think there is a better way! Please advise! Thank you in advance.

So i fooled with just manually changing the zeros to the contractor name, and pasting into a second sheet. The problem that I did not think of with that is that whenever you sort a particular day, it sorts the entire sheet, so no matter which day I am looking at I have blanks. I dont care if the contractor names are lined up with one another. I guess essentially I want to create a list from my raw data for each day of the year worked. I am including a very crude example of the raw data and hopeful end game below:


Day # Contractor
1 AB
1 AC
2 AB
2 AD
3 AB
3 AC
3 AD
3 AE


Results:

Day 1 Day 2 Day 3
AB AB AB
AC AD AC
AD
AE

Here is an idea for you using the Pivot Table. The days that the contractor has a contract are listed under each contractor's name. There is also a count of the days:

If you want to achieve this with the help of a formula, find the attached sheet to see if this is what you can work with.

The attached sheet has two formulas...

In D1 (This formula will give you the unique days across the columns)

=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$9),0,0),0)),"")

and then drag across as per the requirement.

An Array Formula in D2 (Array Formula requires confirmation with Ctrl+Shift+Enter)

=IFERROR(INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=D$1,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(D$2:D2))),"")

and then drag across and down as per the requirement.

If you want to create this list on another sheet (say Sheet2), you will need to include the sheet reference (Sheet1!) in the formula.