Splitting up Group By for similar values in a column within SQL Server

Question

I am trying to split up the values in column5 so that when using a GROUP BY on column5 (seen below) the 2 value isn't all grouped together. Instead, the values will be separated out so that the first value of 2 is in it's own group the the second group is the value 46675 and the final third groupings will be the last couple 2 values. In short, I am looking for a way to split up the 2 value so that it does not aggregate all the 2 values together, but instead splits them into separate groupings (mirroring the 'Groups' column). The intended outcome is to have the each separated 2 values be aggregated together in their own respective groups. Added tidy table and picture from Excel file.

||Column1||Column2||Column3||Column4||Column5||Groups||
||   1   ||   NO  ||   A   ||    F  ||  2    ||  1   ||
||   2   ||   Yes ||   B   ||    C  ||  46   ||  2   ||
||   3   ||   NO  ||   C   ||    F  ||  2    ||  3   ||
||   4   ||   NO  ||   D   ||    F  ||  2    ||  3   ||

Image of Table from Excel File

Answer 1

This is the prototype gaps and islands problem. You're looking for runs in Column5 ordered by Column1:

with data as (
    select *,
        row_number() over (order by Column1) -
            row_number() over (partition by Column5 order by Column1) as grp
)
select * from data order by Column1;