# Table sorted by match and frequency



## ilegall (Jan 5, 2023)

Hi,

would like to ask for help on this. I need to sort the table (new column with both new and theoretically repetitive tags added weekly) by match and frequency. Any idea how to do that?

Example:
first line - tag in row matches, most frequent amongst all columns
second line - tag in row matches, 2nd most frequent...

More advanced would then be showing also the frequency count of each tag row.

Note that the list provided weekly is randomly sorted and the function to sort manually every week needs to be kept.

Thank you very much for the help!

_-ilegall_


----------



## Eric W (Jan 5, 2023)

Welcome to the MrExcel forum!

We need a bit more information.  Is that an actual Excel table, or is it just data formatted to look like a table?  Are there any other columns in it that don't contain "tags"?  (I assume a tag is any of the values in the table, like 2HS-141, which occurs in 3 columns, or 11-LSH 9005 which occurs in 2).

It sounds like you want to create a list of unique tags, sorted by frequency.  So 2HS-141 would be ahead of 11-LSH 9005.  Do you want this list as a column in the table, or somewhere else?  Do you want each individual column sorted?  By the list to-date, or the complete list?

Are you looking for something that can be done with formulas, or with VBA (the Excel macro language)?

What version of Excel do you have?  It would be helpful if you could update you profile to reflect that.  Different versions have different options.

Also, since you're new, I'd like to suggest you look into the XL2BB tool (see a link in my signature or the reply box).  This allows you to display your sample data in such a way that the helpers here can copy and paste your data.  Displaying a picture like you did requires us to retype everything, and some people won't take the time to do that.


Here's one possibility if you have Excel 365:

Book1ABCDEFGH110/20/202212/22/202212/29/20221/5/2023TagFrequency211-LSH 900511-LSH 90059-PI-7123C3PIC-68302HS-1414311-UA-91013UA-83022HS-1414TI-2-393GO-2007A442HS-141UA3120-23GO-2007AUA3120-23PIC-6830453GO-2007A4UA-35266IL501-13SI-3535B3TI-8939463HS-6043A4LSL-80213PIC-68309-PI-7123C4FSAH9125473JI-6197B3PIC-6830UA3120-23LI-7345UA3120-2483PIC-68303TI-8939F20153TI-89396FR1012493QAH-9112DEV4UA-101K101 HP PIANO3GO-2007A3UA-83023103QI-1850-29-PI-7123C3TI-89394UA-35269-PI-7123C3113QI-2140F20154FSAH912511-TIC 966111-LSH 90052123QZAH-21403GO-2007A6FR101209TAH-39144TI-2-392133TI-89396FR10123UA-83024FSAH9125ZL37112144FSAH91252HS-14106HS-31303UA-83024UA-35262154PDI-34613SI-3535B11-TIC 966106HS-3130F20152164TI-2-394UA-9920ZL37112HS-1413SI-3535B217AI7602B3FY-6903B02QI-25106HS-3130218HS-6075A4FSAH91254PDI-352511-TIC 9661219PDI-6833A6FR101211-UA-9101120UA3120-29-HS-7120A-23HS-6043A121ZL37116FR10123JI-6197B1223QAH-9112DEV1233QI-1850-21243QI-21401253QZAH-21401264PDI-3461127AI7602B128HS-6075A129PDI-6833A1304LSL-80211314UA-1011324UA-99201333FY-6903B1346IL501-1135K101 HP PIANO1363LI-734513709TAH-391413802QI-2511394PDI-35251409-HS-7120A-21Sheet6Cell FormulasRangeFormulaG2:H40G2=LET(t,Table1[#Data],r,ROWS(t),c,COLUMNS(t),s,SEQUENCE(r*c,,0),a,INDEX(t,MOD(s,r)+1,INT(s/r)+1),b,UNIQUE(FILTER(a,a<>"")),ci,COUNTIF(t,b),CHOOSE({1,2},SORTBY(b,ci,-1),SORT(ci,,-1)))Dynamic array formulas.

I believe that formula can be shortened to


```
=LET(t,Table1[#Data],a,UNIQUE(TOCOL(t,1)),ci,COUNTIF(t,a),CHOOSE({1,2},SORTBY(a,ci,-1),SORT(ci,,-1)))
```


if you have the TOCOL function.


----------



## ilegall (Tuesday at 3:32 AM)

Hi Eric, thanks for the quick reply and warm welcome. 

Data are currently formatted as table, but this can be changed back to just set of data as requested for the actual data formatting formula/macro. So either script or formula solution works with me.

That is correct, you understand the problem very clearly, I want to create to a list of unique tags (remove duplicates if more than one occurrence in 1 column), sorted by frequency, BUT it is vital to include and track week of occurence (leave blank if missing that week) and make every column sorted to match this criteria as you suggested, so the table/set of data would look something like attached below via suggested xl2bb tool. Would that be managable?

Hope I explained myself clearly enough thanks a lot for the help!

_-ilegall

Tag data tracking idea.xlsxBCDEF24Frequency20/10/202222/12/202229/12/20225/1/20232542HS-1412HS-1412HS-1412HS-1412643GO-2007A3GO-2007A3GO-2007A3GO-2007A2743PIC-68303PIC-68303PIC-68303PIC-68302843TI-89393TI-89393TI-89393TI-89392944FSAH91254FSAH91254FSAH91254FSAH9125304UA3120-2UA3120-2UA3120-2UA3120-23136FR10126FR10126FR10123233UA-83023UA-83023UA-83023339-PI-7123C9-PI-7123C9-PI-7123C34211-LSH 900511-LSH 90053524TI-2-394TI-2-39362ZL3711ZL3711Active Alarms_


----------



## Eric W (Yesterday at 10:29 PM)

I'm sure this could be cleaner, but it seems to do the job.  Open a copy of your workbook.  Press Alt-F11 to open the VBA editor.  Press Alt-IM to Insert a Module.  Paste the following code into the window that opens:


```
Sub SortTags()
Dim MyTags As Range, Results As Range, dict As Object, mr As Long, mc As Long
Dim c As Long, r As Long, i As Long, ColData() As Variant, MyData As Variant
Dim keyz As Variant, itemz As Variant, Table1() As Variant, Table2() As Variant

    Set MyTags = Sheets("Sheet1").Range("A1")
    Set Results = Sheets("Sheet2").Range("A1")
    
    Set dict = CreateObject("Scripting.Dictionary")
    mr = 0
    mc = 0
    While MyTags.Offset(0, mc) <> ""
        mr = WorksheetFunction.Max(mr, MyTags.Offset(Rows.Count - MyTags.Row, mc).End(xlUp).Row)
        mc = mc + 1
    Wend

    MyData = MyTags.Resize(mr - MyTags.Row + 1, mc).Value
    ReDim ColData(1 To mc)
    
    For c = 1 To UBound(MyData, 2)
        Set ColData(c) = CreateObject("Scripting.Dictionary")
        For r = 2 To UBound(MyData)
            If MyData(r, c) <> "" Then
                dict(MyData(r, c)) = dict(MyData(r, c)) + 1
                ColData(c)(MyData(r, c)) = ColData(c)(MyData(r, c)) + 1
            End If
        Next r
    Next c
    
    keyz = dict.keys
    itemz = dict.items
    ReDim Table1(1 To dict.Count, 1 To 2)
    For i = 1 To dict.Count
        Table1(i, 1) = keyz(i - 1)
        Table1(i, 2) = itemz(i - 1)
    Next i
    
    Table1 = WorksheetFunction.Sort(Table1, Array(2, 1), Array(-1, 1))
    
    ReDim Table2(0 To dict.Count, 0 To mc)
    Table2(0, 0) = "Frequency"
    For r = 1 To dict.Count
        Table2(r, 0) = Table1(r, 2)
    Next r
    For c = 1 To mc
        Table2(0, c) = MyData(1, c)
        For r = 1 To UBound(Table2)
            If ColData(c).exists(Table1(r, 1)) Then Table2(r, c) = Table1(r, 1)
        Next r
    Next c
    
    Results.Resize(UBound(Table2) + 1, UBound(Table2, 2) + 1) = Table2
        
    
End Sub
```

On the first 2 executable lines ("Set MyTags = " and "Set Results = "), change them to be the upper left corner of the input and output areas.  On the input it searches as far right and down until it finds an empty cell.  You can write to the same place if you want, but you'll have to allow for the inserted column of frequencies.

Once that's done, go back to Excel.  Press Alt-F8 to open the macro selector, select SortTags and click Run.  Given the input from post #2, this is what the macro created:

Book1ABCDE1Frequency10/20/202212/22/202212/29/20221/5/2023242HS-1412HS-1412HS-1412HS-141343GO-2007A3GO-2007A3GO-2007A3GO-2007A443PIC-68303PIC-68303PIC-68303PIC-6830543TI-89393TI-89393TI-89393TI-8939644FSAH91254FSAH91254FSAH91254FSAH9125746FR10126FR10126FR101284UA3120-2UA3120-2UA3120-2UA3120-2933UA-83023UA-83023UA-83021039-PI-7123C9-PI-7123C9-PI-7123C11206HS-313006HS-313012211-LSH 900511-LSH 900513211-TIC 966111-TIC 96611423SI-3535B3SI-3535B1524TI-2-394TI-2-391624UA-35264UA-3526172F2015F2015182ZL3711ZL371119102QI-25120109TAH-391421111-UA-91012213FY-6903B2313HS-6043A2413JI-6197B2513LI-73452613QAH-9112DEV2713QI-1850-22813QI-21402913QZAH-21403014LSL-80213114PDI-34613214PDI-35253314UA-1013414UA-99203516IL501-13619-HS-7120A-2371AI7602B381HS-6075A391K101 HP PIANO401PDI-6833ASheet2

Let me know what you think.


----------



## Peter_SSs (Yesterday at 11:56 PM)

As a formula approach, would this be headed in the right direction?

ilegall.xlsmABCDEFGHIJK110/20/202212/22/202212/29/20221/05/2023211-LSH 900511-LSH 90059-PI-7123C3PIC-68302HS-14142HS-1412HS-1412HS-1412HS-141311-UA-91013UA-83022HS-1414TI-2-393GO-2007A43GO-2007A3GO-2007A3GO-2007A3GO-2007A42HS-141UA3120-23GO-2007AUA3120-23PIC-683043PIC-68303PIC-68303PIC-68303PIC-683053GO-2007A4UA-35266IL501-13SI-3535B3TI-893943TI-89393TI-89393TI-89393TI-893963HS-6043A4LSL-80213PIC-68309-PI-7123C4FSAH912544FSAH91254FSAH91254FSAH91254FSAH912573JI-6197B3PIC-6830UA3120-23LI-73456FR101246FR10126FR10126FR101283PIC-68303TI-8939F20153TI-8939UA3120-24UA3120-2UA3120-2UA3120-2UA3120-293QAH-9112DEV4UA-101K101 HP PIANO3GO-2007A3UA-830233UA-83023UA-83023UA-8302103QI-1850-29-PI-7123C3TI-89394UA-35269-PI-7123C39-PI-7123C9-PI-7123C9-PI-7123C113QI-2140F20154FSAH912511-TIC 966106HS-3130206HS-313006HS-3130123QZAH-21403GO-2007A6FR101209TAH-391411-LSH 9005211-LSH 900511-LSH 9005133TI-89396FR10123UA-83024FSAH912511-TIC 9661211-TIC 966111-TIC 9661144FSAH91252HS-14106HS-31303UA-83023SI-3535B23SI-3535B3SI-3535B154PDI-34613SI-3535B11-TIC 966106HS-31304TI-2-3924TI-2-394TI-2-39164TI-2-394UA-9920ZL37112HS-1414UA-352624UA-35264UA-352617AI7602B3FY-6903B02QI-251F20152F2015F201518HS-6075A4FSAH91254PDI-3525ZL37112ZL3711ZL371119PDI-6833A6FR101202QI-251102QI-25120UA3120-29-HS-7120A-209TAH-3914109TAH-391421ZL37116FR101211-UA-9101111-UA-9101223FY-6903B13FY-6903B233HS-6043A13HS-6043A243JI-6197B13JI-6197B253LI-734513LI-7345263QAH-9112DEV13QAH-9112DEV273QI-1850-213QI-1850-2283QI-214013QI-2140293QZAH-214013QZAH-2140304LSL-802114LSL-8021314PDI-346114PDI-3461324PDI-352514PDI-3525334UA-10114UA-101344UA-992014UA-9920356IL501-116IL501-1369-HS-7120A-219-HS-7120A-237AI7602B1AI7602B38HS-6075A1HS-6075A39K101 HP PIANO1K101 HP PIANO40PDI-6833A1PDI-6833ASheet1Cell FormulasRangeFormulaF2:G40F2=LET(t,UNIQUE(TOCOL(Table1,1)),SORT(SORT(CHOOSE({1,2},t,COUNTIF(Table1,t)),1),2,-1))H2:K40H2=LET(v,INDEX($F2#,0,1),IF(ISNUMBER(MATCH(v,INDEX(Table1,0,COLUMNS($H:H)),0)),v,""))Dynamic array formulas.


----------

