So before posted I searched the forum to find a relevant topic and found "Excel Remove Blank Cells from a Range" and found a formula which i thought I could used to firstly reove blanks from a column and then hopefully use the same technique to remove the cells form rows as well, however I stumbled at the first hurdle, the original formula was as follows:-
=IFERROR(INDEX($A$2:$A$9, SMALL(IF(LEN($A$2:$A$9)=0,"", ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1), ROW(A1))),"")
Now this worked just fine when copying the example into a blank worksheet , but when I tried to use it in my sheet - nothing! The only differences was the location in the spreadsheet (and of course altered the values to reflect the new location, but it will not work for me at all.
What I actually have is a spredasheet that needs the blanks or 0 removing form columns and rows, for example see below
So in cloumn A & B where there a gaps to remove and from column b in the rows removing all blanks in between.
I have uploaded the Test spreadheet which includes the tab that has the example which works.
All help gratefully appreciated
Richard
| 20014267 | Rapeseed Oil Bulk | 0.004713 | E330 Citric Acid | 0.000024% | |||||
| 20008534 | Sea Salt | 0.004189 | |||||||
| 20021332 | Bagel Conditioner 2018 | 0.003142 | Ascorbic Acid | 0.012567% | |||||
| 12030050 | Calcium Propionate | 0.001969 | |||||||
| 20011688 | RS 190 Star | 0.001571 | |||||||
Hi Richard
Welcome to the forum.
I think you will need to clean your data first. There are many cells within your data range that looks like blank but are not.
If you press F5 - Special - Constants - OK you will notice that many "blank" cells are selected. I suspect this could be your problem.
Try cleaning up these "blank" cells and see if your formula works.
Sunny
Hi Sunny,
many thanks for the quick reply, alas no this does not work, I cleaned the data before I started and still it does not work and at the moment I am only concentrating on the first column to get that working but still it does not work, I know it is me that is doing something wrong but its not because the cells are not blank, i even manually cleaned it up on that column. As you can see from the screenshot, the cells above the text are blank and should be removed with the formula (the screenshot was after I did the F5 constants) The formula is in c280.
Richard
🙁
Hi Richard
In cell B286 (or whatever row that suits your data) enter (array formula)
=IFERROR(INDEX(B$264:B$284, SMALL(IF(LEN(B$264:B$284)=0,"", ROW(B$264:B$284)-MIN(ROW(B$264:B$284))+1), ROW(B1))),"")
Copy down as many rows as needed.
Note that I have removed the $ from $B so that you can copy the formula across multiple columns.
Hope this helps.
Sunny
Hi Sunny,
Many Many Thanks for your continued support.
Obviously, because of the time difference (Which I am used to as I used to work in Australia and my daughter and her family are still there!), I spent most of yesterday trying to get it to work, and did using your original formula array, I went back to the source data and in the process of making the cells blank I turned off in advanced options any cells with 0 in and converted the formula from If(d81=0,"",c81) to if(d81=0,0,c81), dont really know why this then worked with the array formula but it did and I am very happy, thank you so much.
The only problem i have now is that the array formula does not recognize numbers, so where i have say egg 0.1% and tomato 0.2%, it ignores the numbers completely and I have to have the sub components in order of size in the compound ingredient :(, not sure if you could have a think about that one?
many thanks
and Kindest Regards
Richard
Hi Richard
I am not clear about your issue with the %.
Can you post some sample with the problem for us to have a clearer picture?
Sunny
Hi Sunny,
Ignore previous request, all is working fine, Many thanks for all your help, you are a complete and utter star 🙂
Kindest Regards
Richard
Thanks for the compliment.
Glad I was able to help.
Sunny
