Return number count of other variables based on one match in same column

[Tdaddy4]

I have a the first column that has a list of IDs duplicate numbers
i have a second column that has a list of all facilities that are linked to that first column

i want to create a table that list all the associated facilities by the first column where at least one name is in the second column

so I want a count of facility ID and a list of the city name for a facility ID that are filtered on a certain name.
So how many Facility IDs have the word Stockton listed, and a list of the other names in that same facility ID

I need to show the most cities that match up to a filtered city based on Facility IDs

Facility ID	City
000144	ANCHORAGE
000144	ANCHORAGE
000144	ANCHORAGE
000144	ANCHORAGE
000144	ANCHORAGE
000144	EMERYVILLE
000166	W SACRAMENTO
000166	YAKIMA
000166	YAKIMA
000168	WHITE PINE
000168	SALT LAKE
000180	STOCKTON
000180	STOCKTON
000180	TACOMA
000180	TACOMA
000459	SACRAMENTO
000459	PORTLAND
000480	LODI
000480	SACRAMENTO
000480	STOCKTON
000480	SALEM
000481	STOCKTON
000481	STOCKTON
000482	STOCKTON
000482	STOCKTON
000483	SAN FRANCISCO
000483	SAN FRANCISCO
000484	VEGAS
000484	VEGAS
000485	N VEGAS
000485	N VEGAS
000486	VEGAS
000486	VEGAS
000487	VEGAS
000487	VEGAS
000488	VEGAS
000488	VEGAS
000489	VEGAS
000489	N VEGAS
000490	SACRAMENTO
000490	SACRAMENTO
000491	WEST SACRAMENTO
000491	SACRAMENTO
000492	WEST SACRAMENTO
000492	SAN JOSE
000493	ELK GROVE
000493	ELK GROVE
000494	FAIRFIELD
000494	FAIRFIELD
000788	STOCKTON
000788	WAY
000857	VALLEJO
000857	PORTLAND
000857	PORTLAND
000857	PORTLAND
000857	PORTLAND
000866	FAIRFIELD
000866	MULTNOMAH
000866	EVERETT
000866	SEATTLE
000866	SEATTLE
000882	FRESNO
000882	NAMPA
000882	NAMPA
000882	NAMPA
001015	W SACRAMENTO
001015	SEATTLE

 

[etaf]

what version of excel do you have

UNIQUE()
will give you a list of Unique ID and name

so then you can do the counts etc

OR a pivot table - with a page filter

But I'm not clear on what you want to show as the output - or just filter this list


can you show what the output filtered you are after ?

Book18
	A	B	C	D	E	F	G	H	I	J
1	Facility ID	City			Facility ID	City		City	STOCKTON
2	144	ANCHORAGE			144	ANCHORAGE
3	144	ANCHORAGE			144	EMERYVILLE		Row Labels	Count of Facility ID
4	144	ANCHORAGE			166	W SACRAMENTO		180	1
5	144	ANCHORAGE			166	YAKIMA		480	1
6	144	ANCHORAGE			168	WHITE PINE		481	1
7	144	EMERYVILLE			168	SALT LAKE		482	1
8	166	W SACRAMENTO			180	STOCKTON		788	1
9	166	YAKIMA			180	TACOMA		Grand Total	5
10	166	YAKIMA			459	SACRAMENTO
11	168	WHITE PINE			459	PORTLAND
12	168	SALT LAKE			480	LODI
13	180	STOCKTON			480	SACRAMENTO
14	180	STOCKTON			480	STOCKTON
15	180	TACOMA			480	SALEM
16	180	TACOMA			481	STOCKTON
17	459	SACRAMENTO			482	STOCKTON
18	459	PORTLAND			483	SAN FRANCISCO
19	480	LODI			484	VEGAS
20	480	SACRAMENTO			485	N VEGAS
21	480	STOCKTON			486	VEGAS
22	480	SALEM			487	VEGAS
23	481	STOCKTON			488	VEGAS
24	481	STOCKTON			489	VEGAS
25	482	STOCKTON			489	N VEGAS
26	482	STOCKTON			490	SACRAMENTO
27	483	SAN FRANCISCO			491	WEST SACRAMENTO
28	483	SAN FRANCISCO			491	SACRAMENTO
29	484	VEGAS			492	WEST SACRAMENTO
30	484	VEGAS			492	SAN JOSE
31	485	N VEGAS			493	ELK GROVE
32	485	N VEGAS			494	FAIRFIELD
33	486	VEGAS			788	STOCKTON
34	486	VEGAS			788	WAY
35	487	VEGAS			857	VALLEJO
36	487	VEGAS			857	PORTLAND
37	488	VEGAS			866	FAIRFIELD
38	488	VEGAS			866	MULTNOMAH
39	489	VEGAS			866	EVERETT
40	489	N VEGAS			866	SEATTLE
41	490	SACRAMENTO			882	FRESNO
42	490	SACRAMENTO			882	NAMPA
43	491	WEST SACRAMENTO			1015	W SACRAMENTO
44	491	SACRAMENTO			1015	SEATTLE
45	492	WEST SACRAMENTO
46	492	SAN JOSE
47	493	ELK GROVE
48	493	ELK GROVE
49	494	FAIRFIELD
50	494	FAIRFIELD
51	788	STOCKTON
52	788	WAY
53	857	VALLEJO
54	857	PORTLAND
55	857	PORTLAND
56	857	PORTLAND
57	857	PORTLAND
58	866	FAIRFIELD
59	866	MULTNOMAH
60	866	EVERETT
61	866	SEATTLE
62	866	SEATTLE
63	882	FRESNO
64	882	NAMPA
65	882	NAMPA
66	882	NAMPA
67	1015	W SACRAMENTO
68	1015	SEATTLE
Sheet1

Cell Formulas
Range	Formula
E2:F44	E2	=UNIQUE(A2:B68)
Dynamic array formulas.

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

XL2BB is an add-in used on this forum , see my signature or the menu in post to link to the full details
OR put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Here is the link to XL2BB instructions

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Tdaddy4]

I am using 365

apologize for not stating the question correctly
so for every instance i filter for a city i would like to return the other citys not just the count

so if filter is Stockton
how many instances where the facility code has a stockton in it, return the other cities

trying to find the top 5 other common cities to stockton across the other facility codes.

 

[Fluff]

I am using 365

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’)

 

[etaf]

so if filter is Stockton
how many instances where the facility code has a stockton in it, return the other cities

Is that what i gave in the pivot table in the XL2BB output ?

so for every instance i filter for a city i would like to return the other citys not just the count

not sure i understand that - can you please provide a few examples of results needed

 

[Tdaddy4]

i have a giant list of facility IDs, that are unique to a group of cities.
can be a different group of cities to these unique facility ids.
i want to do something, where i filter a city, such as stockton.
and it list a the other cities that are tied to it, in each instance of a facility ID

so Stockton maybe in 1000 different facility IDs
I want a count of the other cities in each of the different facility ids, but also need to know their names, so i can say Sacramento has 900 of the same facility ids as stockton and then down the chain for the top 10 shared

and easily change the filtered city to see which they have in common.

 

[Tdaddy4]

i was trying a pivot table, but he just gives the counts of the filtered can get a view of the info in POWERBI, but can't export the data, need to export each filtered city to share

 

[Tdaddy4]

maybe is is better to say i want a list with a count number on how many times a filtered city matches to other cities based on the facility ID

Stockton (filtered city)

Sacramento 10 (that Stockton and Sacramento share 10 Facility IDs)
San Jose 8 (that Stockton and San Jose share 8 Facility IDs)

then a second one

Sacramento (filtered city)

San Jose 11 (Sacramento and San Jose share 11 Facility IDs)
Stockton 10 (Sacramento and Stockton share 10 Facilty IDs)
Oakland 8


that sort of things (facility IDs are unique, but will have matches between cities, so several rows with same facility id, but different info in column's

 

[etaf]

This is a pretty clunky way of doing what I think you need to do
sure there will be a better solution from more experienced members
This is how i have done it

Column E and F
=UNIQUE(A2:B68)
which brings back the unique entries for those cities and IDs
basically removing ALL duplicates of City & ID with the same info

G2
has a data validation dropdown listing all the Unique Cities in F

H2
=FILTER(E2:E44,F2:F44=G2)
which then list all the Facility Ids - BUT only unique - could not work out how to get all the IDs and City Names associated with all those IDS

Hence
I2
=TRANSPOSE(H2:H21)
Now this will transpose all those Facility IDs horizontally
if you change the range H2 to H21 so that it has the maximum possible entries you may have - then it will not transpose across loads of columns unnecessarily
as we go from H2 to H21 - that is 19 possible entries - when transpose that will use 19 columns - so here is where you can reduce to the maximum possible IDs for 1 city

Now we have all the possible Facility IDs , which have the City name in , going along the ROW from I2 to AB2

In I3 - I have used
=IF(I2=0,"",FILTER($F$2:$F$44,$E$2:$E$44=I2))
So if the ID is zero, ie NO ID - then it blanks those cells - Hence the blank cells in the range I3:AB3

I then filter the City Column F , based on the ID in I2,J2, K2 etc etc
Which means under that ID it will list ALL the cities it finds for that Facility ID

anyway , as i say a bit clunky - bit here is the XL2BB format , and i have also but a link on dropbox , BUT i dont leave the dropbox file available for more than a few days

Stockton-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Facility ID	City			Facility ID	City	Filter	Facility IDs
2	144	ANCHORAGE			144	ANCHORAGE	STOCKTON	180	180	480	481	482	788	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	144	ANCHORAGE			144	EMERYVILLE		480	STOCKTON	LODI	STOCKTON	STOCKTON	STOCKTON
4	144	ANCHORAGE			166	W SACRAMENTO		481	TACOMA	SACRAMENTO			WAY
5	144	ANCHORAGE			166	YAKIMA		482		STOCKTON
6	144	ANCHORAGE			168	WHITE PINE		788		SALEM
7	144	EMERYVILLE			168	SALT LAKE
8	166	W SACRAMENTO			180	STOCKTON
9	166	YAKIMA			180	TACOMA
10	166	YAKIMA			459	SACRAMENTO
11	168	WHITE PINE			459	PORTLAND
12	168	SALT LAKE			480	LODI
13	180	STOCKTON			480	SACRAMENTO
14	180	STOCKTON			480	STOCKTON
15	180	TACOMA			480	SALEM
16	180	TACOMA			481	STOCKTON
17	459	SACRAMENTO			482	STOCKTON
18	459	PORTLAND			483	SAN FRANCISCO
19	480	LODI			484	VEGAS
20	480	SACRAMENTO			485	N VEGAS
21	480	STOCKTON			486	VEGAS
22	480	SALEM			487	VEGAS
23	481	STOCKTON			488	VEGAS
24	481	STOCKTON			489	VEGAS
25	482	STOCKTON			489	N VEGAS
26	482	STOCKTON			490	SACRAMENTO
27	483	SAN FRANCISCO			491	WEST SACRAMENTO
28	483	SAN FRANCISCO			491	SACRAMENTO
29	484	VEGAS			492	WEST SACRAMENTO
30	484	VEGAS			492	SAN JOSE
31	485	N VEGAS			493	ELK GROVE
32	485	N VEGAS			494	FAIRFIELD
33	486	VEGAS			788	STOCKTON
34	486	VEGAS			788	WAY
35	487	VEGAS			857	VALLEJO
36	487	VEGAS			857	PORTLAND
37	488	VEGAS			866	FAIRFIELD
38	488	VEGAS			866	MULTNOMAH
39	489	VEGAS			866	EVERETT
40	489	N VEGAS			866	SEATTLE
41	490	SACRAMENTO			882	FRESNO
42	490	SACRAMENTO			882	NAMPA
43	491	WEST SACRAMENTO			1015	W SACRAMENTO
44	491	SACRAMENTO			1015	SEATTLE
45	492	WEST SACRAMENTO
46	492	SAN JOSE
47	493	ELK GROVE
48	493	ELK GROVE
49	494	FAIRFIELD
50	494	FAIRFIELD
51	788	STOCKTON
52	788	WAY
53	857	VALLEJO
54	857	PORTLAND
55	857	PORTLAND
56	857	PORTLAND
57	857	PORTLAND
58	866	FAIRFIELD
59	866	MULTNOMAH
60	866	EVERETT
61	866	SEATTLE
62	866	SEATTLE
63	882	FRESNO
64	882	NAMPA
65	882	NAMPA
66	882	NAMPA
67	1015	W SACRAMENTO
68	1015	SEATTLE
stockton

Cell Formulas
Range	Formula
E2:F44	E2	=UNIQUE(A2:B68)
H2:H6	H2	=FILTER(E2:E44,F2:F44=G2)
I2:AB2	I2	=TRANSPOSE(H2:H21)
I3:I4,M3:M4,K3:L3,N3:AB3,J3:J6	I3	=IF(I2=0,"",FILTER($F$2:$F$44,$E$2:$E$44=I2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$F$2:$F$44

Dropbox - File Deleted - Simplify your life

www.dropbox.com