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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

P
  • Question Question
How to create a multiple drop-down lists in Excel using Developer?
4 5 6
Replies
51
Views
829
PetarTen
P
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
85
Peter_SSs
Peter_SSs
R
  • Question Question
Add to Data Validation Values From Data Entry Cell
Replies
4
Views
181
kvsrinivasamurthy
K
C
  • Question Question
Protecting a Cell but allowing data validation drop down list to work
Replies
1
Views
420
Artik
Artik
M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
2K
MissDB101
M

Forum statistics

Threads
1,223,956
Messages
6,175,611
Members
452,660
Latest member
Zatman

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