polarisking
Member
We know Excel can isolate blank cells in an array using the Go To Special facility. Does something similar exist for non-blank cells?
Thank you, in advance.
Thank you, in advance.
Isolate non-blank cells
- Thread starter polarisking
- Start date
Hi polarisking,
Could you please elaborate your end requirement? What exactly you are after?
If you want non-blank cells (cells containing data) to be isolated separately, you can go to filter and uncheck blanks option to get the non-blank list.
However, I am sure your requirement is something different.
Please explain it bit more clearly.
Kaushik
Could you please elaborate your end requirement? What exactly you are after?
If you want non-blank cells (cells containing data) to be isolated separately, you can go to filter and uncheck blanks option to get the non-blank list.
However, I am sure your requirement is something different.
Please explain it bit more clearly.
Kaushik
polarisking
Member
Sure. I have a 12 x 12 array of cells. A random number of them, say 23 out of the 144, have data in them. The data in the cells isn't the same (think animal names, for example). I'd like to isolate the non-blank cells and change the 23 values to the same value (say, "Yes"). Does that make sense?
polarisking
Member
That did it! Thank you for an elegant solution.
SirJB7
Excel Rōnin
Hi, porlarisking!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: It's not that we (may I speak for you too, Luke M?) tried to appear as elegant, it's just that we don't want to work hard if we could almost not work
EDIT:
PS 2: But I think Luke M wants to work a little bit more than me
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: It's not that we (may I speak for you too, Luke M?) tried to appear as elegant, it's just that we don't want to work hard if we could almost not work
EDIT:
PS 2: But I think Luke M wants to work a little bit more than me
Hi polarisking,
Ok understood.
The way you suggested, I have selected a 12 x 12 array of cells. Now say, you have data at the following 23 random positions of this entire (144) cell range:
A1,A6,A11,A12, B1, B6, B11, D3, E7, E8 , E11, F1,F11, G1, H3, H7, H12, J6, J8, K8, L1,L11,L12.
Now, according to your need, you want to isolate these 23 cells with the same value say, "yes".
Now at N1 if I write =IF(A1<>"","yes","") and copy it 12 cells down and across I will get the same "yes" value for those 23 cells in 12 x 12 array of cells.
Now, at N14 if I write =IFERROR(INDEX(N$1:N$12,SMALL(IF(N$1:N$12="yes",ROW(N$1:N$12)),ROW(A1))),"") and presee CTRL+SHIFT+ENTER (to enter it as an array formula) and copy it 12 cells down and across I will get the I will get "yes" value subsequently one after another.
Is this something close to what you are trying to achieve?
Kaushik
Ok understood.
The way you suggested, I have selected a 12 x 12 array of cells. Now say, you have data at the following 23 random positions of this entire (144) cell range:
A1,A6,A11,A12, B1, B6, B11, D3, E7, E8 , E11, F1,F11, G1, H3, H7, H12, J6, J8, K8, L1,L11,L12.
Now, according to your need, you want to isolate these 23 cells with the same value say, "yes".
Now at N1 if I write =IF(A1<>"","yes","") and copy it 12 cells down and across I will get the same "yes" value for those 23 cells in 12 x 12 array of cells.
Now, at N14 if I write =IFERROR(INDEX(N$1:N$12,SMALL(IF(N$1:N$12="yes",ROW(N$1:N$12)),ROW(A1))),"") and presee CTRL+SHIFT+ENTER (to enter it as an array formula) and copy it 12 cells down and across I will get the I will get "yes" value subsequently one after another.
Is this something close to what you are trying to achieve?
Kaushik