List to Matrix

[pipelinenyc]

I've been asked to take a list and build it into a "report" like so many before me.
The goal here is to count the number of occurrences that an employee worked on a job, and then put that information into a matrix-style table with a single row for each job.
Below is a simple example of the starting data and the desired outcome. I've a little stumped on my formulas, trying to work with Max, If, Index, Match, CountIfs... and there's just something really wrong!
I also have more data available if it's easier to do the aggregation (counting) in excel rather than be presented with the sums already.

"where there is a match for a job number, first get the max and identify that employee and how many times he is present. then, identify the second most occurrences and the employee. Then the third. etc"

Starting Data:
1.PNG

Final Matrix:
2.PNG

Thanks!

[Pete_UK]

It would help if you attached a sample Excel workbook, rather than pictures of one, so that we don't have to type in all that data BEFORE being able to play about with different formulae.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon (Attachments button) does not work.

Hope this helps.

Pete

[pipelinenyc]

Ah, here's the sample data set. Thanks for the help! Sample data attached.

[Pete_UK]

Are your job numbers really nice sequential numbers like 1, 2, 3, 4, 5 etc., or are they more complex?

Will the data always be sorted by Job Number?

Would it be better to have the individual names across the top of the summary table, with the count (or blank if no contribution) in the appropriate column below?

Pete

[pipelinenyc]

No, the job numbers are not nearly that nice. Something more like "AB2017062817-01" would be our job numbers.

The data sort will always be by Job Number, however that is easily manipulated if necessary.

From a logical standpoint, PROBABLY, but this is to meet an executive request using the format specified. We're talking lawyers and such, they lose their mind with blanks - and also, the true population would have to span over 100 columns if that were the case. They were not satisfied with using a pivot table, unfortunately. If push comes to shove, I would use the pivot table and then have an intern do a lot of copy/pasting to manually create the sheet, but this is exactly what i'm trying to avoid.

[Pete_UK]

I'm about to go out now, so I'll attach what I have worked on, which is along the lines that I suggested.

I don't have time to describe things in detail, but I have used two helper columns (in blue) to identify unique job numbers (which I have changed from yours) and unique names, and then generated the table from cell G9. The formula in H9 can be copied across as far as you need it (until you get blanks), and similar with the formula in G10 - copy down as far as you need. The formula in H10 (which is an array formula) can be copied across and down as needed.

If you need any further explanation please post back, and I'll pick it up later on.

Hope this helps.

Pete

[FlameRetired]

This has a helper column D. It defines the relative ranks within Job Number groups. The formula

Please Login or Register  to view this content.

Then this array entered formula in column G filled down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then just copy that range and paste into I2, K2 ... O2.

Then for the counts this non array formula in column H filled down.

Please Login or Register  to view this content.

Copy that range and paste into J2, L2 ....

[BoredWorker]

Hi pipelinenyc,

my version is no helper column, but the formula a bit long.....

there have three type of formula use,

In F2, (Job Number)

Please Login or Register  to view this content.

It show the unique value from the list.

in E2, (Employee 1)

Please Login or Register  to view this content.

It find the the largest count that in have same job in column F, and return to name (column B)
and when copy to G2, it will find the second largest, and so on...
Roundup is use to keep the large(array, k) in 1,1,2,2,3,3,4,4

in F2, (Count 1)

Please Login or Register  to view this content.

it act same with above, just the result turn to count (column C).

Paste it to other by copy two of them and paste to other column(E:F, paste to G:H etc...)

All formula above is Array Formula
array formula is need to confirm by Ctrl + Shift + Enter instead of Enter only

If not understand, please follow below step:
1, Paste the formula to the cell
2, Double click on the cell
3, press Ctrl + Shift + Enter
It show { sign in front of formula. (ie {=index(.....)

[chief_abound]

modified FlameRetired's formula slightly.

[pipelinenyc]

Thank you all for the input!

Flame/Chief - working with your formulas, I found one small issue when I encounter a duplicate value - for instance, if Bob and Tim both have 4 occurrences on job 1, Flame's work shows me a blank for employee 1, and Chief's work shows me the same employee listed as Employee 1 and 2. I've attached Flame's original work with a modification to recreate the issue I'm seeing.

Bored - thank you also. I found an issue after about 300 rows of data, where the array just wasn't updating, but I might have to point towards the processing power of my machine in relation to the size of the data set. In reality, I am working with a list that is about 6000 rows long with 2500 unique jobs.

[pipelinenyc]

I should have mentioned; when encountering a duplicate value of occurrences on the same job for two different employees, it does not matter which displays first (alphabetic or the order they are encountered is fine), but I do want each of them to display.

[FlameRetired]

In reality, I am working with a list that is about 6000 rows long with 2500 unique jobs.

Yikes! You don't want that many array formulas. They are resource hungry and will slow your workbook down.

Since pivot tables are not an option unless there is a way to resolve this with more helper columns I believe you are going to need a VBA solution.
Unfortunately I don't know enough VBA to help.

I'll keep working on the helper columns approach. Initially that is the what I attempted and failed. So that doesn't look promising.

[pipelinenyc]

On the positive side, this is relatively "one off". We will probably run this workbook about once a year, and then i'm just doing a copy/paste values for the executives to play with.

I tried adding a second helper column and then combining the two with something like C2 & "-" & D2, to produce a helper that looks like "1-1" and "1-2". When I tried modifying your original formula to look at the modified helper column, I just produced error on every row (lol..)

Thanks again!

[FlameRetired]

I should have mentioned; when encountering a duplicate value of occurrences on the same job for two different employees, it does not matter which displays first (alphabetic or the order they are encountered is fine), but I do want each of them to display.

That can be resolved. At this point it might be wasting your time though ... due to the enormity of the array formulas.

See one way to do that in the attached. The helper in column D needs to have a small value added to it to make them unique.

Please Login or Register  to view this content.

Then the final array formula is a bit longer.

Please Login or Register  to view this content.

[Pete_UK]

If the ordering of the names/counts is not important, then you can avoid the use of array formulae with appropriate helper columns. In the attached file I've used columns D and E as helpers, as before - the first helps to identify unique job numbers and the second tags on a unique sequential number to each job number (whatever they are - I've changed them from your simple 1, 2, 3 etc.). These formulae are as follows:

D2: =IF(A2="","-",IF(A2<>A1,MAX(D$1:D1)+1,"-"))

E2: =IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

and can be copied down beyond your list of data - the hyphens will indicate where the formulae are active.

I've used this formula in G10:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),D:D,0)),"")

which when copied down will give you the list of unique job numbers. You can use this formula in H10:

=IFERROR(INDEX($B:$B,MATCH($G10&"_"&RIGHT(H$9,1),$E:$E,0)),"")

to get the names, and this one in I10:

=IFERROR(INDEX($C:$C,MATCH($G10&"_"&LEFT(I$9,1),$E:$E,0)),"")

to get the counts.

Each of these can be copied into cells in alternate columns, i.e. H10 into J10, L10, N10, P10, R10 etc. and I10 into K10, M10, O10, Q10, S10 etc. where the column headings follow the same conventions as those in your example file, up to a maximum of 9_Count, and then the formulae from G10 across can be copied down as far as you need them (to row 19 in the example file). You can see that the ordering is different than in your example, which I have left in the table above, but all the data is included.

Hope this helps.

Pete

[chief_abound]

Power query is simple to solve.

[FlameRetired]

Apparently that is not an option either.

They were not satisfied with using a pivot table, unfortunately. If push comes to shove, I would use the pivot table and then have an intern do a lot of copy/pasting to manually create the sheet, but this is exactly what i'm trying to avoid.