Sorting occurrences of Postal Code in column / Sorting by occurrence high to low

[jsmith70]

I was working on something a bit more convoluted earlier, where user @etaf really took the time to explain/give guidance, which i really appreciated - but I pivoted and will be using something a bit simpler. I am curious about the following:

I would like to utilize Column L (abbreviated) and have it sort how many times each occurs - then create a 2 column table where it has the abbreviated in column A / times it occurred in column B. So if V2N occurred 5 times I just want to see V2N | 5 - not five rows of V2N. I will subsequently want to sort this data out into 4 separate provincial tables as well so we can isolate province down from all areas. I have fumbled with some formulas to get pieces of what I want but do not know how to combine it all into one.

Book1.xlsx
	K	L	M	N
1	Postal Code	Abbreviated	Prov	City
2	S7M4J5	S7M	SK	Saskatoon
3	V4E2V1	V4E	BC	North Delta
4	R3R2N2	R3R	MB	Winnipeg
5	V2C2R3	V2C	BC	Kamloops
6	T3M0G4	T3M	AB	Calgary S/Cranston
7	V2N2T4	V2N	BC	Prince George College Heights
8	V8Z7H3	V8Z	BC	Victoria Royal Oak/Cordova Bay
9	T6J4L3	T6J	AB	Edmonton
10	R3l0X5	R3l	MB	Winnipeg
11	S9A2X3	S9A	SK	North Battleford
12	S7H4B1	S7H	SK	Saskatoon
13	T7X3Y3	T7X	AB	Spruce Grove
14	T9V3B4	T9V	AB	Lloydminster
15	T2K3G2	T2K	AB	Calgary
16	T6R2K4	T6R	AB	Edmonton
17	S7H5E1	S7H	SK	Saskatoon
18	V2B3L8	V2B	BC	Kamloops
19	V5W1A4	V5W	BC	Vancouver
20	T2Y2X5	T2Y	AB	Calgary S
21	T0M0K0	T0M	AB	Airdrie East/Sundre/Crossfields
22	V1H2B8	V1H	BC	Vernon North
23	S7K2K2	S7K	SK	Saskatoon
24	T1H1A4	T1H	AB	Lethbridge North/Coaldale/Taber
25	V9W1M5	V9W	BC	Campbell River
26	S0K2A0	S0K	SK	Martensville
27	V9N0E6	V9N	BC	Parksville
28	V9N9L2	V9N	BC	Parksville
29	V2C 0B4	V2C	BC	Kamloops
30	V8Z 4C6	V8Z	BC	Victoria Royal Oak/Cordova Bay
31	T1H 2P7	T1H	AB	Lethbridge North/Coaldale/Taber
32	T1K6R4	T1K	AB	Lethbridge South
33	V7P1H8	V7P	BC	North Vancouver
34	S0J0J0	S0J	SK	Choiceland
35	V1R3C6	V1R	BC	Trail
36	V1R 3C8	V1R	BC	Trail
37	T1X0N6	T1X	AB	Chestermere
38	V1B 9V4	V1B	BC	Vernon South/Armstrong/Lumby/Winfield
39	V2N0G9	V2N	BC	Prince George College Heights
40	V9C3X1	V9C	BC	Victoria/Colwood
41	S7N1Y1	S7N	SK	Saskatoon
42	T2S0A6	T2S	AB	Calgary S Central
43	V5A4S1	V5A	BC	Burnaby
44	T8L 2J4	T8L	AB	Fort Saskatchewan
45	V3S 0X6	V3S	BC	Surrey
46	V7Z0L1	V7Z	BC	Vancouver
47	R0C0Z0	R0C	MB	Stonewall/South Interlake
48	R3T4M8	R3T	MB	Winnipeg
49	T3K0K1	T3K	AB	Calgary NW
50	T2M6Y8	T2M	AB	Calgary N
51	T6C2J6	T6C	AB	Edmonton
52	V3R7E7	V3R	BC	Surrey
53	R2R 2J7	R2R	MB	Winnipeg
54	R3T2G2	R3T	MB	Winnipeg
55	T5T6C6	T5T	AB	Edmonton
56	V9B2J2	V9B	BC	Victoria W Highlands/Colwood/Langford/View Royal
57	V3V7T2	V3V	BC	Surrey
58	T2T0R1	T2T	AB	Calgary S Central
59	T2C3P4	T2C	AB	Calgary E
60	V9X1E7	V9X	BC	Nanaimo South/Ladysmith
61	S7H5S7	S7H	SK	Saskatoon
62	S4V3L3	S4V	SK	Regina East
63	T2Y3G3	T2Y	AB	Calgary S
64	T6T1l1	T6T	AB	Edmonton
65	S7N 4M2	S7N	SK	Saskatoon
66	S4S4F4	S4S	SK	Regina South
67	V3T2N3	V3T	BC	Surrey
68	V8T1X9	V8T	BC	Victoria/Esquimalt
69	R3X0C6	R3X	MB	Winnipeg
70	V3N1L9	V3N	BC	Burnaby
71	V3B4P3	V3B	BC	Port Coquitlam
72	T9E0T8	T9E	AB	Leduc
73	R2W3l5	R2W	MB	Winnipeg
74	V4A4X9	V4A	BC	Surrey
75	V3E0T4	V3E	BC	Coquitlam
76	V0R2P4	V0R	BC	Cowichan Valley
77	T7X3V3	T7X	AB	Spruce Grove
78	T4B1S6	T4B	AB	Airdrie West
79	R3R0W7	R3R	MB	Winnipeg
80	T5Z2Z8	T5Z	AB	Edmonton
81	R3R0W7	R3R	MB	Winnipeg
82	V5P3A9	V5P	BC	Vancouver
83	V1W3H4	V1W	BC	Kelowna
84	V2N0C2	V2N	BC	Prince George College Heights
85	V1Z4E1	V1Z	BC	West Kelowna/Peachland
86	V5R4C9	V5R	BC	Vancouver
87	V4P1B8	V4P	BC	Surrey
88	V1V3G1	V1V	BC	Kelowna
89	V3V2B8	V3V	BC	Surrey
90	T7X3S3	T7X	AB	Spruce Grove
91	T5Y3J1	T5Y	AB	Edmonton
92	T3H5Z1	T3H	AB	Calgary SW
93	T3G6A4	T3G	AB	Calgary NW
94	V3E0E9	V3E	BC	Coquitlam
95	V4L2J7	V4L	BC	Delta
96	T3H4Y5	T3H	AB	Calgary SW
97	T5Y2X5	T5Y	AB	Edmonton
98	R2C3N5	R2C	MB	Winnipeg
99	V6H1Z4	V6H	BC	Vancouver
100	T5R2W1	T5R	AB	Edmonton
101	T6R0B1	T6R	AB	Edmonton
102	T6K0L2	T6K	AB	Edmonton
103	T3J3A3	T3J	AB	Calgary
104	R3T3X1	R3T	MB	Winnipeg
105	R2M2V4	R2M	MB	Winnipeg/St Vital
106	T3K5J2	T3K	AB	Calgary NW
107	V7N1L8	V7N	BC	North Vancouver
108	T5X5P9	T5X	AB	Edmonton
109	V9L0A6	V9L	BC	Duncan
110	R3N0R6	R3N	MB	Winnipeg
111	T3Z1E1	T3Z	AB	Bragg Creek/Redwood Meadows/Springbank
112	T3E5A4	T3E	AB	Calgary SW
113	R3P1A4	R3P	MB	Winnipeg
114	V7T2L4	V7T	BC	West Vancouver
115	R3Y2B9	R3Y	MB	Winnipeg
116	S6V1M5	S6V	SK	Prince Albert
117	T3G4R5	T3G	AB	Calgary NW
118	V2T5B3	V2T	BC	Abbotsford
119	R0A0V2	R0A	MB	Steinbach/Southern Communities
120	T2W 3V5	T2W	AB	Calgary SW
121	S0H 3G0	S0H	SK	Moose Jaw
122	V7L1P5	V7L	BC	North Vancouver
123	T2B2E4	T2B	AB	Calgary E
124	T2V2A2	T2V	AB	Calgary SE
125	T3M0L5	T3M	AB	Calgary S/Cranston
126	R2P1Y7	R2P	MB	Winnipeg
127	V3T 0E7	V3T	BC	Surrey
128	T3M1V1	T3M	AB	Calgary S/Cranston
129	T5P3M8	T5P	AB	Edmonton
130	V3H 0A6	V3H	BC	Port Moody
131	T2J1M1	T2J	AB	Calgary SE
132	T7S0C8	T7S	AB	Whitecourt
133	T3H 4X6	T3H	AB	Calgary SW
134	V8B 2A1	V8B	BC	Squamish/Lions Bay
135	V1W5K4	V1W	BC	Kelowna
136	V1E1K5	V1E	BC	Salmon Arm
137	T2E 1L8	T2E	AB	Calgary
138	V7S0A4	V7S	BC	West Vancouver
139	V5R 1T5	V5R	BC	Vancouver
140	V0R1R0	V0R	BC	Cowichan Valley
141	R7A0W4	R7A	MB	Brandon 2
142	R0M1P0	R0M	MB	Dauphin
143	R3G3C5	R3G	MB	Winnipeg
144	V9T4S4	V9T	BC	Nanaimo North/Lantzville
145	v2n5k6	v2n	BC	Prince George College Heights
Leads

Cell Formulas
Range	Formula
L2:L145	L2	=LEFT([@[Postal Code]],3)
M2:M145	M2	=IF(LEFT(L2,1)="R","MB",IF(LEFT(L2,1)="S","SK",IF(LEFT(L2,1)="T","AB",IF(LEFT(L2,1)="V","BC",""))))
N2:N145	N2	=VLOOKUP(L2, FranchiseeBaseline!$A$2:$F$500, 6)

 

[Fluff]

How about

=LET(u,UNIQUE(L2:L145),SORT(HSTACK(u,COUNTIFS(L:L,u)),2,-1))

 

[jsmith70]

How about

=LET(u,UNIQUE(L2:L145),SORT(HSTACK(u,COUNTIFS(L:L,u)),2,-1))

well that was quick - and amazing thank you - yes this worked perfectly. i really appreciate it and will look into what you gave me so i can understand (rather than just taking).

now before i start playing with this - if i wanted to add a condition to split out by province can this formula accommodate this? i would presume the easiest would be simply adding the first letter (V, T etc..) as the condition as they are unique to each province rather than identifying a separate 'provincial' column altogether.

 

[alansidman]

Two alternative options
New Excel formula GroupBy

=GROUPBY(Table1[[#All],[Abbreviated]],Table1[[#All],[Abbreviated]],COUNTA)

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Abbreviated"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Fluff]

if i wanted to add a condition to split out by province

How about

=LET(u,UNIQUE(L2:L145),s,SORT(HSTACK(u,COUNTIFS(L:L,u)),2,-1),FILTER(s,LEFT(INDEX(s,,1))="V"))

 

[alansidman]

Here is a parameter query using Power Query that achieves your ask.

PQ Parameter Query 42.xlsx | Powered by Box

app.box.com

 

[jsmith70]

Thank you everyone for suggestions/help on this, it is really appreciated. sorry for the gap in time replying to this - some of that life stuff got in the way recently ..

@Fluff - can more variables be added to that formula. I am totally out of my depth here but i'm curious about a couple things. with the above example spreadsheet - if there was a column O tracking dollars of booked jobs (so Row 2 would be S7M 4J5 / S7M / SK / Saskatoon / $5,000) can the following examples be done with the current formula?

1. tallying up instances each postal code shows up as it is doing - then adding up the subsequent column O values to match?
or
2. tallying up dollar amounts high to low with the abbreviated postal beside it.

does that make sense how i'm asking?

or:
V2N had 3 jobs @ 5,000 each for $15,000
V8R had 2 jobs @ 5,000 each for $10,000
V9B had 1 job @ 5,000 for $5,000

does it have the ability to sort by highest to lowest dollar figure and show the matching abbreviated postal code beside it?

so visually - is that doable within this formula, or is this a different one i'm looking for

Jobs Total $ Postal Code Sold Leads
$15,000 V2N 3
$10,000 V8R 2
$5,000 V9B 1

 

[Fluff]

How about

=LET(u,UNIQUE(L2:L145),SORT(HSTACK(u,COUNTIFS(L:L,u),SUMIFS(O:O,L:L,u)),3,-1))