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

J

JennyReed

New Member
Joined
Feb 8, 2011
Messages
23
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>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Try this:-
Results start "F1".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG11Apr55
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Str         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, -1)
Str = Dn
[COLOR="Navy"]If[/COLOR] Not Str = "" [COLOR="Navy"]Then[/COLOR] Temp = Dn
    [COLOR="Navy"]If[/COLOR] Dn = "" And Dn.Offset(, 1) > "" [COLOR="Navy"]Then[/COLOR] Str = Temp
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) > "" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not .Exists(Str) [COLOR="Navy"]Then[/COLOR]
                .Add Str, Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] .Item(Str) = Union(.Item(Str), Dn)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] .Item(K)
      [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, 3) = "" [COLOR="Navy"]Then[/COLOR]
         Str = IIf(K = Str, "", K)
         c = c + 1
         Cells(c, 6) = Str
            [COLOR="Navy"]For[/COLOR] n = 7 To 10
                Cells(c, n) = Dn.Offset(, n - 6).Value
            [COLOR="Navy"]Next[/COLOR] n
      [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] K


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick

Thank you very much for that. Definitely beyond my excel knowledge but it works.
I have one question though. When the macro is run, if there are three species (art) within one taxon, then the macro 'writes' out the first row with taxon name and then species name, removes the taxon name from row two (still writes out the species name) but then for row three, both the taxon name and species name are written out again. For example, with the data below, the table writes out the taxon name (Subclass Octocorallia (phylum Cnidaria)) twice :eek:n row 1 and row three, Is there a way to write out the taxon name only once ?

Thank you very much for your help. Regards, Jenny

Data
TAXON NAME
ArtNSI 2013Antall individ
Phylum FORAMINIFERAForaminifera indet5
Phylum PORIFERAPorifera indet35.636
Class ANTHOZOA (phylum Cnidaria)Anthozoa indet28.2212
Class HYDROZOA (phylum Cnidaria)Hydrozoa indet25.9215
Subclass OCTOCORALLIA (phylum Cnidaria)Alcyonium digitatum
Anthelia fallax13
Anthomastus grandiflorus
Anthothela grandiflora
Capnella glomerata4
Clavularia arctica
Clavularia borealis
Duva florida8
Funiculina quadranangularis
Gersemia fruticosa
Subclass HEXACORALLIA (phylum Cnidaria)Actinauge richardi
Phylum PLATYHELMINTHESPlatyhelminthes indet26.816
Phylum NEMERTEAAmphiporus angulatus

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


Macro table
TAXON NAMEArtNSI 2013Antall individ
Phylum FORAMINIFERAForaminifera indet5
Phylum PORIFERAPorifera indet35.636
Class ANTHOZOA (phylum Cnidaria)Anthozoa indet28.2212
Class HYDROZOA (phylum Cnidaria)Hydrozoa indet25.9215
Subclass OCTOCORALLIA (phylum Cnidaria)
Anthelia fallax13
Capnella glomerata4
Subclass OCTOCORALLIA (phylum Cnidaria)
Duva florida8
Phylum PLATYHELMINTHESPlatyhelminthes indet26.816

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

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


MickG said:
Try this:-
Results start "F1".
Code: 
[COLOR=Navy]Sub[/COLOR] MG11Apr55
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] n           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Temp        [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] Str         [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] K
[COLOR=Navy]Dim[/COLOR] c           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
    [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Offset(, -1)
Str = Dn
[COLOR=Navy]If[/COLOR] Not Str = "" [COLOR=Navy]Then[/COLOR] Temp = Dn
    [COLOR=Navy]If[/COLOR] Dn = "" And Dn.Offset(, 1) > "" [COLOR=Navy]Then[/COLOR] Str = Temp
        [COLOR=Navy]If[/COLOR] Dn.Offset(, 1) > "" [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]If[/COLOR] Not .Exists(Str) [COLOR=Navy]Then[/COLOR]
                .Add Str, Dn
            [COLOR=Navy]Else[/COLOR]
                [COLOR=Navy]Set[/COLOR] .Item(Str) = Union(.Item(Str), Dn)
            [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]


[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
   [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] .Item(K)
      [COLOR=Navy]If[/COLOR] Not Dn.Offset(, 3) = "" [COLOR=Navy]Then[/COLOR]
         Str = IIf(K = Str, "", K)
         c = c + 1
         Cells(c, 6) = Str
            [COLOR=Navy]For[/COLOR] n = 7 To 10
                Cells(c, n) = Dn.Offset(, n - 6).Value
            [COLOR=Navy]Next[/COLOR] n
      [COLOR=Navy]End[/COLOR] If
   [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]Next[/COLOR] K


[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...
 
Upvote 0
Jenny

A couple of other options to consider.

1. A formula approach as follows.

In all the formulas below, wherever I have 2000 that number should be at least as big as the total number of rows of data you will ever have.

F1 is stand-alone formula.
F2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down as far as you might ever need. Column F could then be hidden if you want.
G2 is copied down to the same row column F was copied to.
H2 is copied across and down as far as the other columns were.

Excel Workbook
ABCDEFGHIJ
1TAXON NAMEArtNSI 2013Antall individ8TAXON NAMEArtNSI 2013Antall individ
2Phylum FORAMINIFERAForaminifera indet51Phylum FORAMINIFERAForaminifera indet5
3Phylum PORIFERAPorifera indet35.6362Phylum PORIFERAPorifera indet35.636
4Class ANTHOZOA (phylum Cnidaria)Anthozoa indet28.22123Class ANTHOZOA (phylum Cnidaria)Anthozoa indet28.2212
5Class HYDROZOA (phylum Cnidaria)Hydrozoa indet25.92154Class HYDROZOA (phylum Cnidaria)Hydrozoa indet25.9215
6Subclass OCTOCORALLIA (phylum Cnidaria)Alcyonium digitatum6Subclass OCTOCORALLIA (phylum Cnidaria)Anthelia fallax13
7Anthelia fallax139Capnella glomerata4
8Anthomastus grandiflorus12Duva florida8
9Anthothela grandiflora16Phylum PLATYHELMINTHESPlatyhelminthes indet26.816
10Capnella glomerata4
11Clavularia arctica
12Clavularia borealis
13Duva florida8
14Funiculina quadranangularis
15Gersemia fruticosa
16Subclass HEXACORALLIA (phylum Cnidaria)Actinauge richardi
17Phylum PLATYHELMINTHESPlatyhelminthes indet26.816
18Phylum NEMERTEAAmphiporus angulatus
19
JennyReed 2nd Data (Formula)





2. Alternate vba approach.
This assumes that you do not have formulas in column A that you don't want over-ridden.
Rich (BB code): 
Sub MakeTable()
  Dim a
  Dim rCrit As Range
  
  Set rCrit = Range("E1:E2")
  With Range("A1:A" & Range("D" & Rows.Count).End(xlUp).Row)
    a = .Value
    On Error Resume Next
    .SpecialCells(xlBlanks).FormulaR1C1 = _
      "=IF(SUMPRODUCT(--(R2C:R[-1]C=LOOKUP(REPT(""Z"",255),R2C:R[-1]C))," _
      & "--(R2C[3]:R[-1]C[3]>0))=0,LOOKUP(REPT(""Z"",255),R2C:R[-1]C),"""")"
    On Error GoTo 0
    rCrit.Cells(2).FormulaR1C1 = "=RC[-1]>0"
    .Resize(, 4).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, _
      CopyToRange:=rCrit.Cells(1).Offset(, 1), Unique:=False
    rCrit.ClearContents
    .Value = a
  End With
End Sub
 
Last edited:
Upvote 0
Glad to help. Thanks for letting us know.
 
Upvote 0
Hi Peter_SSs
I was wondering how to extend your formula approach to include more columns.
The data is the same as above except that instead of just 1 column of antall individ, there could be up to 6 columns of antall individ (i.e. nr. individuals in a sample).
Your formulas above search the column of antall individ and if there was a value there, then wrote out that row of data (while ensuring that the taxon name was also listed).

I tried to extend your formulas to include extra columns of 'antall individ' as most of then there will be up to 6 samples taken at a site and therefore 6 columns of information (antall individ) that the final table needs to be based on.

Is it possible to extend your formulas to search all 6 columns of 'antall individ' and write out the rows of information if ANY of the 6 columns has data in ?
In other words, it is not necessary for all columns to have data in to write out the row but if any of the columns have data in, then the row should be included in the final output table.

Thank you in advance :)
Jenny
 
Upvote 0
A small set of sample data and expected results that demonstrates the extra requirements might help clarify.
 
Upvote 0
Re: Sorting to include col 1 text while sorting according to col 4, %, 6, 7 etc.

Hi,

Yes, here is a sample of the data and the results required:
TAXON NAMESArtISI 2012NSI 2013# G1# G2#G3#G4#G5#G6
Phylum FORAMINIFERAForaminifera indet2118
Phylum PORIFERAPorifera indet23.4935.6320
Class ANTHOZOAAnthozoa indet8.0928.22134
Class HYDROZOA (phylum Cnidaria)Hydrozoa indet10.0925.9225
Subclass OCTOCORALLIA (phylum Cnidaria)Alcyonium digitatum5
Anthelia fallax8
Anthomastus grandiflorus5
Anthothela grandiflora
Capnella glomerata55
Clavularia arctica66
Clavularia borealis77
Duva florida9
Funiculina quadranangularis
Gersemia fruticosa
Gersemia rubiformis
Halipteris cristii
Haipteris finmarchica2
Isidella lofotensis
Kophobelemnon stelliferum15.9625.949
Muriceides kuekenthali
Paragorgia arborea2
Paramuricea placomus
Pennatula aculeata
Pennatula grandis
Pennatula phosphorea20.8228.52
Primnoa resedaeformis
Protoptilium thomsoni
Sarcodictyon roseum
Stylatula elegans16.0821.35
Swiftia pallida
Swiftia rosea
Virgularia glacialis5
Virgularia mirabilis14.3726.64
Virgularia tuberculata1
Subclass HEXACORALLIA (phylum Cnidaria)Actinauge richardi
Actiniaria indet18.9832.17

<tbody>
</tbody>



















































































With all rows where there is no data removed, the results table would look like the following:
(The rows are sorted accoring to columns nr 5 - nr 10. Where there is any data in these columns, the row is included in the output table. Only rows with no data in cols 5 - 10 are removed from the table.). Thank you for your help. Jenny

TAXON NAMESArtISI 2012NSI 2013# G1# G2#G3#G4#G5#G6
Phylum FORAMINIFERAForaminifera indet2118
Phylum PORIFERAPorifera indet23.4935.6320
Class ANTHOZOAAnthozoa indet8.0928.22134
Class HYDROZOA (phylum Cnidaria)Hydrozoa indet10.0925.9225
Subclass OCTOCORALLIA (phylum Cnidaria)Alcyonium digitatum5
Anthelia fallax8
Anthomastus grandiflorus5
Capnella glomerata55
Clavularia arctica66
Clavularia borealis77
Duva florida9
Haipteris finmarchica2
Kophobelemnon stelliferum15.9625.949
Paragorgia arborea2
Pennatula phosphorea20.8228.52
Stylatula elegans16.0821.35
Virgularia glacialis5
Virgularia mirabilis14.3726.64
Virgularia tuberculata1
Subclass HEXACORALLIA (phylum Cnidaria)Actiniaria indet18.9832.17

<tbody>
</tbody>
 
Upvote 0
JennyReed said:
Is it possible to extend your formulas to search all 6 columns of 'antall individ' and write out the rows of information if ANY of the 6 columns has data in ?
Click to expand...
Jenny

Your description and sample expected results don't seem to agree to me. You repeatedly refer to 6 columns of 'antall individ' & say if any of those have something in them then include that row in the results. I can only assume that those 6 columns are E:J in my screen shot below & therefore wonder why you have included the coloured rows in your expected results when the 6 columns (blue cells) contain no data.***

Excel Workbook
BCDEFGHIJK
1ArtISI 2012NSI 2013# G1# G2#G3#G4#G5#G6
2
3Foraminifera indet2118
4
5Porifera indet23.4935.6320
6
7Anthozoa indet8.0928.22134
8
9Hydrozoa indet10.0925.9225
10
11Alcyonium digitatum5
12Anthelia fallax8
13Anthomastus grandiflorus5
14Anthothela grandiflora
15Capnella glomerata55
16Clavularia arctica66
17Clavularia borealis77
18Duva florida9
19Funiculina quadranangularis
20Gersemia fruticosa
21Gersemia rubiformis
22Halipteris cristii
23Haipteris finmarchica2
24Isidella lofotensis
25Kophobelemnon stelliferum15.9625.949
26Muriceides kuekenthali
27Paragorgia arborea2
28Paramuricea placomus
29Pennatula aculeata
30Pennatula grandis
31Pennatula phosphorea20.8228.52
32Primnoa resedaeformis
33Protoptilium thomsoni
34Sarcodictyon roseum
35Stylatula elegans16.0821.35
36Swiftia pallida
37Swiftia rosea
38Virgularia glacialis5
39Virgularia mirabilis14.3726.64
40Virgularia tuberculata1
41
42Actinauge richardi
43Actiniaria indet18.9832.17
JennyReed 3rd Data (Formula)




Anyway, try this which uses an extra helper column but has simpler formulas. (Helper columns L:M could be hidden once formulas have been added)

M1 is a stand-alone formula.
L2:M2 formulas are copied down to the end of the data.
N2 formula is copied across to W2 and down to the end of the data.

Excel Workbook
LMNOPQRSTUVW
116TAXON NAMEArtISI 2012NSI 2013# G1# G2#G3#G4#G5#G6
2 2Phylum FORAMINIFERAForaminifera indet2118
324Phylum PORIFERAPorifera indet23.4935.6320
46Class ANTHOZOAAnthozoa indet8.0928.22134
548Class HYDROZOA (phylum Cnidaria)Hydrozoa indet10.0925.9225
610Subclass OCTOCORALLIA (phylum Cnidaria)Alcyonium digitatum5
7611Anthelia fallax8
812Anthomastus grandiflorus5
9814Capnella glomerata55
1015Clavularia arctica66
111016Clavularia borealis77
121117Duva florida9
131222Haipteris finmarchica2
1424Kophobelemnon stelliferum15.9625.949
151426Paragorgia arborea2
161537Virgularia glacialis5
171639Virgularia tuberculata1
1817
19
20
21
22
2322
24
2524
26
2726
28
JennyReed 3rd Data (Formula)



*** If you do want those coloured rows in the result (that is any row with values in any of columns C:J) then just change the L2 formula to

=IF(COUNT(C2:J2),ROWS(L$2:L2),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Sort and filter on a protected sheet
Replies
7
Views
249
Oryon
O
D
  • Question
Email Pivot Table to Distribution list changing Pivot Table Filter
Replies
2
Views
283
DarkNight
D
V
  • Question
Sort/filter/index formula...
Replies
0
Views
134
valmir
V
J
  • Solved
Sorting occurrences of Postal Code in column / Sorting by occurrence high to low
Replies
7
Views
156
Fluff
Fluff
M
  • Solved
Filter Row of desire number + 1 row below it.
Replies
6
Views
111
MARK858
MARK858

Forum statistics

Threads
1,215,343
Messages
6,124,394
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top