There are many ways. The simplest is to use the new function FILTER (available in Excel 365) to extract and list the records according to a specified criteria. You can also use function TEXTJOIN to extract the records into one cell.
This example is a very long list, but I showed just several records.
| A | B | C | D | E | F | G | H | I |
---|
1 | | | | | | | | | |
2 | Rank (2016) | Municipality | Province | Population (2016) | | | | | |
3 | 1 | Toronto | Ontario | 2,731,571 | | | Criteria | Municipality | |
4 | 2 | Montreal | Quebec | 1,704,694 | | | Alberta | Calgary | Calgary, Edmonton, Red Deer, Strathcona County, Lethbridge, Wood Buffalo, St. Albert, Medicine Hat, Grande Prairie, Airdrie |
5 | 3 | Calgary | Alberta | 1,239,220 | | | | Edmonton | |
6 | 4 | Ottawa | Ontario | 934,243 | | | | Red Deer | |
7 | 5 | Edmonton | Alberta | 932,546 | | | | Strathcona County | |
8 | 6 | Mississauga | Ontario | 721,599 | | | | Lethbridge | |
9 | 7 | Winnipeg | Manitoba | 705,224 | | | | Wood Buffalo | |
10 | 8 | Vancouver | British Columbia | 631,486 | | | | St. Albert | |
11 | 9 | Brampton | Ontario | 593,638 | | | | Medicine Hat | |
12 | 10 | Hamilton | Ontario | 536,917 | | | | Grande Prairie | |
13 | 11 | Quebec City | Quebec | 531,902 | | | | Airdrie | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet15
Worksheet Formulas
Cell | Formula |
---|
H4 | =FILTER(T_Pop[Municipality],T_Pop[Province]=G4) |
---|
I4 | =TEXTJOIN(", ",,IF(G4=T_Pop[Province],T_Pop[Municipality],"")) |
---|
<thead>
</thead><tbody>
</tbody> |
<tbody>
</tbody>
<strike></strike>