If Statements and dealing with blank cells

terrykalaka

New Member
Joined
Sep 10, 2014
Messages
6
Hey Gang,

Long time lurker, first time poster here! I've searched around a for a couple of days to try to figure out my deal here, but haven't been able to find a solution, so here goes.

The problem I'm working on is for creating start lists for a cycling event. We start with a Sign In worksheet like this:

A
B
C
D
E
F
G
H
1
No.NameLic #Cat.StateTTIP
2
196John Stevenson582451
GAx
x
3
268Steven James236543
GAx
4
310Don Johnson44038
4
GAx
5
4
32
Chad Christie
64573
2
GA
x
x

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The TT and IP columns are the two different events, x in each of columns G and H column for riders signing up for that particular event. From that, we create a start/time sheet using formula:

=IF('Sign In'!G2="","",'Sign In'!C2)

To get this Start List worksheet:

A
B
C
D
E
1
Heat
Start
Position
No.Men's 1000m TTTime
2
1
Front
96John Stevenson
3
Back
4
2Front10Don Johnson
5
Back32Chad Christie
6
7
Heat
Start Position
No.Men's 4k IPTime
8
1Front
96John Stevenson
9
Back68
Steven James
10
2Front
11
Back32Chad Christie

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


Everything works great on that part, except for the blank cells that are left from riders in the original Sign In sheet not doing a particular event. Right now, we manually go through all of the lists and remove the blank cells. This is where I'd like to automate that process, so that in the case that the IF statement returned a blank cell, that it would read the next cell from the Sign In sheet until it returned an actual value. What do you guys think?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
The formula {=IF('Sign In'!G2="","",'Sign In'!C2)} is the same as having NO forumula. Each record should be its own and not read a different record.
If you dont want blank, show a different character.
 
Upvote 0
The formula {=IF('Sign In'!G2="","",'Sign In'!C2)} is the same as having NO forumula. Each record should be its own and not read a different record.
If you dont want blank, show a different character.


It's not that they don't want a blank, it's that they want to skip to the next filled line. This formula is a formula as it returns C2 based on G2. I don't get your statement of it being the same as no formula.


As for the OP, it sounds like a visual basic problem because the only possible thing I can think of is a nested index/match/offset formula that might get messy.
 
Upvote 0
Welcome to the forum.

This is not easy to do with a formula but simple to do with a macro.

Code:
Sub lineup()
Dim currRow As Long
Dim lastRow As Long
Dim heat As Integer
Dim outRow As Long


lastRow = Sheets("Sign In").Range("C1000").End(xlUp).Row
Sheets.Add
Application.ScreenUpdating = False
Cells(1, 1) = "Heat"
Cells(1, 2) = "Start Position"
Cells(1, 3) = "No."
Cells(1, 4) = "Men's 1000m TT"
Cells(1, 5) = "Time"


outRow = 2
heat = 1
For currRow = 2 To lastRow
    If (Sheets("Sign In").Cells(currRow, 7) <> "") Then
        If outRow Mod 2 = 0 Then
            Cells(outRow, 1) = heat
            Cells(outRow, 2) = "Front"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        Else
            Cells(outRow, 2) = "Back"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        End If
        outRow = outRow + 1
    End If
Next
outRow = outRow + 3
Cells(outRow - 1, 1) = "Heat"
Cells(outRow - 1, 2) = "Start Position"
Cells(outRow - 1, 3) = "No."
Cells(outRow - 1, 4) = "Men's 4K IP"
Cells(outRow - 1, 5) = "Time"
heat = 1
For currRow = 2 To lastRow
    If (Sheets("Sign In").Cells(currRow, 8) <> "") Then
        If outRow Mod 2 = 0 Then
            Cells(outRow, 1) = heat
            Cells(outRow, 2) = "Front"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        Else
            Cells(outRow, 2) = "Back"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        End If
        outRow = outRow + 1
    End If
Next
End Sub
 
Upvote 0
Thanks for the replies, after my search I had been thinking it would be something more intense than a regular formula could handle. par60056, thanks for the tip, I'll give it a shot and see how it goes, thanks!
 
Upvote 0
I just realized there is a line missing from the macro.

Add:
Application.ScreenUpdating = True

Right before the "End Sub"
 
Upvote 0
The worst line to forget....

DARN IT EXCEL WHY WON'T YOU LET ME TYPE!!! GAH!!!!!!

Funny thing is that the macro ran find in testing and everything looked right. But I bet it would mess with your mind later.
 
Upvote 0
Before I learned of the 'Immediate' window I would have my own macro call to turn on/off anything I forgot to turn on/off. Before that...I usually just shut it all down if I forgot that step.
 
Upvote 0
With it as a macro you can then add formatting (text wrap, bold, column widths outlines) and have a lot of fun
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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