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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Well I'm in lockdown and finding reasons to not move... this can only help, parttaaaaaaaaay hat is on whilst waiting for the OP to clarify!
I may go crazy today and blow up a balloon.. you know variety and all that
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
We're a bit luckier here...golf course across the road, club is closed, but we can use the course...as long as we abide by social distancing and all that stuff !!
 

frank265

New Member
Joined
Feb 5, 2013
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
Just wanted to say a huge thank you to everyone who has commented here, what an amazing community! :)

Thanks so much

Frank
 

frank265

New Member
Joined
Feb 5, 2013
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
@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!
Hi, I managed to resolve this with some of the earlier suggestions and a little manual manipulation, but thank you so much. But yes, the data is jagged for example on recipe 1, there could be only 5 ingredients and the rest of the columns blank. For recipe 2 there could be 10 ingredients and the remaining columns blank. Blank columns in themselves are not an issue, it's only an issue when one of the columns is skipped...
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
So long as you have working for you what you need from the various replies, then ... the work here is done!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,975
Messages
5,599,127
Members
414,291
Latest member
yanqing06

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