Forum Discussion
How can i resolve this problem?
I prepared a table in excel with "IF" formula using a data, then i copied and pasted the values in the table as values. Then i have so many blank cells in my table and i cannot delete them easily as using "go to special" because excel doesn't understand these blank cells as blank. I searched the reason why i encounter this problem, and the answer is that they don't seem blank because they came from a formula. I need an advice about how i can resolve my problem that i cannot define unnecessary cells easily to delete them. i am sharing the mini table with you to explain my problem, you should know that indeed i have a big data that is hard to delete unnecessary cells manually. Thanks for your help, and good ideas..
Have you tried the Filter function ?
Example in file with your source data "as is" in first sheet, and with your source data in a table in second sheet.
"=FILTER(Table1[Provinces];Table1[Countries]=G$1)"
14 Replies
Another approach is to run Text to Columns (Delimited, clear all delimiters) on the data. This re-evaluates a column's contents. Go to special can then identify the blanks.
The downside is Text to Columns is 1 column at a time.
Another variant with legacy solution
To select list of countries in M1
=IFERROR( INDEX($C$2:$C$12, AGGREGATE(15,6,1/(COUNTIF($L$1:L$1,$C$2:$C$12)=0)*(ROW($C$2:$C$12)-ROW($C$1)),1) ), "")and drag it to the right.
To pickup provinces in M2
=IFERROR( INDEX($D$2:$D$12, AGGREGATE(15,6, 1/($C$2:$C$12=M$1)*(ROW($D$2:$D$12)-ROW($D$1)), ROW()-ROW(M$1) ) ), "")and drag it to the right and down.
- Thank you so much. After your answer, i searched something about array functions and aggregate usage. I really loved your approach. That legacy solution is so helpful, and i spend a few hours to comprehend it. Thanks for sharing it with us !
Sekoleyte , glad to help
Have you tried the Filter function ?
Example in file with your source data "as is" in first sheet, and with your source data in a table in second sheet.
"=FILTER(Table1[Provinces];Table1[Countries]=G$1)"
That's really useful function which is the best way for our problems! Thank you. But the only deficiency of this function is that it's available just in office 365. I appreciate you.
Yes, it works. But i was after how i can get rid of blank cells. what was your strategy to get rid of blank cells ?
A workaround would be to change your formula, in the first step, to insert a character ("-"), rather than a blank ("") e.g. =IF($C2=K$1,$D2,"-")
Then copy / paste values as normal and a new step will be find / replace (CTRL + H); find - replace with nothing (leave the replace field empty)......now your special / blanks function will work.
- That's a good idea, i appreciate you for your help. İ can use any character that doesn't exist in any cell instead "", then i replace them with blank and i can delete blank cells. it absolutely works 🙂
