Find Corresponding Column from a Total

[srh5428]

Hello,

I created a formula to sort some data from largest to smallest. From my screenshot below, you can see I created an array that counts the different fields ("Designer") that are in "Altoona". In my other screenshot is an example of the Designer Column name.

I am sort of trying to work backwards and say to Excel: "Ok, I have my list from largest to smallest, now I want the Designer associated with that count". So my question is, what sort of formula would I write, to get Excel to give me the Designer name for associated total? I am trying to download the XL2BB add in so I can post an example of my data.



Thanks everyone

 

[awoohaw]

If there is any chance at all that you would get the same sum twice then that makes it difficult. Please give an example of something like that when you post the xl2bb mini sheet.

If you can't use the xl2bb minisheet, then just copy and paste a reasonable cell range into the post thread. It will post as a table. We won't see your formulas or column and row headers, but it would give some help. (but better than images!).

 

[srh5428]

Designer Work Requests.xlsx
	H																			AA
4	Altoona																			Litzinger,David A
5	Altoona																			Litzinger,David A
6	Altoona																			Litzinger,David A
7	Altoona																			Litzinger,David A
8	Altoona																			Litzinger,David A
9	Altoona																			Litzinger,David A
10	Altoona																			Litzinger,David A
11	Altoona																			Litzinger,David A
12	Altoona																			Litzinger,David A
13	Altoona																			Litzinger,David A
14	Altoona																			Hunter,Seth R
15	Altoona																			Litzinger,David A
16	Altoona																			Litzinger,David A
17	Altoona																			Litzinger,David A
18	Altoona																			McElhinney,Joel
19	Altoona																			Litzinger,David A
20	Altoona																			Litzinger,David A
21	Altoona																			Litzinger,David A
22	Altoona																			Litzinger,David A
23	Altoona																			Litzinger,David A
24	Altoona																			Litzinger,David A
25	Altoona																			Litzinger,David A
26	Altoona																			Litzinger,David A
27	Altoona																			Litzinger,David A
28	Altoona																			Litzinger,David A
29	Altoona																			Litzinger,David A
30	Altoona																			Litzinger,David A
31	Altoona																			Litzinger,David A
32	Altoona																			Litzinger,David A
33	Altoona																			Litzinger,David A
34	Altoona																			Litzinger,David A
35	Altoona																			Litzinger,David A
36	Altoona																			Litzinger,David A
37	Altoona																			Litzinger,David A
38	Altoona																			Litzinger,David A
39	Altoona																			Litzinger,David A
40	Altoona																			Litzinger,David A
41	Altoona																			Litzinger,David A
42	Altoona																			Litzinger,David A
43	Altoona																			Litzinger,David A
44	Altoona																			Litzinger,David A
45	Altoona																			Litzinger,David A
46	Altoona																			Litzinger,David A
47	Altoona																			Litzinger,David A
48	Altoona																			Litzinger,David A
49	Altoona																			Litzinger,David A
50	Altoona																			McElhinney,Joel
51	Altoona																			Litzinger,David A
52	Altoona																			Litzinger,David A
53	Altoona																			Litzinger,David A
54	Altoona																			Litzinger,David A
55	Altoona																			Litzinger,David A
56	Altoona																			Litzinger,David A
57	Altoona																			Litzinger,David A
58	Altoona																			Litzinger,David A
59	Altoona																			Litzinger,David A
60	Altoona																			Litzinger,David A
61	Altoona																			Litzinger,David A
62	Altoona																			Litzinger,David A
63	Altoona																			Litzinger,David A
64	Altoona																			Litzinger,David A
65	Altoona																			Litzinger,David A
66	Altoona																			Litzinger,David A
67	Altoona																			Litzinger,David A
68	Altoona																			Litzinger,David A
69	Altoona																			McElhinney,Joel
70	Altoona																			Litzinger,David A
71	Altoona																			Litzinger,David A
72	Altoona																			Litzinger,David A
73	Altoona																			Litzinger,David A
74	Altoona																			Litzinger,David A
75	Altoona																			Litzinger,David A
76	Altoona																			Litzinger,David A
77	Altoona																			Litzinger,David A
78	Altoona																			McElhinney,Joel
79	Altoona																			Litzinger,David A
80	Altoona																			Litzinger,David A
81	Altoona																			Litzinger,David A
82	Altoona																			Litzinger,David A
83	Altoona																			Litzinger,David A
84	Altoona																			Litzinger,David A
85	Altoona																			Litzinger,David A
86	Altoona																			Litzinger,David A
87	Altoona																			Litzinger,David A
88	Altoona																			Litzinger,David A
89	Altoona																			Litzinger,David A
90	Altoona																			Litzinger,David A
91	Altoona																			Litzinger,David A
92	Altoona																			Neely,Joseph C
93	Altoona																			Litzinger,David A
94	Altoona																			McElhinney,Joel
95	Altoona																			Litzinger,David A
96	Altoona																			Neely,Joseph C
97	Altoona																			Litzinger,David A
98	Altoona																			Litzinger,David A
99	Altoona																			Litzinger,David A
100	Altoona																			Litzinger,David A
101	Altoona																			Litzinger,David A
102	Altoona																			Litzinger,David A
103	Altoona																			Litzinger,David A
104	Altoona																			Litzinger,David A
105	Altoona																			McElhinney,Joel
106	Altoona																			Neely,Joseph C
107	Altoona																			Neely,Joseph C
108	Altoona																			McElhinney,Joel
109	Altoona																			McElhinney,Joel
110	Altoona																			McElhinney,Joel
111	Altoona																			McElhinney,Joel
112	Altoona																			Forshey,Gary L
113	Altoona																			McElhinney,Joel
114	Altoona																			McElhinney,Joel
115	Altoona																			McElhinney,Joel
116	Altoona																			McElhinney,Joel
117	Altoona																			McElhinney,Joel
118	Altoona																			McElhinney,Joel
119	Altoona																			Neely,Joseph C
120	Altoona																			Forshey,Gary L
121	Altoona																			McElhinney,Joel
122	Altoona																			McElhinney,Joel
123	Altoona																			McElhinney,Joel
124	Altoona																			McElhinney,Joel
125	Altoona																			McElhinney,Joel
126	Altoona																			Litzinger,David A
127	Altoona																			Litzinger,David A
128	Altoona																			McElhinney,Joel
POFUs

 

[Fluff]

How about

=LET(u,UNIQUE(AA4:AA128),SORT(HSTACK(u,COUNTIFS(AA:AA,u,H:H,"altoona")),2,-1))

 

[srh5428]

How about

=LET(u,UNIQUE(AA4:AA128),SORT(HSTACK(u,COUNTIFS(AA:AA,u,H:H,"altoona")),2,-1))

This seemed to almost completely work - the array spilled the names of other Designers that were not in the screenshot i provided. They show a 0 because the are not in "Altoona", which makes sense.

I suppose I could get rid of the 0 values with an IF statement, unless you have a better idea?

Thank you

 

[srh5428]

This seemed to almost completely work - the array spilled the names of other Designers that were not in the screenshot i provided. They show a 0 because the are not in "Altoona", which makes sense.

View attachment 111088

I suppose I could get rid of the 0 values with an IF statement, unless you have a better idea?

Thank you

Update, the IF statement didn't work..

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER(AA4:AA128,H4:H128="altoona")),SORT(HSTACK(u,COUNTIFS(AA:AA,u,H:H,"altoona")),2,-1))

 

[srh5428]

How about

=LET(u,UNIQUE(FILTER(AA4:AA128,H4:H128="altoona")),SORT(HSTACK(u,COUNTIFS(AA:AA,u,H:H,"altoona")),2,-1))

That worked - thank you very much!

I hope someday I am at the Wizarding level you are.

Thanks again!! Huge help!!

 

[Fluff]

Glad we could help & thanks for the feedback.