Need Help : Creating Lists with IF Statements

[Clockout]

I want to start off by saying I'm suuper new to Excel and completely Self-taught mostly using these forum threads. I've hit a hitch lately and I need some assistance resolving my problem.

I work for a Front Desk in Las Vegas. And I'm trying to make a report that auto-sorts information from Delimited data. Currently I'm using this method:

=IF(MID('Data Drop'!M2,3,1)="0",'Data Drop'!M2,IF(MID('Data Drop'!M2,3,1)="1",'Data Drop'!M2,""))   AND     =IF(MID('Data Drop'!M2,3,1) = "6",'Data Drop'!M2,"")

This pulls the room number for a corresponding tower into two separate areas. However I do not want blank cells in between.

I don't know if that makes sense but this is what it looks like: https://imgur.com/a/jZX0B

I want to remove the blank cells, but I cannot filter them out since it affects all cells.
Perhaps I can do a IF statement that will generate a List for the true value?


Thank you for any help!

 

[Fluff]

How about

Sub RoomsNumbers()
   
   Dim Cl As Range
   Dim Dsht As Worksheet
   Dim NxtRw As Long
   Dim NxtCol1 As Long
   Dim NxtCol2 As Long
   
   Set Dsht = Sheets("Data Drop")
   With Sheets("Today!!")
      NxtCol1 = 1
      NxtCol2 = 7
      For Each Cl In Dsht.Range("M2", Dsht.Range("M" & Rows.Count).End(xlUp))
         If Len(Cl.Value) = 5 Then
            Select Case Mid(Cl.Value, 3, 1)
               Case 0, 1
                  NxtRw = .Cells(Rows.Count, NxtCol1).End(xlUp).Offset(1).Row
                  If NxtRw > 30 Then
                     NxtCol1 = NxtCol1 + 2
                     NxtRw = .Cells(Rows.Count, NxtCol1).End(xlUp).Offset(1).Row
                  End If
                  .Cells(NxtRw, NxtCol1).Value = Cl.Value
               Case 6
                  NxtRw = .Cells(Rows.Count, NxtCol2).End(xlUp).Offset(1).Row
                  If NxtRw > 30 Then
                     NxtCol2 = NxtCol2 + 2
                     NxtRw = .Cells(Rows.Count, NxtCol2).End(xlUp).Offset(1).Row
                  End If
                  .Cells(NxtRw, NxtCol2).Value = Cl.Value
            End Select
         Else
            NxtRw = .Cells(Rows.Count, NxtCol2).End(xlUp).Offset(1).Row
            If NxtRw > 30 Then
               NxtCol2 = NxtCol2 + 2
               NxtRw = .Cells(Rows.Count, NxtCol2).End(xlUp).Offset(1).Row
            End If
            .Cells(NxtRw, NxtCol2).Value = Cl.Value
         End If
      Next Cl
   End With
End Sub

 

[Clockout]

Thank you omg. Amazing!

 

[Fluff]

Glad to help & thanks for the feedback