Need Help : Creating Lists with IF Statements

C

Clockout

New Member
Joined
Dec 23, 2017
Messages
4
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:
Code: 
=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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Looking at your picture, on which it is notoriously difficult to try code, this would do it I think.
Code: 
Sub AAAAA()
Dim lc, i As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lc Step 2
        If WorksheetFunction.CountA(Columns(i)) > 1 Then
            With ActiveSheet.UsedRange.Columns(i).Resize(, 2)
                .SpecialCells(4).Delete Shift:=xlUp
            End With
        End If
    Next i
End Sub
Make sure to try it on a copy of your workbook, not a copy of the picture as it will screw it up!
A Merry Christmas and a Happy and Prosperous New Year to you and yours
 
Upvote 0
@jolivanes
As the OP is using a formula to populate the sheet, the cells aren't blank.
Also, even if one of the cells is actually blank you are only deleting cells from the first of the 2 columns not both.
 
Upvote 0
Hence the "try it on a copy"
If I misread/misunderstood it, as you pointed out, the OP can let us know what the problem is.
But it certainly looks like you're right. Thanks for pointing that out.
With a loop one can do it even if it has formulae but that deletes the formulae of course. Mind you, they could be entered again with code. (What a go around here!!!!)
On the other hand, it might be advantageous to do everything by code instead of half by formulae and the other half by code. Or the other way around of course.
If the OP can explain concise and to the point what needs to be done, I am sure someone will have a suitable solution.
And if the data is pulled in by code, if you do it proper it does not need to delete empties!


Anyway, have a Merry Christmas and a Happy and Prosperous New Year to you and yours Fluff.
 
Last edited:
Upvote 0
Seasons greetings to you as well.
 
Upvote 0
Just for the heck of it.

Code: 
Sub Del_Empties_With_Loop()
Dim lc, i As Long, j As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
    For i = 1 To lc Step 2
        For j = Cells(Rows.Count, i).End(xlUp).Row To 1 Step -1
            If Cells(j, i).Value = "" Then Cells(j, i).Resize(, 2).Delete Shift:=xlUp
        Next j
    Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Here is a sample of the raw data that I have my formula pulling from. This information is being dropped in a data drop tab. I have a tedious work around, I would have to select the cells with room numbers and sort by Column the room numbers are in to bring them in line. Also with the code that you've provided;where do I paste that in excel? I do apologize but I was not kidding when i say I am extremely new to excel.
Code: 
[TABLE="width: 1515"]
<tbody>[TR]
[TD]c[/TD]
[TD]GRP_BY_COL[/TD]
[TD]GRP_BY_DESC[/TD]
[TD]SEC_RMNO[/TD]
[TD]CHAR_DEPDATE[/TD]
[TD][/TD]
[TD][/TD]
[TD]SUM_NTS[/TD]
[TD]SUM_RMS[/TD]
[TD]SUM_BALANCE[/TD]
[TD]RESORT1[/TD]
[TD]IS_SHARED_YN[/TD]
[TD]ROOM[/TD]
[TD]NIGHTS[/TD]
[TD]ARRIVAL[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12/14/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12/20/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12/20/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]16005[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12/20/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]16041[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12/21/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]16124[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12/20/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]19637[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12/21/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]20083[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12/19/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]22623[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12/19/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]25625[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12/21/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]32026[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12/17/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]33032[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12/21/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5789.85[/TD]
[TD="align: right"]190[/TD]
[TD]N[/TD]
[TD="align: right"]35051[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12/19/2017[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Ok try this
Code: 
Sub RoomsNumbers()

   Dim Cl As Range
   Dim Dsht As Worksheet
  
   Set Dsht = Sheets("Data Drop")
   With Sheets("Today!!")
      For Each Cl In Dsht.Range("M2", Dsht.Range("M" & Rows.Count).End(xlUp))
         Select Case Mid(Cl.Value, 3, 1)
            Case 0, 1
               .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Cl.Value
            Case 6
               .Range("G" & Rows.Count).End(xlUp).Offset(1).Value = Cl.Value
         End Select
      Next Cl
   End With
End Sub
This will put rooms with a 3rd digit of 0 or 1, in col A & those with a 3rd digit of 6 in col G.
If that is wrong change the values in red to suit.
There is a tutorial on installing & running macros here
http://www.contextures.com/xlvba01.html

This code needs to go in a regular module
 
Upvote 0
Thank you so much! It works great!

I am having 2 issues if you can help. If not it's fine I can manage!

1. Can we switch what column the data is going to after a certain number of lines.
Example:

After room number is dropped in Cell A30, the next room will go to C6-C30 then E6-E30

2. with Floors 3-9 the 3rd number for tower 2 can be a 1 or a 0.. So is it possible to check for a "6" on the second number if there are only 4 numbers?
Example:

Room 9603 only has 4 numbers, looking for a 6 as the second number.


Thank you for everything! I've learned so much!

Happy Holidays!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
600
MissDB101
M
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
308
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
Dynamic Sheet using Drop Down List & INDIRECT using External File
Replies
5
Views
410
smozgur
smozgur
J
  • Question
Need help with filter in 2016 excel
Replies
1
Views
140
Jamieneedshelp
J
M
  • Question
Formula to populate the next available cell in a column using data populated in another cell.
Replies
4
Views
399
Marshkevuk
M

Forum statistics

Threads
1,215,626
Messages
6,125,896
Members
449,271
Latest member
bergy32204

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top