Table sorted by match and frequency

[ilegall]

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]

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:

Book1
	A	B	C	D	E	F	G	H
1	10/20/2022	12/22/2022	12/29/2022	1/5/2023			Tag	Frequency
2	11-LSH 9005	11-LSH 9005	9-PI-7123C	3PIC-6830			2HS-141	4
3	11-UA-9101	3UA-8302	2HS-141	4TI-2-39			3GO-2007A	4
4	2HS-141	UA3120-2	3GO-2007A	UA3120-2			3PIC-6830	4
5	3GO-2007A	4UA-3526	6IL501-1	3SI-3535B			3TI-8939	4
6	3HS-6043A	4LSL-8021	3PIC-6830	9-PI-7123C			4FSAH9125	4
7	3JI-6197B	3PIC-6830	UA3120-2	3LI-7345			UA3120-2	4
8	3PIC-6830	3TI-8939	F2015	3TI-8939			6FR1012	4
9	3QAH-9112DEV	4UA-101	K101 HP PIANO	3GO-2007A			3UA-8302	3
10	3QI-1850-2	9-PI-7123C	3TI-8939	4UA-3526			9-PI-7123C	3
11	3QI-2140	F2015	4FSAH9125	11-TIC 9661			11-LSH 9005	2
12	3QZAH-2140	3GO-2007A	6FR1012	09TAH-3914			4TI-2-39	2
13	3TI-8939	6FR1012	3UA-8302	4FSAH9125			ZL3711	2
14	4FSAH9125	2HS-141	06HS-3130	3UA-8302			4UA-3526	2
15	4PDI-3461	3SI-3535B	11-TIC 9661	06HS-3130			F2015	2
16	4TI-2-39	4UA-9920	ZL3711	2HS-141			3SI-3535B	2
17	AI7602B	3FY-6903B		02QI-251			06HS-3130	2
18	HS-6075A	4FSAH9125		4PDI-3525			11-TIC 9661	2
19	PDI-6833A			6FR1012			11-UA-9101	1
20	UA3120-2			9-HS-7120A-2			3HS-6043A	1
21	ZL3711			6FR1012			3JI-6197B	1
22							3QAH-9112DEV	1
23							3QI-1850-2	1
24							3QI-2140	1
25							3QZAH-2140	1
26							4PDI-3461	1
27							AI7602B	1
28							HS-6075A	1
29							PDI-6833A	1
30							4LSL-8021	1
31							4UA-101	1
32							4UA-9920	1
33							3FY-6903B	1
34							6IL501-1	1
35							K101 HP PIANO	1
36							3LI-7345	1
37							09TAH-3914	1
38							02QI-251	1
39							4PDI-3525	1
40							9-HS-7120A-2	1
Sheet6

Cell Formulas
Range	Formula
G2:H40	G2	=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]

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.xlsx
	B	C	D	E	F
24	Frequency	20/10/2022	22/12/2022	29/12/2022	5/1/2023
25	4	2HS-141	2HS-141	2HS-141	2HS-141
26	4	3GO-2007A	3GO-2007A	3GO-2007A	3GO-2007A
27	4	3PIC-6830	3PIC-6830	3PIC-6830	3PIC-6830
28	4	3TI-8939	3TI-8939	3TI-8939	3TI-8939
29	4	4FSAH9125	4FSAH9125	4FSAH9125	4FSAH9125
30	4	UA3120-2	UA3120-2	UA3120-2	UA3120-2
31	3		6FR1012	6FR1012	6FR1012
32	3		3UA-8302	3UA-8302	3UA-8302
33	3		9-PI-7123C	9-PI-7123C	9-PI-7123C
34	2	11-LSH 9005	11-LSH 9005
35	2	4TI-2-39			4TI-2-39
36	2	ZL3711		ZL3711
Active Alarms

 

[Eric W]

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:

Book1
	A	B	C	D	E
1	Frequency	10/20/2022	12/22/2022	12/29/2022	1/5/2023
2	4	2HS-141	2HS-141	2HS-141	2HS-141
3	4	3GO-2007A	3GO-2007A	3GO-2007A	3GO-2007A
4	4	3PIC-6830	3PIC-6830	3PIC-6830	3PIC-6830
5	4	3TI-8939	3TI-8939	3TI-8939	3TI-8939
6	4	4FSAH9125	4FSAH9125	4FSAH9125	4FSAH9125
7	4		6FR1012	6FR1012	6FR1012
8	4	UA3120-2	UA3120-2	UA3120-2	UA3120-2
9	3		3UA-8302	3UA-8302	3UA-8302
10	3		9-PI-7123C	9-PI-7123C	9-PI-7123C
11	2			06HS-3130	06HS-3130
12	2	11-LSH 9005	11-LSH 9005
13	2			11-TIC 9661	11-TIC 9661
14	2		3SI-3535B		3SI-3535B
15	2	4TI-2-39			4TI-2-39
16	2		4UA-3526		4UA-3526
17	2		F2015	F2015
18	2	ZL3711		ZL3711
19	1				02QI-251
20	1				09TAH-3914
21	1	11-UA-9101
22	1		3FY-6903B
23	1	3HS-6043A
24	1	3JI-6197B
25	1				3LI-7345
26	1	3QAH-9112DEV
27	1	3QI-1850-2
28	1	3QI-2140
29	1	3QZAH-2140
30	1		4LSL-8021
31	1	4PDI-3461
32	1				4PDI-3525
33	1		4UA-101
34	1		4UA-9920
35	1			6IL501-1
36	1				9-HS-7120A-2
37	1	AI7602B
38	1	HS-6075A
39	1			K101 HP PIANO
40	1	PDI-6833A
Sheet2

Let me know what you think.

 

[Peter_SSs]

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

ilegall.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	10/20/2022	12/22/2022	12/29/2022	1/05/2023
2	11-LSH 9005	11-LSH 9005	9-PI-7123C	3PIC-6830		2HS-141	4	2HS-141	2HS-141	2HS-141	2HS-141
3	11-UA-9101	3UA-8302	2HS-141	4TI-2-39		3GO-2007A	4	3GO-2007A	3GO-2007A	3GO-2007A	3GO-2007A
4	2HS-141	UA3120-2	3GO-2007A	UA3120-2		3PIC-6830	4	3PIC-6830	3PIC-6830	3PIC-6830	3PIC-6830
5	3GO-2007A	4UA-3526	6IL501-1	3SI-3535B		3TI-8939	4	3TI-8939	3TI-8939	3TI-8939	3TI-8939
6	3HS-6043A	4LSL-8021	3PIC-6830	9-PI-7123C		4FSAH9125	4	4FSAH9125	4FSAH9125	4FSAH9125	4FSAH9125
7	3JI-6197B	3PIC-6830	UA3120-2	3LI-7345		6FR1012	4		6FR1012	6FR1012	6FR1012
8	3PIC-6830	3TI-8939	F2015	3TI-8939		UA3120-2	4	UA3120-2	UA3120-2	UA3120-2	UA3120-2
9	3QAH-9112DEV	4UA-101	K101 HP PIANO	3GO-2007A		3UA-8302	3		3UA-8302	3UA-8302	3UA-8302
10	3QI-1850-2	9-PI-7123C	3TI-8939	4UA-3526		9-PI-7123C	3		9-PI-7123C	9-PI-7123C	9-PI-7123C
11	3QI-2140	F2015	4FSAH9125	11-TIC 9661		06HS-3130	2			06HS-3130	06HS-3130
12	3QZAH-2140	3GO-2007A	6FR1012	09TAH-3914		11-LSH 9005	2	11-LSH 9005	11-LSH 9005
13	3TI-8939	6FR1012	3UA-8302	4FSAH9125		11-TIC 9661	2			11-TIC 9661	11-TIC 9661
14	4FSAH9125	2HS-141	06HS-3130	3UA-8302		3SI-3535B	2		3SI-3535B		3SI-3535B
15	4PDI-3461	3SI-3535B	11-TIC 9661	06HS-3130		4TI-2-39	2	4TI-2-39			4TI-2-39
16	4TI-2-39	4UA-9920	ZL3711	2HS-141		4UA-3526	2		4UA-3526		4UA-3526
17	AI7602B	3FY-6903B		02QI-251		F2015	2		F2015	F2015
18	HS-6075A	4FSAH9125		4PDI-3525		ZL3711	2	ZL3711		ZL3711
19	PDI-6833A			6FR1012		02QI-251	1				02QI-251
20	UA3120-2			9-HS-7120A-2		09TAH-3914	1				09TAH-3914
21	ZL3711			6FR1012		11-UA-9101	1	11-UA-9101
22						3FY-6903B	1		3FY-6903B
23						3HS-6043A	1	3HS-6043A
24						3JI-6197B	1	3JI-6197B
25						3LI-7345	1				3LI-7345
26						3QAH-9112DEV	1	3QAH-9112DEV
27						3QI-1850-2	1	3QI-1850-2
28						3QI-2140	1	3QI-2140
29						3QZAH-2140	1	3QZAH-2140
30						4LSL-8021	1		4LSL-8021
31						4PDI-3461	1	4PDI-3461
32						4PDI-3525	1				4PDI-3525
33						4UA-101	1		4UA-101
34						4UA-9920	1		4UA-9920
35						6IL501-1	1			6IL501-1
36						9-HS-7120A-2	1				9-HS-7120A-2
37						AI7602B	1	AI7602B
38						HS-6075A	1	HS-6075A
39						K101 HP PIANO	1			K101 HP PIANO
40						PDI-6833A	1	PDI-6833A
Sheet1

Cell Formulas
Range	Formula
F2:G40	F2	=LET(t,UNIQUE(TOCOL(Table1,1)),SORT(SORT(CHOOSE({1,2},t,COUNTIF(Table1,t)),1),2,-1))
H2:K40	H2	=LET(v,INDEX($F2#,0,1),IF(ISNUMBER(MATCH(v,INDEX(Table1,0,COLUMNS($H:H)),0)),v,""))
Dynamic array formulas.

 

[ilegall]

Erik and Peter, thank you both so much for the effort! I managed to get Peter's formula approach working for this purpose without much extra work every week.

I will ask one more question though to close the automation loop here. This will require most definitely require VBA now. If I am exporting this set of data from another automatically generated sheet week by week, what would be the easiest solution to automate the import and add simplified date (DD/MM/YYYY) on top of each dataset as shown above?

Thanks!

-ilegall

 

[Peter_SSs]

Are you talking about putting the dates into A1:D1 as in my mini sheet above or are you talking about getting the dates from A1:D1 into H1:K1?

 

[ilegall]

A1:D1 and so forth per each week's import. This could then be simply copied into H1:K1 and more with a simple =cellValue formula, right?

 

[Peter_SSs]

That still is not entirely clear to me, but does this formula in cell H1 in my layout above do what you want?

=Table1[#Headers]

 

[ilegall]

Apologies for the confusion. To clarify: I want to avoid manual copying of data (20 rows you see above) from automatically generated excel sheet to my master data sheet compiling all these automated report's data on a weekly basis. At this point I copy only the 20 rows, not the date above it, which I then add manually per report date. Any chance this import + date on top addition could be automated or semi-automated? Thanks Peter!

-ilegall