Non-Array, Non-Pivot Solution Needed (Formula) for Counting Distinct Combination of Text Values in Two Separate Columns

[Aimee S.]

Hello Folks!!

I am working with some very big data that needs to be in a shared mode format to allow for both Tableau to access as a data source and for file admins to get into it and update occasionally.

Shared mode is disallowed in Excel where pivot tables are concerned, and array solutions, while available, would crash the file given there are thirty thousand rows of purchasing data to comb through.

I am looking for a clever non-array, non-pivot table-driven formula that would:

For all instances where text in F2 appears in range F2:F30001 count the number of distinct corresponding instances of unique values in column AF2:AF30001.
For example, let's say the value that appears in F2 occurs 50 times in range F2:F30001, and 49 cells in the corresponding rows for range AF2:AF30001 contain 'apple', but one contains 'orange.'
The output for this solution would be 2 (number of distinct text strings that appears in the AF range for all instances in the F range where F2's value appears).

Am I up a creek with this without arrays or pivots? I figured if anyone could think outside the box on this one it would be my friends here!!

Warm Regards,
Aimee

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Aimee S.]

Very sorry Fluff!! All done. It's been a minute since I was here 365 thankfully!

 

[Fluff]

Thanks for that.
How about

=ROWS(UNIQUE(FILTER(AF2:AF30001,F2:F30001=F2)))

 

[Aimee S.]

This works wonderfully, and I have even given you credit in the data source ;D THANK YOU!!!!!!!!!!!!!!

Purchase Order ID	WBS Prefix Tied to This PO Line Item	Fluff's Magical Unique Value Count of Project WBS Elements Tied to This PO ID
4502460980	TC-210640021	3
4502460980	TC-210640022	3
4502460980	TC-210640023	3
4502458057	TC-220028027	3
4502458057	TC-220028028	3
4502458057	TC-220028029	3
4502458057	TC-220028029	3
4502383685	TC-223006021	3
4502383685	TC-223006023	3
4502383685	TC-223006024	3
4502383685	TC-223006027	3
4502373810	TC-220926021	3
4502373810	TC-220926022	3
4502373810	TC-220928021	3
4502373810	TC-220927021	3
4502373810	TC-220928022	3
3800004723	TC-220930021	3
3800004723	TC-220931022	3
3800001087	MC-220545034	3
3800001087	MC-220545036	3
3800001087	MC-220545036	3
3800001087	MC-220545036	3
3800001087	MC-220545036	3
3800001087	MC-220545036	3
3800001087	MC-220545036	3
3800001087	MC-220545036	3
3800001053	TC-210885060	3
3800001053	TC-210885060	3
3800001053	TC-210885071	3
3800001053	TC-210885071	3
3800001053	TC-210885071	3

 

[Fluff]

You're welcome & thanks for the feedback.