Sorting to include col 1 text while sorting according to col 4

[JennyReed]

Hi.
I have a table of data something like:

Taxon Species Index No. individuals
taxon1 species1 35.63 15

taxon2 species2 28.22 7

taxon3 species3 25.94
species4 28.52 6
species5 21.35
species6 26.64 18

taxon4 species7 22.94 26

I would like to generate a table according to No. Individuals where all the blanks are excluded - i.e. only the rows with data in are included. At the same time, I would like the taxon name to included in the final table. With the data above, if I filter according to No. Individuals to exclude blanks, I would generate a table that looks like:

taxon1 species1 35.63 15
taxon2 species2 28.22 7
species4 28.52 6
species6 26.64 18
taxon4 species7 22.94 26
In other words, the third and fourth rows in the 'filtered' table do not have the taxon3 name.

I would like the table to look like:
taxon1 species1 35.63 15
taxon2 species2 28.22 7
taxon3 species4 28.52 6
species6 26.64 18
taxon4 species7 22.94 26

I know that I can copy the taxon name for every row of data but I am hoping to avoid this as I would like the taxon name to appear only once in column 1. I am hoping to avoid the table looking like:

taxon1 species1 35.63 15
taxon2 species2 28.22 7
taxon3 species4 28.52 6
taxon3 species6 26.64 18
taxon4 species7 22.94 26
Where 'taxon3' is repeated twice.

Any help would be greatly appreciated.
Jenny

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[JennyReed]

Hi Peter

Thank you very much. That works.
You were completely correct, I was not after the rows that you had marked in blue.
Sorry about that.

Kind regards,
Jenny

 

[JennyReed]

Hi Peter

I have one adjustment for the output table: the taxon name (col A) to be listed in the table for the first occurrence of an 'art':
For example, for the data:

TAXON NAMES	Art	ISI 2012	NSI 2013	# G1	# G2	#G3	#G4	#G5	#G6
Phylum FORAMINIFERA	Foraminifera indet
Phylum PORIFERA	Porifera indet	23.49	35.63
Class ANTHOZOA	Anthozoa indet	8.09	28.22				4
Class HYDROZOA (phylum Cnidaria)	Hydrozoa indet	10.09	25.92					5
Subclass OCTOCORALLIA (phylum Cnidaria)	Alcyonium digitatum
	Anthelia fallax								6
	Anthomastus grandiflorus			1
	Anthothela grandiflora
Subclass HEXACORALLIA (phylum Cnidaria)	Actinauge richardi
	Actiniaria indet	18.98	32.17		2
	Actinia equina

<colgroup><col style="mso-width-source:userset;mso-width-alt:7460;width:153pt" width="204"> <col style="mso-width-source:userset;mso-width-alt:6619;width:136pt" width="181"> <col style="mso-width-source:userset;mso-width-alt:1316;width:27pt" width="36"> <col style="mso-width-source:userset;mso-width-alt:1462;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:877;width:18pt" width="24"> <col style="mso-width-source:userset;mso-width-alt:950;width:20pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:877; width:18pt" span="2" width="24"> <col style="mso-width-source:userset;mso-width-alt:841;width:17pt" width="23"> <col style="mso-width-source:userset;mso-width-alt:877;width:18pt" width="24"> </colgroup><tbody>
</tbody>

The output table with the current formulas looks like:

TAXON NAMES	Art	ISI 2012	NSI 2013	#G1	#G2	#G3	#G4	#G5	#G6
Class ANTHOZOA	Anthozoa indet	8.09	28.22				4
Class HYDROZOA (phylum Cnidaria)	Hydrozoa indet	10.09	25.92					5
	Anthelia fallax								6
	Anthomastus grandiflorus			1
	Actiniaria indet	18.98	32.17		2

<colgroup><col style="mso-width-source:userset;mso-width-alt:6290;width:129pt" width="172"> <col style="mso-width-source:userset;mso-width-alt:3840;width:79pt" width="105"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> <col style="mso-width-source:userset;mso-width-alt:1828;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:1097;width:23pt" width="30"> <col style="mso-width-source:userset;mso-width-alt:1170;width:24pt" width="32"> <col style="mso-width-source:userset;mso-width-alt:1060; width:22pt" span="2" width="29"> <col style="mso-width-source:userset;mso-width-alt:1024;width:21pt" width="28"> <col style="mso-width-source:userset;mso-width-alt:1060;width:22pt" width="29"> </colgroup><tbody>
</tbody>

Whereas, I would like it to look like:

TAXON NAMES	Art	ISI 2012	NSI 2013	#G1	#G2	#G3	#G4	#G5	#G6
Class ANTHOZOA	Anthozoa indet	8.09	28.22				4
Class HYDROZOA (phylum Cnidaria)	Hydrozoa indet	10.09	25.92					5
Subclass OCTOCORALLIA (phylum Cnidaria)	Anthelia fallax								6
	Anthomastus grandiflorus			1
Subclass HEXACORALLIA (phylum Cnidaria)	Actiniaria indet	18.98	32.17		2

<colgroup><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Thank you in advance.
Kind regards,
Jenny

 

[Peter_SSs]

Not entirely sure but try this.

Formula in L1 is a stand-alone formula (just moved from M1 in my previous layout)
M1 houses a 0
L2:N2 copied down
O2 copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	TAXON NAMES	Art	ISI 2012	NSI 2013	# G1	# G2	#G3	#G4	#G5	#G6		5	0	TAXON NAME	Art	ISI 2012	NSI 2013	# G1	# G2	#G3	#G4	#G5	#G6
2													6	Class ANTHOZOA	Anthozoa indet	8.09	28.22				4
3	Phylum FORAMINIFERA	Foraminifera indet											8	Class HYDROZOA (phylum Cnidaria)	Hydrozoa indet	10.09	25.92					5
4													11	Subclass OCTOCORALLIA (phylum Cnidaria)	Anthelia fallax								6
5	Phylum PORIFERA	Porifera indet	23.49	35.63									12		Anthomastus grandiflorus			1
6													16	Subclass HEXACORALLIA (phylum Cnidaria)	Actiniaria indet	18.98	32.17		2
7	Class ANTHOZOA	Anthozoa indet	8.09	28.22				4				6
8
9	Class HYDROZOA (phylum Cnidaria)	Hydrozoa indet	10.09	25.92					5			8
10
11	Subclass OCTOCORALLIA (phylum Cnidaria)	Alcyonium digitatum
12		Anthelia fallax								6		11
13		Anthomastus grandiflorus			1							12
14		Anthothela grandiflora
15
16	Subclass HEXACORALLIA (phylum Cnidaria)	Actinauge richardi
17		Actiniaria indet	18.98	32.17		2						16
18		Actinia equina
JennyReed 4

 

[JennyReed]

Hi Peter

Thank you ever so much. That works perfectly.

Kind regards,
Jenny