Help creating a chart

[Anonymous]

Hi all, I am seeking help on how to create a chart that shows employees that have worked in more than one agency. I think that the best way to represent it is by the number of links. I have an Excel table that has the name of the agency for comumns and "person 1, 2, 3,..." for rows. If a person has worked in one of the agency listed, it has a 1. If a person left the agency and went to work for another agency, before coming back to the first agency, the person has a 2 in the first agency cell and a 1 in the other agency. 

I would like to highlight the following insights: 

1) persons that worked in more than one agency

2) count of persons that worked in the same agencies 

3) some kind of trend that the data could tell and that I am not aware of 

I have experimented with some scatter charts with poor results. Would anyone have add a similar problem elsewhere and is willing to share the procedure? 

[Anonymous]

Agencies	Frontex	EFCA	Cepol	Europol	EDA	EMA	Eurojust	EMSA	eu-LISA	EPPO	EASO	EUIPO	ECB	ECHA	EEA	ECDC	ELA
Person 1	1		1	1											1
Person 2	1		1	1
Person 3	1							1
Person 4				1					1
Person 5				1					1
Person 6	1			1			1
Person 7	1			1
Person 8	1			1
Person 9	2			1
Person 10			2								1
Person 11			1	1							1
Person 12	1			1
Person 13			1										1
Person 14			1												1
Person 15	1			1
Person 16	1										1
Person 17	1	1
Person 18	1	1
Person 19	1							1									1
Person 20			1					1
Person 21	1			1
Person 22	1			1
Person 23	1												1
Person 24	1									1
Person 25			1		1
Person 26			1		1
Person 27			1		1
Person 28	1							1
Person 29	1						1
Person 30			1			1
Person 31	1			1
Person 32	1			1
Person 33	1			1
Person 34	1			1

[Anonymous]

How do I attach a file? I see no option. I copied and pasted the table in my prvious reply but now it is gone from the thread.

[PhilipTreacy]

@Anonymous 

Load your file to somewhere like Google Drive, OneDrive or Dropbox, then link to it from here.

Phil

[Anonymous]

Here is the link to the file on DropBox https://www.dropbox.com/scl/fi/9xt2gft4yggii4wk8m8x1/Transits-Across-EU-Agencies.xlsx?rlkey=fog1l2uo...

[ryan_mayu]

@Anonymous 

in PQ， select the agencies column and unpivot other columns.

then it will be easy to get the result

[Anonymous]

Thank you but this puts the focus on the agencies. I would like to show how people have traveled across agencies. Thus, some kind of bubbles with links if at all possible, would represent this. 

[ryan_mayu]

what's the expected output? could you pls create the output in excel and paste the screenshot?

[Anonymous]

I posted a reply but it does not show for some reason

[ryan_mayu]

could you pls provide the sample data and expected output?