How to sort matching colums

Question

I have a huge database to sort. Basically I have datas from 2 periods. Over the years let's say people joined the database while others leaved.

Here is a screenshot :

Problem

How can I come to this type of result where everything is sorted :

Final result

Thanks

Answer 1

I think to solve this problem you should use google-script.

This is an option using pure google-sheet formula, hopefully can help you somewhat

Method 1:

=ArrayFormula({INDEX(SPLIT(FILTER(INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1),INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1)<>""),"/"),0,1),
IF(FILTER(INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,2),INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1)<>"")<>"",INDEX(SPLIT(FILTER(INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1),INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1)<>""),"/"),0,2),),
IF(FILTER(INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,3),INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1)<>"")<>"",INDEX(SPLIT(FILTER(INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1),INDEX(QUERY({SPLIT("2005"&"#"&FILTER(A2:A&"/"&B2:B,A2:A<>""),"#");SPLIT("2015"&"#"&FILTER(C2:C&"/"&D2:D,C2:C<>""),"#")},"select Col2,count(Col2) group by Col2 pivot Col1"),0,1)<>""),"/"),0,2),)})

---

Method 2: (make formula more compact)

=QUERY(ArrayFormula({SPLIT("2005"&"|"&FILTER(A2:A&"|"&B2:B,A2:A<>""),"|");
                     SPLIT("2015"&"|"&FILTER(C2:C&"|"&D2:D,C2:C<>""),"|")}),
       "select Col2,min(Col3) group by Col2,Col3 pivot Col1 order by Col2")