Hi Guys,
I have a list in N
Apples
Pears
Grapes
(Space)
(Space)
Oranges
Bananas
I'm looking to tidy this in Column O
Apples
Pears
Grapes
Oranges
Bananas
I have this already done in another spreadsheet and have (as I thought) copied it exactly and just changed the relevant letter for columns but its not ignoring the gaps in my new one. Can you see any errors in the below
Please Login or Register to view this content.
looks to me like it should work, are you activating it by using ctrl/shift/enter because it is an array formula?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi
Yes. Have the Curley brackets by pressing ctl sft enter. Picture attached
I used it exactly as you put it in post 1 and activated it by ctrl/shift/enter and then dragged it down and it got rid of the spaces (though it did not get rid of duplicates though you didn't say that was an issue).
You may have to post a sample to see what is going on.
While putting together my sample sheet I stumbled across the error. The "blanks are not blanks. They are formula giving a blank result. When I delete the formula in the cells the new list closes the gap. How do I ignore formula with a "" return
Ok, you likely will have to upload a sample.
Because I put your list as shown in cells L3 through L9 with blanks in L6 and L7. Then I put a formula in N3 through N9 which pulls over the values from col L.
then I used your formula in O3 and hit ctrl/shift/enter then dragged it down so even with a formula in N6 and N7 giving blanks the formula still ignored those cells and consolidated the list.
Please see attached
you have a space in your reference formula =IF('Set Up'!D3=""," ",'Set Up'!D3)
change it to =IF('Set up'!D3="","",'Set up'!D3) without the space and you'll be ok and the formula will work.
Oh, and next time if you can save it as an .xlsx instead of .xls, caused me problems in opening it.
Please change Bank Account N2 to
=IF('Set Up'!D2="","",'Set Up'!D2)
or O2 with CSE
=IF(ROWS(O$2:O2)< COUNTA('Set Up'!D:D),INDEX('Set Up'!D:D,SMALL(IF('Set Up'!$D$2:$D$40<>"",ROW('Set Up'!$D$2:$D$40)),ROWS(O$2:O2))),"")
This fixed my error. Something as silly as "nothing"
=IF('Set Up'!D2="","",'Set Up'!D2)
Thanks again to everyone for taking the time
glad I could help, AND thank you for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks