How can I quality check a BIG spreadsheet?

frank265

New Member
Joined
Feb 5, 2013
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
Hi All,

I was hoping an excel genius on here might be able to help solve this problem...

Basically I have an excel recipe file that I want to import into a database. Each row on the recipe can have UPTO 50 columns filled, but not more.
The important thing is that each column should only have data in it if the column to it's left has been filled, i.e. there shouldn't be any blank columns in-between... In the example below Row 2 is correct and Row 3 is wrong.
Recipe NameIngredient 1Ingredient 2Ingredient 3Ingredient 4Ingredient 5Ingredient 6Ingredient 7Ingredient 8
Apple Pie1 litre of Milk12 Eggs500g Butter6 cups of Flour1 kg of apples2 Tsp of CinamonPinch of Rock Salt2 Tsp of Sugar
Apple Pie1 liter of Milk12 Eggs500g Butter6 cups of Flour1 kg of Flour2 Tsp of CinamonPinch of Rock Salt

If there is a gap in the columns the import stops for that recipe where the gap is making the recipe incomplete. So Apple Pie number 2 would only have 3 ingredients showing instead of 8.

I can manually check each recipe but the spreadsheet is huge and means scrolling 50 columns to the right for each recipe, plus there are steps to the recipe to which means very long columns.

Is there some kind of formula I can use to detect gaps in the columns? NB, blank columns are ok at the end of the ingredients, for example, I mentioned that there are 50 columns, Ingredients 9+ could be blank if there are not that many ingredients and recipe steps, but not in-between information... I hope that makes sense?

Be grateful for any advice here...

Thanks so much

Frank
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,059
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Would it be acceptable to replace the Blank/null value cell with an arbitrary value such as "0" or "x" so that the load is not interrupted?
 

frank265

New Member
Joined
Feb 5, 2013
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
Hi Alan, thanks for coming back to me about this. I could give that a try.

How would you recommend to do that?

Thanks for your help!

Frank
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,059
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Here is a VBA code you apply.
VBA Code:
Option Explicit

Sub FindBlank()
    Dim lr As Long, lc As Long
    Dim i As Long, j As Long
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        lc = Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 2 To lc
            If Cells(i, j) = "" Then Cells(i, j) = 0
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "Completed"

End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button
 

frank265

New Member
Joined
Feb 5, 2013
Messages
25
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Here is a VBA code you apply.
VBA Code:
Option Explicit

Sub FindBlank()
    Dim lr As Long, lc As Long
    Dim i As Long, j As Long
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        lc = Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 2 To lc
            If Cells(i, j) = "" Then Cells(i, j) = 0
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "Completed"

End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

Thank you SO MUCH! - This is so kind of you, I really appreciate it :)

Can't say thanks enough for this.

Have a great evening!

Frank
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just as alt example using .SpecialCells(xlCellTypeBlanks) and reducing total loop count, try:
VBA Code:
Sub ReplaceBlank()

    Dim LR  As Long: LR = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
    Dim r   As Range: Set r = cells(1, LR+1)
    Dim x   As Long
    
    On Error Resume Next
    For x = 2 To LR
        Set r = Union(r, Cells(x, 1).Resize(, Cells(x, Columns.Count).End(xlToLeft).Column).SpecialCells(xlCellTypeBlanks)
    Next x
    On Error Goto 0

    Application.ScreenUpdating = False
  
    r.Value = 0
    Cells(1, LR+1).Value = ""

    Application.ScreenUpdating = True
  
    Set r = nothing

End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,693
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Why not simply

Select the whole range>>Use Find/Replace,
Find what: ""
Replace with: 0
press Replace all
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
Another option if you want to shift elements rather than replace with 0.
VBA Code:
Sub Remove_Blanks()

Dim WS_DATA() As Variant, T As Long, Y As Long, N As Long, Row_CLN As New Collection

WS_DATA = ThisWorkbook.ActiveSheet.UsedRange.Value

For T = 1 To UBound(WS_DATA, 1) 'Loop rows of array

    For Y = 1 To UBound(WS_DATA, 2) 'loop columns
    
        If WS_DATA(T, Y) = Empty Then 'if array element is empty
        
            For N = Y To UBound(WS_DATA, 2) 'Add items to collection that need to be shifted
                On Error Resume Next '<--- in case of duplicates
                If WS_DATA(T, N) <> Empty Then Row_CLN.Add WS_DATA(T, N)
                On Error GoTo 0
            Next N
            
            If Row_CLN.Count >= 1 Then 'ensure collection has items...may not if data already has empty columns to the far right
            
                For N = Y To UBound(WS_DATA, 2)
                    If (N - Y + 1) <= Row_CLN.Count Then
                        WS_DATA(T, N) = Row_CLN(N - Y + 1)
                    Else
                        WS_DATA(T, N) = Empty
                    End If
                Next N
            
            End If
            
            Set Row_CLN = Nothing
            
            Exit For
            
        End If
        
    Next Y
    
Next T

ThisWorkbook.ActiveSheet.UsedRange.Resize(UBound(WS_DATA, 1), UBound(WS_DATA, 2)) = WS_DATA

End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
@Michael M based on OP initial post and subsequent reply, my guess is the rows are jagged with varying end columns, where the last column on the row can't be empty, or yeah that seems the simplest solution!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,295
Messages
5,595,294
Members
413,984
Latest member
stikpet

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
Top