need help to fine-tune a formula that removes blank cells in a column range

naira

New Member
Joined
May 7, 2013
Messages
6
hi!

I need help in fine tuning a formula that removes blank cells from a column range.

Code: =IF(ROWS(B$2:B2)>COUNTIF($A$2:$A$52,"?*"),"",INDEX($A$2:$A$52,MATCH(1,INDEX(($A$2:$A$52<>"")*ISNA(MATCH($A$2:$A$52,$B$1:$B1,1)),0),0)))

Problem is that each value is appearing uniquely in the result, even though it might be appearing multiple times in the original range. Any suggestions to make the values appear sequentially in the order they appear and multiple times.

Alternately, if someone has a better formula would appreciate if it can be shared.

Following limitations may please be considered:
1. No VBA/ Macros please since they are volatile and tend to disable undo option.
2. No array formulas please (those committed with CTRL+SHIFT+ENTER), since my current solution already uses array formula and that has considerably slowed down calculations. My actual worksheet has about 100,000 cells with the array formula updated on a real time basis.
3. The blank cells will actually not be blank but will be containing a formula and the cells are blank since the result of their calculation is blank.


Regards,

Naira
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Sheet1 is assumed to house the relevant data. (Adjust to suit.)

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Sheet1!$A$2:$A$52)-ROW(Sheet1!$A$2)+1

B2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$52,SMALL(IF($A$2:$A$52="","",Ivec),ROWS($B$2:B2))),"")
 

naira

New Member
Joined
May 7, 2013
Messages
6
B2, control+shift+enter, not just enter, and copy down:

Hi Aladin,

Thank you for your prompt reply. Checked your solution and it works perfectly well.
However, in point-2 of my post I had requested that I already have an array solution that is working but it is slowing down my calculations.

Any other solution that avoids arrays and macros?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi Aladin,

Thank you for your prompt reply. Checked your solution and it works perfectly well.
However, in point-2 of my post I had requested that I already have an array solution that is working but it is slowing down my calculations.

Any other solution that avoids arrays and macros?

Check whether the following would be faster with this task...

B1: 0

B2, just enter and copy down:

=IF(A2="","",LOOKUP(9.99999999999999E+307,$B$1:B1)+1)

C1, just enter:

=LOOKUP(9.99999999999999E+307,B:B)

C2, just enter and copy down:

=IF(ROWS($C$2:C2)<=$C$1,LOOKUP(ROWS($C$2:C2),B:B,A:A),"")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi Aladin,

Thank you for your prompt reply. Checked your solution and it works perfectly well.
However, in point-2 of my post I had requested that I already have an array solution that is working but it is slowing down my calculations.

Any other solution that avoids arrays and macros?

Check whether the following would be faster with this task...

B1: 0

B2, just enter and copy down:

=IF(A2="","",LOOKUP(9.99999999999999E+307,$B$1:B1)+1)

C1, just enter:

=LOOKUP(9.99999999999999E+307,B:B)

C2, just enter and copy down:

=IF(ROWS($C$2:C2)<=$C$1,LOOKUP(ROWS($C$2:C2),B:B,A:A),"")

Did you try the above set up?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

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