How can I quality check a BIG spreadsheet?

[frank265]

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 Name	Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4	Ingredient 5	Ingredient 6	Ingredient 7	Ingredient 8
Apple Pie	1 litre of Milk	12 Eggs	500g Butter	6 cups of Flour	1 kg of apples	2 Tsp of Cinamon	Pinch of Rock Salt	2 Tsp of Sugar
Apple Pie	1 liter of Milk	12 Eggs	500g Butter		6 cups of Flour	1 kg of Flour	2 Tsp of Cinamon	Pinch 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

 

[Michael M]

@JackDanIce
Agreed....I guess we'll have to wait and see...?

 

[JackDanIce]

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]

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]

Just wanted to say a huge thank you to everyone who has commented here, what an amazing community!

Thanks so much

Frank

 

[frank265]

@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]

So long as you have working for you what you need from the various replies, then ... the work here is done!