Succeeded in counting distinct values, but can’t get them to display across row

[Zalik]

My source spreadsheet has information on 46 projects across a few hundred rows. I am coding a “quick view” sheet that will summarize key information from each project in one row each - so 46 total rows.

I have easily created the quick view sheet to return only 1 instance of each uniquely named project, to add funding amounts to show the total funding for each, and to return the number of contractors who received funding for each project. It has columns A (helper column to find distinct project names), B(project title, correctly shows each of 46 only once), C(total funding, corrected added for each project title), ... , G (# of different contractors who received funding for that project), and then I want columns H-R to be the list of contractors (11 is the maximum).

So, in G2 I have “=IFERROR(ROWS(UNIQUE(FILTER(‘Project tracker’!$G$2:$G$1001, ‘Project tracker’!$C$2:$C$1001=$B2, “”))),0).

That returns the correct value for how many distinct contractors (listed in Project Tracker sheet in column G) for each project (listed in Funds Tracker sheet in column C). Column B on my ‘Quick View’ sheet has each project named only once (so 46 columns).

If I do in row H =IFERROR(UNIQUE(FILTER(‘Project tracker’!$G$2:$G$1001, ‘Project tracker’!$C$2:$C$1001=$B2,””))),0) it correctly lists all of the contractors only once each. BUT the 0365 dynamic array function spills the results down the column and I get a !SPILL error as the rows below are not blank.

So I tried in row H =IFERROR(UNIQUE(INDEX(‘Project tracker’!$G$2:$G$1001,SMALL(IF($B2=‘Project tracker’!$C$2:$C$1001,ROW(‘Project tracker’!$C$2:$C$1001)-ROW(‘Project tracker’!$C$2)+1),1)),0),””)).

I copy and paste from H2 to R2, changing the last 1 to 2, 3, 4, etc to give the 2nd distinct value, 3rd distinct value, etc. The formula almost works, but is giving duplicate values. (For a project that has 6 rows in my source sheet with 3 contractors receiving funds, for example, instead of getting output of H2-J2 as Alliant. CPHL. RHSP as I want, I will get H2-J2 as Alliant CPHL Alliant, and taking it out further will show full list as Alliant CPHL Alliant RHSP Alliant CPHL RHSP etc...).

Can someone help me figure out how to either get the dynamic array to spill across a row instead of down a column, or to fix the other formula so that it finds only the first of each distinct contractor and no duplicates? MUCH APPRECIATED.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Zalik]

Oh my. Makes sense, let me try to re-create a sample.
Thanks.

[Zalik]

Ok, here's trying to attach sample worksheet.
On the "quick view" page, I am trying to get the columns labeled "mechanisms" to display all of the contractors that are correctly counted in column D, but to show up only once each. Right now, the formula is returning duplicates. So column D correctly tells me how many different contractors received funding, and in the following columns I want to see who all of those contractors are (but shown only once each).
TIA!

[Fluff13]

Maybe
=TRANSPOSE(UNIQUE(FILTER('Project tracker'!$G$2:$G$1001, 'Project tracker'!$C$2:$C$1001=$B2,"")))

[Zalik]

Oh my gosh, that totally worked. I tried transpose, but obviously screwed up the way I wrote it somehow (and am obviously very new to O365 and these dynamic arrays!).
THANK YOU

[Fluff13]

You're welcome & thanks for the feedback.

[AliGW]

Do NOT post edit your posts - it makes a complete nonsense of the thread. I have reinstated your first post and correctly marked the thread as solved for you.