Apparently empty (blank) cells aren't empty

[daymaker]

I exported an access database to an excel spreadsheet. In one of my columns, I need to select or 'go to' all blank fields so that I can then copy data from above field into them. Problem is, the fields (shown below) appear blank, but they aren't. So when I select the column and choose 'Edit | Go To | Special | Blanks | Ok', no blank fields are recognized.


-- removed inline image ---


I read on another site's thread that possibly when exporting empty fields in access file to excel, there is in fact a null string there rather than it actually being empty and this may be spaces.

I am hoping there's a way to possibly identify the true contents of these supposedly blank cells and then to truly empty/clear the contents. Or, I could find all cells that have these invisible characters and 'go to' them & then do my copy step. Any ideas?

Thanks, John

 

[Mr_Lucky]

A cell with a formula isn't a blank cell, what are you trying to do with the cell afterwards?

I am referencing 3 cells in total to calculate a ranking using the below formula. Cell reference BO and BP are calculated cells based on other data, which is why some of the cells do not contain text or numbers and only the formula.

=IF(COUNTA(BO3:BP3)=2,COUNTIFS(BO$3:BO$4001,BO3,BO$3:BO$4001,"<>",BL$3:BL$4001,">"&BL3,BP$3:BP$4001,"<>")+1,"")

 

[MARK858]

Only on my phone now so can't post a formula but if it is the Countifs part then just put in another criteria with <>""

 

[pgc01]

Not very clear the first part of the Countifs()

COUNTIFS(BO$3:BO$4001,BO3,BO$3:BO$4001,"<>",...

both are testing BO3:BO4001?

Can't you take the second test out, like

IF(BO3<>"",COUNTIFS(BO$3:BO$4001,BO3,...

 

[Mr_Lucky]

Thanks for the reply. I have looked alternative formulas and the follow one seems to work an ignores the blank cells with formula I.

IF(AND([@[Regulatory impact]]<>"", [@Volume]<>"",[@[Top 10]]<>""), SUMPRODUCT(--(C3=$C$3:$C$15),--(D3<$D$3:$D$15))+1,"")

The only remaining problem that the ranking order only adjust for D3:D15 and no for C3:C15 or the Top 10 E3:E15. Tried to add another array for E3:E15 but cant get it to work.

Any ideas how I can get the ranking order to go 1,2,3, instead of 1,2,4 when a blank cell is referenced.

 

[Mr_Lucky]

Added data

No	Regulatory impact	Volume	Top 10	Ranking	Expected result	Pass/Fail
7	Over-reporting	700	Top 10 Over-reporting	1	1	Pass
5	Under-reporting	500	Top 10 Under-reporting	2	2	Pass
6	Over-reporting	600	Unscheduled	2	2	Pass
10	Mis-reporting	1000	Unscheduled	1	1	Pass
4	Under-reporting	400	Top 10 Under-reporting	3	3	Pass
9	Under-reporting	900	Unscheduled	1	1	Pass
3	Under-reporting	300	Top 10 Under-reporting	4	4	Pass
8	Under-reporting	100	Unscheduled	5	5	Pass
13	Mis-reporting		Top 10 Mis-reporting		-	Pass
12	Mis-reporting	960			-	Pass
11	Mis-reporting	950			-	Pass
2	Mis-reporting	200	Top 10 Mis-reporting	4	2	Fail
1	Mis-reporting	100			-	Pass

<colgroup><col width="64" style="width: 48pt;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="152" style="width: 114pt; mso-width-source: userset; mso-width-alt: 5558;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>