Stacking date from a dynamic table

[MCreighton]

I have a dynamic table populated from a variety of sources which is essentially structured as:
dates as column headers (dynamical expands as new dates are entered, object names as left-most column (these names are currently fixed but more may be added), and then numeric data showing how many objects for each date (includes blanks).

I need to be able to stack this data so it can be exported into an external web-site. The stack would need three columns:
The date taken from the existing table column headers, the name of the object, the number of objects on that date.
And it should only stack data where the number of objects is not blank for the date in question.

I understand the basic concepts of stacking but taking successive dates from the existing table column headers to use them as 'row headers' is beyond me at the moment.

Hopefully I've explained the problem properly, but I've attached an image to. give an idea of what I mean. Any help gratefully received.

Mike

 

[MCreighton]

I have a table (mini-sheet sample attached) comprising the records of the number of birds, by species, seen on specific days. Currently this shows the date as column headers with the bird species as row headers (actually two row headers - British Common Name and Scientific Name). In order to import this data into an external web-site I need to create a single list, in date order, showing just four columns: Date; British Common Name; Scientific Name; Number. I would like to ignore 0 numbers in the list, but I can filter those manually if that makes life easier.

Any help or pointers gratefully received.

Many thanks
Mike

Book2
	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
1	COMBINED ALL TRANSACTS British Common	Scientific	02/10/19	08/10/19	16/10/19	22/10/19	29/10/19	03/11/19	07/11/19	14/11/19	20/11/19	27/11/19	04/12/19	11/12/19	18/12/19	01/01/20	08/01/20	15/01/20	22/01/20	29/01/20	05/02/20	12/02/20	19/02/20	26/02/20	04/03/20
2	Brent Goose	Branta bernicla	81	-	-	140	-	-	3	200	44	-	43	-	11	42	-	5	2	7	7	210	2	-	-
3	Dark-bellied Brent Goose	Branta bernicla bernicla	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
4	Pale-bellied Brent Goose	Branta bernicla hrota	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
5	Black Brant	Branta bernicla nigricans	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
6	Red-breasted Goose	Branta ruficollis	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
7	Canada Goose	Branta canadensis	25	-	360	-	20	-	-	-	-	2	-	12	-	-	-	-	-	5	2	-	7	-	8
8	Barnacle Goose	Branta leucopsis	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
9	Cackling Goose	Branta hutchinsii	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
10	Snow Goose	Anser caerulescens	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
11	Greylag Goose	Anser anser	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
12	Taiga Bean Goose	Anser fabalis	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
13	Pink-footed Goose	Anser brachyrhynchus	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
14	Tundra Bean Goose	Anser serrirostris	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
15	European White-fronted Goose	Anser albifrons	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
16	Greenland White-fronted Goose	Anser albifrons flavirostris	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
17	Lesser White-fronted Goose	Anser erythropus	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
18	Mute Swan	Cygnus olor	2	-	-	1	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
19	Bewick's Swan	Cygnus columbianus	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
20	Whooper Swan	Cygnus cygnus	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
21	Egyptian Goose	Alopochen aegyptiaca	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
22	Shelduck	Tadorna tadorna	-	-	-	-	-	-	-	-	-	-	-	2	-	6	2	1	-	3	2	1	4	4	12
23	Ruddy Shelduck	Tadorna ferruginea	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
24	Mandarin Duck	Aix galericulata	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
25	Baikal Teal	Sibirionetta formosa	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
26	Garganey	Spatula querquedula	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
27	Blue-winged Teal	Spatula discors	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
28	Shoveler	Spatula clypeata	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
29	Gadwall	Mareca strepera	-	4	-	2	-	2	-	-	34	3	-	-	1	-	-	-	-	-	-	-	-	-	11
30	Falcated Duck	Mareca falcata	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
31	Wigeon	Mareca penelope	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	10	-	-	-
32	American Wigeon	Mareca americana	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
33	Mallard	Anas platyrhynchos	6	14	-	4	18	5	-	3	9	21	8	2	8	2	13	3	3	7	7	-	-	-	3
34	Black Duck	Anas rubripes	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
35	Pintail	Anas acuta	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
36	Teal	Anas crecca	2	37	-	1	6	-	-	19	-	3	15	-	9	25	11	1	-	20	12	12	10	-	47
Sheet1

 

[MCreighton]

Please do not start a new thread for this question. Just post the data here.

Sorry too late - I thought the dynamic table stuff was just confusing matters so thought it would be easier to start again. My mistake.

 

[Fluff]

Never too late

How about

Fluff.xlsm
	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
1	COMBINED ALL TRANSACTS British Common	Scientific	02/10/2019	08/10/2019	16/10/2019	22/10/2019	29/10/2019	03/11/2019	07/11/2019	14/11/2019	20/11/2019	27/11/2019	04/12/2019	11/12/2019	18/12/2019	01/01/2020	08/01/2020	15/01/2020	22/01/2020	29/01/2020	05/02/2020	12/02/2020	19/02/2020	26/02/2020	04/03/2020
2	Brent Goose	Branta bernicla	81	0	0	140	0	0	3	200	44	0	43	0	11	42	0	5	2	7	7	210	2	0	0
3	Dark-bellied Brent Goose	Branta bernicla bernicla	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	Pale-bellied Brent Goose	Branta bernicla hrota	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	Black Brant	Branta bernicla nigricans	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Red-breasted Goose	Branta ruficollis	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	Canada Goose	Branta canadensis	25	0	360	0	20	0	0	0	0	2	0	12	0	0	0	0	0	5	2	0	7	0	8
8	Barnacle Goose	Branta leucopsis	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	Cackling Goose	Branta hutchinsii	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	Snow Goose	Anser caerulescens	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	Greylag Goose	Anser anser	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	Taiga Bean Goose	Anser fabalis	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	Pink-footed Goose	Anser brachyrhynchus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	Tundra Bean Goose	Anser serrirostris	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	European White-fronted Goose	Anser albifrons	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	Greenland White-fronted Goose	Anser albifrons flavirostris	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	Lesser White-fronted Goose	Anser erythropus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	Mute Swan	Cygnus olor	2	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	Bewick's Swan	Cygnus columbianus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	Whooper Swan	Cygnus cygnus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	Egyptian Goose	Alopochen aegyptiaca	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	Shelduck	Tadorna tadorna	0	0	0	0	0	0	0	0	0	0	0	2	0	6	2	1	0	3	2	1	4	4	12
23	Ruddy Shelduck	Tadorna ferruginea	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24	Mandarin Duck	Aix galericulata	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
25	Baikal Teal	Sibirionetta formosa	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
26	Garganey	Spatula querquedula	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27	Blue-winged Teal	Spatula discors	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
28	Shoveler	Spatula clypeata	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
29	Gadwall	Mareca strepera	0	4	0	2	0	2	0	0	34	3	0	0	1	0	0	0	0	0	0	0	0	0	11
30	Falcated Duck	Mareca falcata	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
31	Wigeon	Mareca penelope	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	10	0	0	0
32	American Wigeon	Mareca americana	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
33	Mallard	Anas platyrhynchos	6	14	0	4	18	5	0	3	9	21	8	2	8	2	13	3	3	7	7	0	0	0	3
34	Black Duck	Anas rubripes	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
35	Pintail	Anas acuta	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
36	Teal	Anas crecca	2	37	0	1	6	0	0	19	0	3	15	0	9	25	11	1	0	20	12	12	10	0	47
37
38
39
40	02/10/2019	Brent Goose	Branta bernicla	81
41	02/10/2019	Canada Goose	Branta canadensis	25
42	02/10/2019	Mute Swan	Cygnus olor	2
43	02/10/2019	Mallard	Anas platyrhynchos	6
44	02/10/2019	Teal	Anas crecca	2
45	08/10/2019	Gadwall	Mareca strepera	4
46	08/10/2019	Mallard	Anas platyrhynchos	14
47	08/10/2019	Teal	Anas crecca	37
48	16/10/2019	Canada Goose	Branta canadensis	360
49	22/10/2019	Brent Goose	Branta bernicla	140
50	22/10/2019	Mute Swan	Cygnus olor	1
51	22/10/2019	Gadwall	Mareca strepera	2
52	22/10/2019	Mallard	Anas platyrhynchos	4
53	22/10/2019	Teal	Anas crecca	1
54	29/10/2019	Canada Goose	Branta canadensis	20
55	29/10/2019	Mallard	Anas platyrhynchos	18
56	29/10/2019	Teal	Anas crecca	6
57	03/11/2019	Gadwall	Mareca strepera	2
58	03/11/2019	Mallard	Anas platyrhynchos	5
59	07/11/2019	Brent Goose	Branta bernicla	3
60	14/11/2019	Brent Goose	Branta bernicla	200
61	14/11/2019	Mallard	Anas platyrhynchos	3
62	14/11/2019	Teal	Anas crecca	19
63	20/11/2019	Brent Goose	Branta bernicla	44
64	20/11/2019	Gadwall	Mareca strepera	34
65	20/11/2019	Mallard	Anas platyrhynchos	9
66	27/11/2019	Canada Goose	Branta canadensis	2
67	27/11/2019	Gadwall	Mareca strepera	3
68	27/11/2019	Mallard	Anas platyrhynchos	21
69	27/11/2019	Teal	Anas crecca	3
70	04/12/2019	Brent Goose	Branta bernicla	43
71	04/12/2019	Mallard	Anas platyrhynchos	8
72	04/12/2019	Teal	Anas crecca	15
73	11/12/2019	Canada Goose	Branta canadensis	12
74	11/12/2019	Shelduck	Tadorna tadorna	2
75	11/12/2019	Mallard	Anas platyrhynchos	2
76	18/12/2019	Brent Goose	Branta bernicla	11
77	18/12/2019	Gadwall	Mareca strepera	1
78	18/12/2019	Mallard	Anas platyrhynchos	8
79	18/12/2019	Teal	Anas crecca	9
80	01/01/2020	Brent Goose	Branta bernicla	42
81	01/01/2020	Shelduck	Tadorna tadorna	6
82	01/01/2020	Mallard	Anas platyrhynchos	2
83	01/01/2020	Teal	Anas crecca	25
84	08/01/2020	Shelduck	Tadorna tadorna	2
85	08/01/2020	Mallard	Anas platyrhynchos	13
86	08/01/2020	Teal	Anas crecca	11
87	15/01/2020	Brent Goose	Branta bernicla	5
88	15/01/2020	Shelduck	Tadorna tadorna	1
89	15/01/2020	Mallard	Anas platyrhynchos	3
90	15/01/2020	Teal	Anas crecca	1
91	22/01/2020	Brent Goose	Branta bernicla	2
92	22/01/2020	Mallard	Anas platyrhynchos	3
93	29/01/2020	Brent Goose	Branta bernicla	7
94	29/01/2020	Canada Goose	Branta canadensis	5
95	29/01/2020	Shelduck	Tadorna tadorna	3
96	29/01/2020	Mallard	Anas platyrhynchos	7
97	29/01/2020	Teal	Anas crecca	20
98	05/02/2020	Brent Goose	Branta bernicla	7
99	05/02/2020	Canada Goose	Branta canadensis	2
100	05/02/2020	Shelduck	Tadorna tadorna	2
101	05/02/2020	Wigeon	Mareca penelope	1
102	05/02/2020	Mallard	Anas platyrhynchos	7
103	05/02/2020	Teal	Anas crecca	12
104	12/02/2020	Brent Goose	Branta bernicla	210
105	12/02/2020	Shelduck	Tadorna tadorna	1
106	12/02/2020	Wigeon	Mareca penelope	10
107	12/02/2020	Teal	Anas crecca	12
108	19/02/2020	Brent Goose	Branta bernicla	2
109	19/02/2020	Canada Goose	Branta canadensis	7
110	19/02/2020	Shelduck	Tadorna tadorna	4
111	19/02/2020	Teal	Anas crecca	10
112	26/02/2020	Shelduck	Tadorna tadorna	4
113	04/03/2020	Canada Goose	Branta canadensis	8
114	04/03/2020	Shelduck	Tadorna tadorna	12
115	04/03/2020	Gadwall	Mareca strepera	11
116	04/03/2020	Mallard	Anas platyrhynchos	3
117	04/03/2020	Teal	Anas crecca	47
Data

Cell Formulas
Range	Formula
A40:D117	A40	=LET(Hdr,C1:Y1,Data,A2:B36,nums,C2:Y36,c,COLUMNS(Hdr),s,SEQUENCE(ROWS(Data)*c,,0),xa,INDEX(Hdr,MOD(s,c)+1),xb,INDEX(Data,INT(s/c)+1,{1,2}),xc,INDEX(nums,INT(s/c)+1,MOD(s,c)+1),SORT(FILTER(CHOOSE({1,2,3,4},xa,INDEX(xb,,1),INDEX(xb,,2),xc),xc>0)))
Dynamic array formulas.

 

[KRice]

I would use Power Query for this:
Convert your table to an official table (hit Ctrl t and indicate you have headers).
While in the table, click Data > From Table/Range to open PQ (your table should appear).
Select the 1st two columns (select heading of 1st and hold Ctrl while clicking 2nd heading).
From top menu, click Transform > Unpivot Columns > Unpivot Other Columns (produces a 4-column table).
Rename headings (double click to do so), drag columns to desired positions (drag headings) and then filter out 0's of Number column (using drop down filter arrow in heading).
Finally, Home > Close and Load to load back into workbook.

MrExcel_20220831.xlsx
	A	B	C	D
1	Date	COMBINED ALL TRANSACTS British Common	Scientific	Number
2	10/2/2019	Brent Goose	Branta bernicla	81
3	10/22/2019	Brent Goose	Branta bernicla	140
4	11/7/2019	Brent Goose	Branta bernicla	3
5	11/14/2019	Brent Goose	Branta bernicla	200
6	11/20/2019	Brent Goose	Branta bernicla	44
7	12/4/2019	Brent Goose	Branta bernicla	43
8	12/18/2019	Brent Goose	Branta bernicla	11
9	1/1/2020	Brent Goose	Branta bernicla	42
10	1/15/2020	Brent Goose	Branta bernicla	5
11	1/22/2020	Brent Goose	Branta bernicla	2
12	1/29/2020	Brent Goose	Branta bernicla	7
13	2/5/2020	Brent Goose	Branta bernicla	7
14	2/12/2020	Brent Goose	Branta bernicla	210
15	2/19/2020	Brent Goose	Branta bernicla	2
16	10/2/2019	Canada Goose	Branta canadensis	25
17	10/16/2019	Canada Goose	Branta canadensis	360
18	10/29/2019	Canada Goose	Branta canadensis	20
Table1

 

[MCreighton]

That is marvellous and works perfectly. I will now have to study it in detail to see what it's doing so I can learn this technique for myself. Many, many thanks.

 

[MCreighton]

I would use Power Query for this:
Convert your table to an official table (hit Ctrl t and indicate you have headers).
While in the table, click Data > From Table/Range to open PQ (your table should appear).
Select the 1st two columns (select heading of 1st and hold Ctrl while clicking 2nd heading).
From top menu, click Transform > Unpivot Columns > Unpivot Other Columns (produces a 4-column table).
Rename headings (double click to do so), drag columns to desired positions (drag headings) and then filter out 0's of Number column (using drop down filter arrow in heading).
Finally, Home > Close and Load to load back into workbook.

MrExcel_20220831.xlsx
	A	B	C	D
1	Date	COMBINED ALL TRANSACTS British Common	Scientific	Number
2	10/2/2019	Brent Goose	Branta bernicla	81
3	10/22/2019	Brent Goose	Branta bernicla	140
4	11/7/2019	Brent Goose	Branta bernicla	3
5	11/14/2019	Brent Goose	Branta bernicla	200
6	11/20/2019	Brent Goose	Branta bernicla	44
7	12/4/2019	Brent Goose	Branta bernicla	43
8	12/18/2019	Brent Goose	Branta bernicla	11
9	1/1/2020	Brent Goose	Branta bernicla	42
10	1/15/2020	Brent Goose	Branta bernicla	5
11	1/22/2020	Brent Goose	Branta bernicla	2
12	1/29/2020	Brent Goose	Branta bernicla	7
13	2/5/2020	Brent Goose	Branta bernicla	7
14	2/12/2020	Brent Goose	Branta bernicla	210
15	2/19/2020	Brent Goose	Branta bernicla	2
16	10/2/2019	Canada Goose	Branta canadensis	25
17	10/16/2019	Canada Goose	Branta canadensis	360
18	10/29/2019	Canada Goose	Branta canadensis	20
Table1

Thanks - I've not really played with power queries. I'll give it a go.

 

[MCreighton]

Never too late

How about

Fluff.xlsm
	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
1	COMBINED ALL TRANSACTS British Common	Scientific	02/10/2019	08/10/2019	16/10/2019	22/10/2019	29/10/2019	03/11/2019	07/11/2019	14/11/2019	20/11/2019	27/11/2019	04/12/2019	11/12/2019	18/12/2019	01/01/2020	08/01/2020	15/01/2020	22/01/2020	29/01/2020	05/02/2020	12/02/2020	19/02/2020	26/02/2020	04/03/2020
2	Brent Goose	Branta bernicla	81	0	0	140	0	0	3	200	44	0	43	0	11	42	0	5	2	7	7	210	2	0	0
3	Dark-bellied Brent Goose	Branta bernicla bernicla	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	Pale-bellied Brent Goose	Branta bernicla hrota	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	Black Brant	Branta bernicla nigricans	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Red-breasted Goose	Branta ruficollis	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	Canada Goose	Branta canadensis	25	0	360	0	20	0	0	0	0	2	0	12	0	0	0	0	0	5	2	0	7	0	8
8	Barnacle Goose	Branta leucopsis	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	Cackling Goose	Branta hutchinsii	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	Snow Goose	Anser caerulescens	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	Greylag Goose	Anser anser	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	Taiga Bean Goose	Anser fabalis	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	Pink-footed Goose	Anser brachyrhynchus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	Tundra Bean Goose	Anser serrirostris	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	European White-fronted Goose	Anser albifrons	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	Greenland White-fronted Goose	Anser albifrons flavirostris	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	Lesser White-fronted Goose	Anser erythropus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	Mute Swan	Cygnus olor	2	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	Bewick's Swan	Cygnus columbianus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	Whooper Swan	Cygnus cygnus	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	Egyptian Goose	Alopochen aegyptiaca	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	Shelduck	Tadorna tadorna	0	0	0	0	0	0	0	0	0	0	0	2	0	6	2	1	0	3	2	1	4	4	12
23	Ruddy Shelduck	Tadorna ferruginea	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24	Mandarin Duck	Aix galericulata	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
25	Baikal Teal	Sibirionetta formosa	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
26	Garganey	Spatula querquedula	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27	Blue-winged Teal	Spatula discors	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
28	Shoveler	Spatula clypeata	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
29	Gadwall	Mareca strepera	0	4	0	2	0	2	0	0	34	3	0	0	1	0	0	0	0	0	0	0	0	0	11
30	Falcated Duck	Mareca falcata	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
31	Wigeon	Mareca penelope	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	10	0	0	0
32	American Wigeon	Mareca americana	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
33	Mallard	Anas platyrhynchos	6	14	0	4	18	5	0	3	9	21	8	2	8	2	13	3	3	7	7	0	0	0	3
34	Black Duck	Anas rubripes	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
35	Pintail	Anas acuta	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
36	Teal	Anas crecca	2	37	0	1	6	0	0	19	0	3	15	0	9	25	11	1	0	20	12	12	10	0	47
37
38
39
40	02/10/2019	Brent Goose	Branta bernicla	81
41	02/10/2019	Canada Goose	Branta canadensis	25
42	02/10/2019	Mute Swan	Cygnus olor	2
43	02/10/2019	Mallard	Anas platyrhynchos	6
44	02/10/2019	Teal	Anas crecca	2
45	08/10/2019	Gadwall	Mareca strepera	4
46	08/10/2019	Mallard	Anas platyrhynchos	14
47	08/10/2019	Teal	Anas crecca	37
48	16/10/2019	Canada Goose	Branta canadensis	360
49	22/10/2019	Brent Goose	Branta bernicla	140
50	22/10/2019	Mute Swan	Cygnus olor	1
51	22/10/2019	Gadwall	Mareca strepera	2
52	22/10/2019	Mallard	Anas platyrhynchos	4
53	22/10/2019	Teal	Anas crecca	1
54	29/10/2019	Canada Goose	Branta canadensis	20
55	29/10/2019	Mallard	Anas platyrhynchos	18
56	29/10/2019	Teal	Anas crecca	6
57	03/11/2019	Gadwall	Mareca strepera	2
58	03/11/2019	Mallard	Anas platyrhynchos	5
59	07/11/2019	Brent Goose	Branta bernicla	3
60	14/11/2019	Brent Goose	Branta bernicla	200
61	14/11/2019	Mallard	Anas platyrhynchos	3
62	14/11/2019	Teal	Anas crecca	19
63	20/11/2019	Brent Goose	Branta bernicla	44
64	20/11/2019	Gadwall	Mareca strepera	34
65	20/11/2019	Mallard	Anas platyrhynchos	9
66	27/11/2019	Canada Goose	Branta canadensis	2
67	27/11/2019	Gadwall	Mareca strepera	3
68	27/11/2019	Mallard	Anas platyrhynchos	21
69	27/11/2019	Teal	Anas crecca	3
70	04/12/2019	Brent Goose	Branta bernicla	43
71	04/12/2019	Mallard	Anas platyrhynchos	8
72	04/12/2019	Teal	Anas crecca	15
73	11/12/2019	Canada Goose	Branta canadensis	12
74	11/12/2019	Shelduck	Tadorna tadorna	2
75	11/12/2019	Mallard	Anas platyrhynchos	2
76	18/12/2019	Brent Goose	Branta bernicla	11
77	18/12/2019	Gadwall	Mareca strepera	1
78	18/12/2019	Mallard	Anas platyrhynchos	8
79	18/12/2019	Teal	Anas crecca	9
80	01/01/2020	Brent Goose	Branta bernicla	42
81	01/01/2020	Shelduck	Tadorna tadorna	6
82	01/01/2020	Mallard	Anas platyrhynchos	2
83	01/01/2020	Teal	Anas crecca	25
84	08/01/2020	Shelduck	Tadorna tadorna	2
85	08/01/2020	Mallard	Anas platyrhynchos	13
86	08/01/2020	Teal	Anas crecca	11
87	15/01/2020	Brent Goose	Branta bernicla	5
88	15/01/2020	Shelduck	Tadorna tadorna	1
89	15/01/2020	Mallard	Anas platyrhynchos	3
90	15/01/2020	Teal	Anas crecca	1
91	22/01/2020	Brent Goose	Branta bernicla	2
92	22/01/2020	Mallard	Anas platyrhynchos	3
93	29/01/2020	Brent Goose	Branta bernicla	7
94	29/01/2020	Canada Goose	Branta canadensis	5
95	29/01/2020	Shelduck	Tadorna tadorna	3
96	29/01/2020	Mallard	Anas platyrhynchos	7
97	29/01/2020	Teal	Anas crecca	20
98	05/02/2020	Brent Goose	Branta bernicla	7
99	05/02/2020	Canada Goose	Branta canadensis	2
100	05/02/2020	Shelduck	Tadorna tadorna	2
101	05/02/2020	Wigeon	Mareca penelope	1
102	05/02/2020	Mallard	Anas platyrhynchos	7
103	05/02/2020	Teal	Anas crecca	12
104	12/02/2020	Brent Goose	Branta bernicla	210
105	12/02/2020	Shelduck	Tadorna tadorna	1
106	12/02/2020	Wigeon	Mareca penelope	10
107	12/02/2020	Teal	Anas crecca	12
108	19/02/2020	Brent Goose	Branta bernicla	2
109	19/02/2020	Canada Goose	Branta canadensis	7
110	19/02/2020	Shelduck	Tadorna tadorna	4
111	19/02/2020	Teal	Anas crecca	10
112	26/02/2020	Shelduck	Tadorna tadorna	4
113	04/03/2020	Canada Goose	Branta canadensis	8
114	04/03/2020	Shelduck	Tadorna tadorna	12
115	04/03/2020	Gadwall	Mareca strepera	11
116	04/03/2020	Mallard	Anas platyrhynchos	3
117	04/03/2020	Teal	Anas crecca	47
Data

Cell Formulas
Range	Formula
A40:D117	A40	=LET(Hdr,C1:Y1,Data,A2:B36,nums,C2:Y36,c,COLUMNS(Hdr),s,SEQUENCE(ROWS(Data)*c,,0),xa,INDEX(Hdr,MOD(s,c)+1),xb,INDEX(Data,INT(s/c)+1,{1,2}),xc,INDEX(nums,INT(s/c)+1,MOD(s,c)+1),SORT(FILTER(CHOOSE({1,2,3,4},xa,INDEX(xb,,1),INDEX(xb,,2),xc),xc>0)))
Dynamic array formulas.

That is marvellous and works perfectly. I will now have to study it in detail to see what it's doing so I can learn this technique for myself. Many, many thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.

If you have received the new functions such as HSTACK the formula can be simplified slightly to

=LET(Hdr,C1:Y1,Data,A2:B36,nums,C2:Y36,c,COLUMNS(Hdr),s,SEQUENCE(ROWS(Data)*c,,0),xa,INDEX(Hdr,MOD(s,c)+1),xb,INDEX(Data,INT(s/c)+1,{1,2}),xc,INDEX(nums,INT(s/c)+1,MOD(s,c)+1),SORT(FILTER(HSTACK(xa,xb,xc),xc>0)))