Automatically perform formula repeatedly on different cells within another formula

[WorldTraveler]

Hello,

In the sheet titled Data have a list of books organized by year. This also includes the author. Often there is more than one author per book because they often collaborated. Relevant to my question is the fact that years are in column A and authors in column D.


Now, each author was part of 1 or more organizations. Please ignore column B above because it's not entirely accurate. Instead to link authors to organizations I have a list of authors under each organization they belonged to in Sheet 4. For example, authors who were in BR are listed in Sheet 4, column E, rows 5-89 like so:

I realize this is not the greatest setup, but what I'm trying to do is link the organization with the years in which their members published a book, repeating years if there was more than one publication so that later I can create a bar graph for each organization with # of publications per year.

So for example, I'd want to end up with a list like this for the BR:

1995

1995

1996

1997

1997

1997

1997

1998

1998

I've developed a clunky formula that gives me the years of publications by any authors listed in cells E5 to E9 in Sheet 4:

=FILTER(Data!A:A,ISNUMBER(SEARCH(SHEET4!E5,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E6,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E7,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E8,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E9,Data!D:D))

However, typing out ISNUMBER(SEARCH(SHEET4![cell],Data!D:D)) 84 times for 84 cells is not fun. Is there a way to automate this so that Excel will automatically check all authors listed in cells E5 to E89 individually before conducting the FILTER formula?

=FILTER(Data!A:A,ISNUMBER(SEARCH(SHEET4!E5:E89,Data!D:D)) does not work because this is asking Excel to see whether all of the authors' names are listed for the same book, which of course is never the case.

Even better would be a formula that when it sees this:

1981 Book A Author A, Author B, Author C

and

Organization 1: Author A

Organization 2: Author B, Author C

does not count the text twice for organization 2, but that goes well beyond my capabilities.

This is on Excel for Windows 11 in Microsoft 365 office for enterprise. I think that's version 2502?

Many thanks!

 

[saboh12617]

Ciao,

You are looking for the new "array formulas" avaliable in Office 365. Especially MAP and maybe REDUCE. However i have a hard time figuring things out with just screenshots, if you could provide some sample data with the XLBB addon it would be great.

 

[WorldTraveler]

Ok, here's the list of organizations/authors:

memoriali_17.xlsx
	F
11
Sheet4

 

[WorldTraveler]

Sorry, the last one didn't work as intended. Here's the organizations/authors sheet:

memoriali_17.xlsx
	C	D	E
4	A/traverso	Autonomia	BR
5	C Cappi	A Pazienza	A Basone
6	M Torrealta	E Palandri	A Bonavita
7		F Antoni	A Braghetti
8		G Memeo	A Casimirri
9		G Memeo	A De Luca
10		P Mancini	A Faranda
11		P Tondelli	A Fosso
12		S Tamburini	A Franceschini
13			A Lojacono
14			A Ludmann
15			A Savasta
16			A Vai
17			B Balzerani
18			B Fabrizi
19			B Seghetti
20			C Di Lenardo
21			C Garavalia
22			E Arcangeli
23			E Calvitti
24			E Fenzi
25			E Libera
26			E Triaca
27			F Bonisoli
28			F Donati
29			F Galloni
30			F Grilli
31			F Lo Bianco
32			F Miglietta
33			F Piccioni
34			F Ravalli
35			F Ravalli
36			G Alimonti
37			G Anonimo
38			G Ciucco
39			G Codrini
40			G Colotti
41			G Frau
42			G Naria
43			G Senzani
44			L Azzolini
45			L Betassa
46			L Carpi
47			L Nicolotti
48			M Brioschi
49			M Cagol
50			M Cappello
51			M Cianfanelli
52			M Di Marzio
53			M Falessi
54			M Iannelli
55			M Maraschi
56			M Moretti
57			M Petrella
58			M Pisetta
59			M Romeo
60			N D'Amore
61			N Manenti
62			N Mantovani
63			N Ponti
64			P Bertolazzi
65			P Ferrari
66			P Gallinari
67			P Morlacchi
68			P Panciarelli
69			P Peci
70			R Algranati
71			R Cappelli
72			R Curcio
73			R Dura
74			R Fiore
75			R Lupo
76			R Micaletto
77			R Ognibene
78			R Volinia
79			S Berardi
80			S Colonna
81			S Padula
82			S Tornaghi
83			T Cherubini
84			T Paroli
85			V Acella
86			V Guagliardo
87			V Morucci
88			V Vaccaro
89			W Alasia
Sheet4

Cell Formulas
Range	Formula
C5:C6	C5	=SORT(FILTER('Lista di Nomi controllati'!A2:A392,('Lista di Nomi controllati'!G2:G392=C4)+('Lista di Nomi controllati'!H2:H392=C4)+('Lista di Nomi controllati'!I2:I392=C4)+('Lista di Nomi controllati'!J2:J392=C4)," "))
D5:D12	D5	=SORT(FILTER('Lista di Nomi controllati'!A2:A392,('Lista di Nomi controllati'!G2:G392=D4)+('Lista di Nomi controllati'!H2:H392=D4)+('Lista di Nomi controllati'!I2:I392=D4)+('Lista di Nomi controllati'!J2:J392=D4)," "))
E5:E89	E5	=SORT(FILTER('Lista di Nomi controllati'!A2:A392,('Lista di Nomi controllati'!G2:G392=E4)+('Lista di Nomi controllati'!H2:H392=E4)+('Lista di Nomi controllati'!I2:I392=E4)+('Lista di Nomi controllati'!J2:J392=E4)," "))
Dynamic array formulas.

Lambda Functions
Name	Formula
S	=LAMBDA(a,b,ISNUMBER(SEARCH(a,b)))

 

[WorldTraveler]

And here's the list of books by year/author. I've cut off the names of the books etc since they're not relevant to the question, but each row is one text.

memoriali_17.xlsx
	A	B	C	D
1	year	group	group2	author
2	1968	assorted		P Liguori, O Scalzone, Pierpaolo Balbo, Massimo Barone, Massimiliano Fuksas, Sergio Petruccioli, Pure Franco, and Andrea Silipo, [controllare chi qua e' attivista]
3	1969
4	1970
5	1971	Potop	Autonomia	N Balestrini
6	1972
7	1973	LC		T D'Amico
8	1973	LC		V Franzinetti
9	1974	Potop		various
10	1975	Autonomia		D Mariotti
11	1975	Radio Città Futura		A Mordenti
12	1975	LC		E Deaglio
13	1975	Movimento studentesco		E Rutigliano
14	1976	LC		M Radice, L Ravera
15	1976	LC		L Manconi
16	1976	LC		N Vento
17	1976	Potop	Autonomia	N Balestrini
18	1977	LC		L Manconi
19	1977	LC		L Manconi
20	1977	LC		M Sinibaldi
21	1977	Autonomia		A Pazienza
22	1977	PAC		A Cavallina
23	1978	LC		L Manconi, G Lerner e M Sinibaldi
24	1978	LC		G Viale
25	1978	LC		M Colafato
26	1978	LC		M Revelli
27	1978	LC		P Hutter
28	1978	LC		M L Radice
29	1978	LC		G Mughini
30	1979	Autonomia		E Palandri
31	1979	Movimento studentesco, LC		M Boato
32	1979	LC		L Boato
33	1979	LC		L Manconi
34	1979	?		V Borelli
35	1980	LC		L Manconi
36	1980	BR		G Naria
37	1980	assorted		R Curcio e M Rostagno
38	1981	LC		L Manconi
39	1981	BR		Giorgio Anonimo
40	1981	BR		G Codrini (inventato?)
41	1982
42	1983	Potop		T Negri
43	1983	BR		P Peci
44	1984	LC		G de Luna
45	1985	LC		A Ceste
46	1985	Potop		T Negri
47	1986	Autonomia		E Palandri
48	1986	NAP		G Panizzari
49	1986	Autonomia		E Palandri
50	1987	Potop	Autonomia	N Balestrini
51	1987	BR		E Fenzi
52	1987	LC		G Mughini
53	1987	Potop		F Berardi
54	1988	LC		A Ceste
55	1988	Movimento studentesco		M Boato, E Rutigliano
56	1988	Potop	Autonomia	N Balestrini e P Moroni
57	1988	BR		G Naria
58	1988	Potop		O Scalzone
59	1989	Autonomia		E Vesce
60	1989	LC		M Revelli
61	1989	LC		M Revelli
62	1989	BR		F Piccioni, F Lo Bianco
63	1989	Radio Città Futura		S Provvisionato
64	1989	Potop		N Balestrini
65	1990	LC		M Revelli, P Ortoleva
66	1990	NAP		G Panizzari
67	1990	LC		L Manconi
68	1990	LC		A Ceste
69	1990	LC		M Calopresti
70	1990	LC		A Sofri
71	1991	BR		Maria Cappello, Tiziana Cherubini, Antonio De Luca, Franco Galloni, Franco Grilli., Rossella Lupo, Fulvia Matarazzo, Stefano Minguzzi, Fabio Ravalli e dei militanti rivoluzionari Daniele Bencini, Vincenza Vaccaro, Marco Venturini
72	1991	BR		G Naria
73	1991	BR		V Guagliardo
74	1991	LC		A Sofri
75	1992	LC		M Revelli
76	1992	LC		T D'Amico
77	1992	LC		L Marino
78	1993	BR		M Cappello
79	1993	Radio Città Futura		S Provvisionato
80	1993	LC		A Ceste
81	1993	BR		R Curcio
82	1993	BR		R Curcio
83	1993	PAC		C Battisti
84	1994	BR		A Faranda
85	1994	BR		A Franceschini
86	1994	LC		A Ceste
87	1994	BR		M Moretti
88	1994	BR		F F De Albuquerquee M Iannelli (non chiaro quanto intervenuto, ne se davvero BR)
89	1994	BR		V Morucci
90	1994	BR		V Guagliardo
91	1994	Potop	Autonomia	N Balestrini
92	1995	LC		A Sofri
93	1995	LC		P Ortoleva
94	1995	LC		M Calopresti
95	1995	LC		L Manconi
96	1995	BR		A Braghetti
97	1995	BR		P Gallinari L Santilli (moglie di PG)
98	1996	BR		N Mantovani
99	1996	UCC		W Labate
100	1996	LC		A Sofri
101	1996	Radio Città Futura		E Bassignano
102	1996	LC		A Sofri L Sofri, curatori
103	1997	BR		A Franceschini
104	1997	BR		G Naria
105	1997	LC		A Sofri
106	1997	BR		G Colotti
107	1997	assorted		F Piperno Ida Dominijanni, Felice Liperi, Luigi Manconi, Marco Bascetta, Marco Grispigni, Alfonso Maurizio Iacono, Renato Nicolini, Rina Gagliardi, Rossana Rossanda, Paolo Virno
108	1997	BR		V Guagliardo
109	1997	Autonomia		F Scozzari
110	1997	FCC		T Z Zanetti
111	1998	BR		A Braghetti
112	1998	LC		A Ceste
113	1998	LC		A Sofri
114	1998	BR		B Balzerani
115	1998	LC		M Revelli, P Ortoleva
116	1998	LC		G Mughini
117	1998	LC		E Deaglio
118	1998	PAC		C Battisti
119	1998	Potop		O Scalzone
120	1998	assorted		mix
121	1998	Potop		F Berardi
122	1999	BR		V Morucci
123	1999	BR		R Curcio F Silvestri
124	1999	BR		A Vai
125	1999	Potop		O Scalzone
126	2000	LC		G Crainz
127	2000	BR		G Colotti
128	2000	FCC		T Z Zanetti
129	2001	LC		M Fossati
130	2001	LC		M Fossati
131	2002	LC		G Mughini
132	2002	BR		V Guagliardo
133	2002	Potop		F Berardi
134	2002	Potop		F Berardi
135	2002	Potop		F Berardi
136	2003	PAC		C Battisti
137	2003	LC		M Fossati
138	2003	PAC		C Battisti
139	2003	Radio Città Futura		S Provvisionato
140	2003	LC		T D'Amico
141	2003	LC		G Crainz
142	2003	BR		G Colotti
143	2003	Potop		T Negri
144	2004	Movimento studentesco		E Rutigliano
145	2004	LC		E De Luca
146	2004	LC		T D'Amico
147	2004	BR		A Franceschini
148	2004	BR		V Morucci
149	2004	LC		L Foa
150	2004	Potop		O Scalzone
151	2005	Autonomia		E Palandri
152	2005	BR		G Colotti
153	2005	LC		A Sofri
154	2005	BR		V Morucci
155	2005	BR		V Guagliardo
156	2005	LC		A Bravo
157	2005	PL		M Bignami
158	2005	Potop		Alberto Magnaghi, Stefano Lepri, Valerio Morucci, Franco Berardi, Oreste Scalzone, Francesco Bellosi, Lanfranco Pace, Letizia Paolozzi, etc
159	2005	PL		S Segio
160	2005	PAC		A Cavallina
161	2005	BR		C Garavalia
162	2005	assorted		A Pera, A Camilleri, D Frascati, E Belotti, E Gallo, E De Luca, G Colotti, G Manfredi, G Alimonti, I Scanner, M Carlotto, N Balestrini, P Staccioli, R Tumminelli, S Tassinari
163	2006	LC		G de Luna
164	2006	Radio Città Futura		S Provvisionato
165	2006	mix		P Sorbi, Bifo, O Scalzone, M Boato, A Sofri ecc
166	2006	BR		A Faranda
167	2006	BR		P Gallinari
168	2006	LC		G Sartorio
169	2006	PL		S Segio
170	2006	LC		M Fossati
171	2006	PAC		C Battisti
172	2006	Collettivi giovanili		L Rastello
173	2006	LC		B Arpaia
174	2006	LC		M Revelli
175	2006	Potop		O Scalzone
176	2007	BR		R Fiore
177	2007	UCC		W Labate
178	2007	LC		T D'Amico
179	2007	Potop		T Negri
180	2008	LC		T D'Amico
181	2008	Autonomia		F Scozzari
182	2008	BR		A Franceschini
183	2008	Movimento studentesco, LC		M Boato
184	2008	BR		A Franceschini
185	2008	BR		V Morucci
186	2008	LC		E De Luca
187	2008	LC		E De Luca
188	2008	LC		A Bravo
189	2008	Radio Città Futura		S Provvisionato
190	2008	Potop		T Negri
191	2008	LC		L Manconi
192	2008	Potop		F Piperno
193	2009	LC		A Sofri
194	2009	LC		S Martin
195	2009	LC		G Crainz
196	2009	Radio Città Futura		S Provvisionato
197	2009	BR		B Balzerani
198	2009	LC		G Mughini
199	2009	LC		G de Luna
200	2009	LC		M Fossati
201	2009	BR		T Paroli
202	2010	PL		A Bonvicini
203	2010	Autonomia		E Palandri
204	2010	LC		M Calopresti
205	2010	LC		A Cambria
206	2010	PAC		C Lavazza
207	2011	LC		G Sartorio
208	2011	LC		M Fossati
209	2011	Radio Città Futura		A Mordenti
210	2011	BR		B Balzerani
211	2012	Autonomia		G Vitali
212	2012	LC		G Mughini
213	2012	LC		G de Luna
214	2012	BR		M Prette
215	2012	LC		M Revelli
216	2012	LC		G Crainz
217	2012	LC		M Revelli
218	2012	LC		A Sofri
219	2012	NAP		R Marrone
220	2012	PL		B La Ronga, M G Gibertini, R Rosso
221	2013	PL		B La Ronga, M G Gibertini, R Rosso, E Galmozzi
222	2013	LC		M Calopresti
223	2013	assorted		P Margini, M Battisaldo
224	2013	PL		M Gibertini
225	2013	BR		G Senzani
226	2013	BR		E Triaca
227	2013	PL		M Bignami
228	2013	Potop		N Balestrini
229	2014	BR		A Franceschini
230	2014	LC		M Fossati
231	2014	LC		E De Luca
232	2014	BR		B Balzerani
233	2014	BR		G Senzani
234	2015	Radio Città Futura		S Provvisionato
235	2015	BR		F Donati
236	2015	BR		B Balzerani
237	2015	LC		M Fossati
238	2015	LC		M Fossati
239	2015	LC		M Fossati
240	2015	Potop		T Negri
241	2016	LC		G Crainz
242	2016	LC		L Manconi
243	2016	LC		S Della Casa
244	2016	LC		M Fossati
245	2016	Autonomia		G Vitali
246	2017	BR		B Balzerani
247	2017	LC		P Brogi
248	2017	BR		P Persichetti e E Santalena
249	2017	BR		M T Romeo
250	2017	Potop		T Negri
251	2017	BR		M Iannelli
252	2017	LC		E Deaglio
253	2017	NAP		G Panizzari
254	2017	LC		T D'Amico
255	2017	Potop		T Negri
256	2017	Potop		O Scalzone
257	2018	Movimento studentesco, LC		M Boato
258	2018	LC		G Viale
259	2018	BR		F Bonisoli
260	2018	NAP		P Abatangelo
261	2018	PL		M Premoli
262	2018	LC		G Mughini
263	2018	LC		L Pero
264	2018	LC		G Mughini
265	2018	Potop		F Berardi, N Balestrini, S Bianchi
266	2018	LC		T Capuozzo
267	2018	Autonomia		P Ricci
268	2019	LC		G de Luna
269	2019	LC		E Deaglio
270	2019	LC		M Fossati
271	2019	LC		E De Luca
272	2019	LC		P Brogi
273	2019	LC		L Bobbio
274	2019	LC		E De Luca
275	2019	assorted		A Bignami, W Labate, G de Luna
276	2019	LC		Michele Boato
277	2019	NAP		G Panizzari
278	2019	PL		E Galmozzi
279	2020	BR		B Balzerani
280	2020	BR		P Ferrari
281	2020	Autonomia		P Ricci
282	2020	BR		A Vai
283	2020	PL		M Bignami
284	2020	LC		T Capuozzo
285	2020	LC		M Boato
286	2020	LC		M Sinibaldi, L Manconi, M Radice
287	2020	Potop		T Negri
288	2021	PL		M Premoli
289	2021	PAC		A Cavallina
290	2022	LC		M Calopresti
291	2022	LC		P Liguori, Lerner, de luca, franzinetti, boato, sinibaldi e Mughini
292	2022	LC		F Salmoni
293	2022	BR		P Persichetti
294	2023	LC		L Manconi
295	2023	LC		E Deaglio
296	2023	Autonomia		V Minnella
297	2023	LC		G de Luna
298	2023	LC		G Viale
299	2023	PAC		A Cavallina
300	2024	LC		E Deaglio
301	2024	LC		G Pietrostefani
302	2024	LC		M Revelli
303	2024	?		S Turrini
304	2024	LC		F Salmoni
305	2024	LC		G Viale
306	2024	LC		S Della Casa
307	2024	LC		G Mughini
308	2025	BR		P Persichetti
309	2025	PL		M Gibertini
310	2025	LC		T D'Amico
Data

 

[WorldTraveler]

Technically the lists of authors per organization is based on this:

memoriali_17.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Cognome		Nome	Contribuito un testo?		org1	org2	org3	org4
2	S Turrini	Turrini	S	Severino	1		?
3	F D'Ursi	D'Ursi	F	Francesco	0		PL
4	M Bignami	Bignami	M	Maurice	3		PL
5	E Galmozzi	Galmozzi	E	Enrico/Chicco	2		PL	LC
6	S Segio	Segio	S	Sergio	2		PL	LC
7	B La Ronga	La Ronga	B	Bruno	2		PL
8	M Gibertini	Gibertini	M	Maurizio Gibo	2		PL
9	R Rosso	Rosso	R	Roberto	2		PL
10	M Viscardi	Viscardi	M	Michele	0		PL	LC
11	S D'Elia	D'Elia	S	Sergio	0		PL
Lista di Nomi controllati

Cell Formulas
Range	Formula
A2:A11	A2	=C2&" "&B2
C2:C11	C2	=LEFT(D2)&IF(ISNUMBER(FIND(" ",B2)),,"")
E2:E11	E2	=COUNT(FILTER(Data!A:A,ISNUMBER(SEARCH('Lista di Nomi controllati'!A2,Data!D:D))))

Lambda Functions
Name	Formula
S	=LAMBDA(a,b,ISNUMBER(SEARCH(a,b)))

 

[saboh12617]

Hello,

I think i am getting easier results with PowerQuery. Would this alternative work for you?

Below are some data that i managed to extract:
We can then easily work with this base with formulas or pivot tables/charts

Book1
	A	B	C
1	GrpByA.year	Orga	GrpY-O.author
2	1968	assorted	P Liguori
3	1968	assorted	O Scalzone
4	1968	assorted	Pierpaolo Balbo
5	1968	assorted	Massimo Barone
6	1968	assorted	Massimiliano Fuksas
7	1968	assorted	Sergio Petruccioli
8	1968	assorted	Pure Franco
9	1968	assorted	and Andrea Silipo
10	1968	assorted	[controllare chi qua e' attivista]
11	1971	Potop	N Balestrini
12	1971	Autonomia	N Balestrini
13	1973	LC	T D'Amico
14	1973	LC	V Franzinetti
15	1974	Potop	various
16	1975	Autonomia	D Mariotti
17	1975	Radio Città Futura	A Mordenti
18	1975	LC	E Deaglio
19	1975	Movimento studentesco	E Rutigliano
20	1976	Potop	N Balestrini
21	1976	Autonomia	N Balestrini
22	1976	LC	M Radice
23	1976	LC	L Ravera
24	1976	LC	L Manconi
25	1976	LC	N Vento
26	1977	LC	L Manconi
27	1977	LC	L Manconi
28	1977	LC	M Sinibaldi
29	1977	Autonomia	A Pazienza
30	1977	PAC	A Cavallina
31	1978	LC	L Manconi
32	1978	LC	G Lerner e M Sinibaldi
33	1978	LC	G Viale
34	1978	LC	M Colafato
35	1978	LC	M Revelli
36	1978	LC	P Hutter
37	1978	LC	M L Radice
38	1978	LC	G Mughini
39	1979	LC	L Manconi
40	1979	LC	L Boato
41	1979	Autonomia	E Palandri
42	1979	Movimento studentesco, LC	M Boato
43	1979	?	V Borelli
44	1980	LC	L Manconi
45	1980	BR	G Naria
46	1980	assorted	R Curcio e M Rostagno
47	1981	LC	L Manconi
48	1981	BR	Giorgio Anonimo
49	1981	BR	G Codrini (inventato?)
50	1983	Potop	T Negri
51	1983	BR	P Peci
52	1984	LC	G de Luna
53	1985	Potop	T Negri
54	1985	LC	A Ceste
55	1986	Autonomia	E Palandri
56	1986	Autonomia	E Palandri
57	1986	NAP	G Panizzari
58	1987	Potop	N Balestrini
59	1987	Potop	F Berardi
60	1987	Autonomia	N Balestrini
61	1987	LC	G Mughini
62	1987	BR	E Fenzi
63	1988	Potop	O Scalzone
64	1988	Potop	N Balestrini e P Moroni
65	1988	Movimento studentesco	E Rutigliano
66	1988	Movimento studentesco	M Boato
67	1988	BR	G Naria
68	1988	LC	A Ceste
69	1988	Autonomia	N Balestrini e P Moroni
70	1989	Potop	N Balestrini
71	1989	LC	M Revelli
72	1989	LC	M Revelli
73	1989	Autonomia	E Vesce
74	1989	BR	F Piccioni
75	1989	BR	F Lo Bianco
76	1989	Radio Città Futura	S Provvisionato
77	1990	LC	L Manconi
78	1990	LC	M Revelli
79	1990	LC	A Ceste
80	1990	LC	P Ortoleva
81	1990	LC	M Calopresti
82	1990	LC	A Sofri
83	1990	NAP	G Panizzari
84	1991	BR	G Naria
85	1991	BR	Maria Cappello
86	1991	BR	Tiziana Cherubini
87	1991	BR	Antonio De Luca
88	1991	BR	Franco Galloni
89	1991	BR	Franco Grilli.
90	1991	BR	Rossella Lupo
91	1991	BR	Fulvia Matarazzo
92	1991	BR	Stefano Minguzzi
93	1991	BR	Fabio Ravalli e dei militanti rivoluzionari Daniele Bencini
94	1991	BR	Vincenza Vaccaro
95	1991	BR	Marco Venturini
96	1991	BR	V Guagliardo
97	1991	LC	A Sofri
98	1992	LC	T D'Amico
99	1992	LC	M Revelli
100	1992	LC	L Marino
101	1993	LC	A Ceste
102	1993	Radio Città Futura	S Provvisionato
103	1993	BR	M Cappello
104	1993	BR	R Curcio
105	1993	BR	R Curcio
106	1993	PAC	C Battisti
107	1994	Potop	N Balestrini
108	1994	Autonomia	N Balestrini
109	1994	LC	A Ceste
110	1994	BR	V Guagliardo
111	1994	BR	A Faranda
112	1994	BR	A Franceschini
113	1994	BR	M Moretti
114	1994	BR	F F De Albuquerquee M Iannelli (non chiaro quanto intervenuto
115	1994	BR	ne se davvero BR)
116	1994	BR	V Morucci
117	1995	LC	L Manconi
118	1995	LC	P Ortoleva
119	1995	LC	M Calopresti
120	1995	LC	A Sofri
121	1995	BR	A Braghetti
122	1995	BR	P Gallinari L Santilli (moglie di PG)
123	1996	LC	A Sofri
124	1996	LC	A Sofri L Sofri
125	1996	LC	curatori
126	1996	BR	N Mantovani
127	1996	UCC	W Labate
128	1996	Radio Città Futura	E Bassignano
129	1997	BR	G Naria
130	1997	BR	V Guagliardo
131	1997	BR	A Franceschini
132	1997	BR	G Colotti
133	1997	LC	A Sofri
134	1997	assorted	F Piperno Ida Dominijanni
135	1997	assorted	Felice Liperi
136	1997	assorted	Luigi Manconi
137	1997	assorted	Marco Bascetta
138	1997	assorted	Marco Grispigni
139	1997	assorted	Alfonso Maurizio Iacono
140	1997	assorted	Renato Nicolini
141	1997	assorted	Rina Gagliardi
142	1997	assorted	Rossana Rossanda
143	1997	assorted	Paolo Virno
144	1997	Autonomia	F Scozzari
145	1997	FCC	T Z Zanetti
146	1998	Potop	O Scalzone
147	1998	Potop	F Berardi
148	1998	LC	E Deaglio
149	1998	LC	M Revelli
150	1998	LC	G Mughini
151	1998	LC	A Ceste
152	1998	LC	P Ortoleva
153	1998	LC	A Sofri
154	1998	PAC	C Battisti
155	1998	BR	A Braghetti
156	1998	BR	B Balzerani
157	1998	assorted	mix
158	1999	Potop	O Scalzone
159	1999	BR	V Morucci
160	1999	BR	R Curcio F Silvestri
161	1999	BR	A Vai
162	2000	BR	G Colotti
163	2000	FCC	T Z Zanetti
164	2000	LC	G Crainz
165	2001	LC	M Fossati
166	2001	LC	M Fossati
167	2002	LC	G Mughini
168	2002	Potop	F Berardi
169	2002	Potop	F Berardi
170	2002	Potop	F Berardi
171	2002	BR	V Guagliardo
172	2003	LC	T D'Amico
173	2003	LC	G Crainz
174	2003	LC	M Fossati
175	2003	Potop	T Negri
176	2003	Radio Città Futura	S Provvisionato
177	2003	PAC	C Battisti
178	2003	PAC	C Battisti
179	2003	BR	G Colotti
180	2004	Potop	O Scalzone
181	2004	LC	T D'Amico
182	2004	LC	E De Luca
183	2004	LC	L Foa
184	2004	Movimento studentesco	E Rutigliano
185	2004	BR	A Franceschini
186	2004	BR	V Morucci
187	2005	assorted	N Balestrini
188	2005	assorted	G Colotti
189	2005	assorted	E De Luca
190	2005	assorted	A Pera
191	2005	assorted	A Camilleri
192	2005	assorted	D Frascati
193	2005	assorted	E Belotti
194	2005	assorted	E Gallo
195	2005	assorted	G Manfredi
196	2005	assorted	G Alimonti
197	2005	assorted	I Scanner
198	2005	assorted	M Carlotto
199	2005	assorted	P Staccioli
200	2005	assorted	R Tumminelli
201	2005	assorted	S Tassinari
202	2005	PAC	A Cavallina
203	2005	Autonomia	E Palandri
204	2005	LC	A Sofri
205	2005	LC	A Bravo
206	2005	BR	V Guagliardo
207	2005	BR	V Morucci
208	2005	BR	G Colotti
209	2005	BR	C Garavalia
210	2005	PL	M Bignami
211	2005	PL	S Segio
212	2005	Potop	Alberto Magnaghi
213	2005	Potop	Stefano Lepri
214	2005	Potop	Valerio Morucci
215	2005	Potop	Franco Berardi
216	2005	Potop	Oreste Scalzone
217	2005	Potop	Francesco Bellosi
218	2005	Potop	Lanfranco Pace
219	2005	Potop	Letizia Paolozzi
220	2005	Potop	etc
221	2006	mix	O Scalzone
222	2006	mix	M Boato
223	2006	mix	P Sorbi
224	2006	mix	Bifo
225	2006	mix	A Sofri ecc
226	2006	Potop	O Scalzone
227	2006	LC	M Revelli
228	2006	LC	G de Luna
229	2006	LC	M Fossati
230	2006	LC	G Sartorio
231	2006	LC	B Arpaia
232	2006	Radio Città Futura	S Provvisionato
233	2006	PAC	C Battisti
234	2006	BR	A Faranda
235	2006	BR	P Gallinari
236	2006	PL	S Segio
237	2006	Collettivi giovanili	L Rastello
238	2007	LC	T D'Amico
239	2007	Potop	T Negri
240	2007	UCC	W Labate
241	2007	BR	R Fiore
242	2008	LC	T D'Amico
243	2008	LC	L Manconi
244	2008	LC	E De Luca
245	2008	LC	E De Luca
246	2008	LC	A Bravo
247	2008	Movimento studentesco, LC	M Boato
248	2008	Potop	T Negri
249	2008	Potop	F Piperno
250	2008	Radio Città Futura	S Provvisionato
251	2008	BR	A Franceschini
252	2008	BR	A Franceschini
253	2008	BR	V Morucci
254	2008	Autonomia	F Scozzari
255	2009	LC	G Mughini
256	2009	LC	G de Luna
257	2009	LC	A Sofri
258	2009	LC	G Crainz
259	2009	LC	M Fossati
260	2009	LC	S Martin
261	2009	Radio Città Futura	S Provvisionato
262	2009	BR	B Balzerani
263	2009	BR	T Paroli
264	2010	Autonomia	E Palandri
265	2010	LC	M Calopresti
266	2010	LC	A Cambria
267	2010	PL	A Bonvicini
268	2010	PAC	C Lavazza
269	2011	Radio Città Futura	A Mordenti
270	2011	BR	B Balzerani
271	2011	LC	M Fossati
272	2011	LC	G Sartorio
273	2012	LC	M Revelli
274	2012	LC	M Revelli
275	2012	LC	G Mughini
276	2012	LC	G de Luna
277	2012	LC	A Sofri
278	2012	LC	G Crainz
279	2012	Autonomia	G Vitali
280	2012	BR	M Prette
281	2012	NAP	R Marrone
282	2012	PL	B La Ronga
283	2012	PL	M G Gibertini
284	2012	PL	R Rosso
285	2013	Potop	N Balestrini
286	2013	LC	M Calopresti
287	2013	PL	M Bignami
288	2013	PL	B La Ronga
289	2013	PL	M G Gibertini
290	2013	PL	R Rosso
291	2013	PL	E Galmozzi
292	2013	PL	M Gibertini
293	2013	assorted	P Margini
294	2013	assorted	M Battisaldo
295	2013	BR	G Senzani
296	2013	BR	E Triaca
297	2014	BR	A Franceschini
298	2014	BR	B Balzerani
299	2014	BR	G Senzani
300	2014	LC	M Fossati
301	2014	LC	E De Luca
302	2015	Potop	T Negri
303	2015	Radio Città Futura	S Provvisionato
304	2015	BR	B Balzerani
305	2015	BR	F Donati
306	2015	LC	M Fossati
307	2015	LC	M Fossati
308	2015	LC	M Fossati
309	2016	LC	L Manconi
310	2016	LC	G Crainz
311	2016	LC	M Fossati
312	2016	LC	S Della Casa
313	2016	Autonomia	G Vitali
314	2017	Potop	O Scalzone
315	2017	Potop	T Negri
316	2017	Potop	T Negri
317	2017	LC	T D'Amico
318	2017	LC	E Deaglio
319	2017	LC	P Brogi
320	2017	NAP	G Panizzari
321	2017	BR	B Balzerani
322	2017	BR	P Persichetti e E Santalena
323	2017	BR	M T Romeo
324	2017	BR	M Iannelli
325	2018	Potop	N Balestrini
326	2018	Potop	F Berardi
327	2018	Potop	S Bianchi
328	2018	LC	G Viale
329	2018	LC	G Mughini
330	2018	LC	G Mughini
331	2018	LC	L Pero
332	2018	LC	T Capuozzo
333	2018	Movimento studentesco, LC	M Boato
334	2018	BR	F Bonisoli
335	2018	NAP	P Abatangelo
336	2018	PL	M Premoli
337	2018	Autonomia	P Ricci
338	2019	LC	E Deaglio
339	2019	LC	G de Luna
340	2019	LC	M Fossati
341	2019	LC	E De Luca
342	2019	LC	E De Luca
343	2019	LC	P Brogi
344	2019	LC	L Bobbio
345	2019	LC	Michele Boato
346	2019	assorted	G de Luna
347	2019	assorted	W Labate
348	2019	assorted	A Bignami
349	2019	NAP	G Panizzari
350	2019	PL	E Galmozzi
351	2020	LC	M Radice
352	2020	LC	L Manconi
353	2020	LC	M Sinibaldi
354	2020	LC	M Boato
355	2020	LC	T Capuozzo
356	2020	Potop	T Negri
357	2020	BR	B Balzerani
358	2020	BR	A Vai
359	2020	BR	P Ferrari
360	2020	PL	M Bignami
361	2020	Autonomia	P Ricci
362	2021	PAC	A Cavallina
363	2021	PL	M Premoli
364	2022	LC	P Liguori
365	2022	LC	M Calopresti
366	2022	LC	Lerner
367	2022	LC	de luca
368	2022	LC	franzinetti
369	2022	LC	boato
370	2022	LC	sinibaldi e Mughini
371	2022	LC	F Salmoni
372	2022	BR	P Persichetti
373	2023	LC	E Deaglio
374	2023	LC	L Manconi
375	2023	LC	G Viale
376	2023	LC	G de Luna
377	2023	PAC	A Cavallina
378	2023	Autonomia	V Minnella
379	2024	LC	E Deaglio
380	2024	LC	G Viale
381	2024	LC	M Revelli
382	2024	LC	G Mughini
383	2024	LC	S Della Casa
384	2024	LC	F Salmoni
385	2024	LC	G Pietrostefani
386	2024	?	S Turrini
387	2025	LC	T D'Amico
388	2025	PL	M Gibertini
389	2025	BR	P Persichetti
Table1_1__2

 

[WorldTraveler]

This is fantastic! Can I ask how you extracted this/what the steps are for power query?

 

[saboh12617]

Yes, sure:

1. Select the "data" table and go to Tab Data > from range (in my query it is "Table2")
2. Filter the "group" column with no null
3. Split column authors by commas, to rows
4. Add a custom function column and trim the authors column to remove spaces, use ita s new authors column and delete the old one
5. Group by authors
6. Expand Group, group2 and year
7. Unpivot columns group and group2
8. Group by Year and "organization"/"group" = from the step 6
9. Expand authors, sort by year & load

The corresponding query is below

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"year", Int64.Type}, {"group", type text}, {"group2", type text}, {"author", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([group] <> null)),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"author", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "author"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"author", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Trim([author])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"author"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "author"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"author"}, {{"GrpByA", each _, type table [year=nullable number, group=nullable text, group2=nullable text, author=nullable text]}}),
    #"Expanded GrpByA" = Table.ExpandTableColumn(#"Grouped Rows", "GrpByA", {"year", "group", "group2"}, {"GrpByA.year", "GrpByA.group", "GrpByA.group2"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded GrpByA", {"author", "GrpByA.year"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"GrpByA.year", "Value"}, {{"GrpFinal", each _, type table [author=nullable text, GrpByA.year=nullable number, Value=text]}}),
    #"Expanded GrpFinal" = Table.ExpandTableColumn(#"Grouped Rows1", "GrpFinal", {"author"}, {"GrpFinal.author"}),
    #"Sorted Rows" = Table.Sort(#"Expanded GrpFinal",{{"GrpByA.year", Order.Ascending}})
in
    #"Sorted Rows"

Nota : it could certainly be simplified, but i did it using the UI for ease of use.

 

[WorldTraveler]

Apologies for the delay--it took me a while to get it to work, but many thanks!!