Finding Unknown Values

[Moe1950]

I have a column in a spreadsheet that is either populated by either an #N/A (which is populated using a COUNTIF(XX:XX,"Value") ) or an actual number. I have no way of know what that number is.

What I need to do is find all of the rows in the spreadsheet where that particular column DOES NOT equal #N/A

What would be best way of accomplishing this?

This is Excel 2007

 

[Akashwani]

Hi and welcome to MrExcel.

Does this do what you require?...

Excel Workbook
	A	B	C	D
1	Data	*	Results	*
2	#N/A	*	1	*
3	#N/A	*	2	*
4	1	*	3	*
5	2	*	4	*
6	#N/A	*	5	*
7	3	*	6	*
8	#N/A	*	7	*
9	4	*	*	*
10	#N/A	*	*	*
11	#N/A	*	*	*
12	5	*	*	*
13	6	*	*	*
14	7	*	*	*
15	*	*	*	*
Sheet10

The formula in C2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
You will need to change the cell references to suit your layout.

OR using Conditional Formatting....

Excel Workbook
	A	B	C	D
1	Data		Results
2	#N/A		FALSE
3	#N/A		FALSE
4	1		TRUE
5	2		TRUE
6	#N/A		FALSE
7	3		TRUE
8	#N/A		FALSE
9	4		TRUE
10	#N/A		FALSE
11	#N/A		FALSE
12	5		TRUE
13	6		TRUE
14	7		TRUE
15
Sheet10

I hope that helps, if not, then I suggest that you post some sample data and your expected results.

Ak

 

[Moe1950]

I don't know how to make those nice looking tables that you made, so I'll do my best.

In one column I have entries like
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
AC3867521
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
672B98712
#N/A
#N/A
#N/A
etc

There are 13,834 rows. I used a COUNTIF so I know that out of those 13,834 rows there are 44 numbers leaving 13,790 occurances of #N/A - I need to ignore the #N/As and either get the r:number of each of the 44 entries, or copy the 44 entries into a blank row.

I can accomplish my goal by scrolling through all 13,834 entries locating each of the 44 numbers I'm looking for one at a time, but I figure there has to be a better way than that.

In your forumla example, what is 'SUMPRODUCT'?

 

[Aladin Akyurek]

I don't know how to make those nice looking tables that you made, so I'll do my best.

In one column I have entries like
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
AC3867521
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
672B98712
#N/A
#N/A
#N/A
etc

There are 13,834 rows. I used a COUNTIF so I know that out of those 13,834 rows there are 44 numbers leaving 13,790 occurances of #N/A - I need to ignore the #N/As and either get the r:number of each of the 44 entries, or copy the 44 entries into a blank row.

I can accomplish my goal by scrolling through all 13,834 entries locating each of the 44 numbers I'm looking for one at a time, but I figure there has to be a better way than that.

In your forumla example, what is 'SUMPRODUCT'?

Try...

=COUNTIF($A$:$A$100,"?*")

The formula excludes the text in the header cell A1.

 

[Moe1950]

Try...

=COUNTIF($A$:$A$100,"?*")

The formula excludes the text in the header cell A1.

Thank you very much, but that will tell me how many there are in the column. I have that number. I need to locate them without manually scrolling through all 13,834 rows.

 

[Aladin Akyurek]

Thank you very much, but that will tell me how many there are in the column. I have that number. I need to locate them without manually scrolling through all 13,834 rows.

D1:

=COUNTIF($A$:$A$100,"?*")

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

=IF(ROWS($D$2:D2)<=$D$1,
  INDEX($A$2:$A$19,SMALL(IF(1-ISNA($A$2:$A$19),
  ROW($A$2:$A$19)-ROW($A$2)+1),ROWS($D$2:D2))),"")

 

[Moe1950]

=IF(ROWS($D$2:D2)<=$D$1,
  INDEX($A$2:$A$19,SMALL(IF(1-ISNA($A$2:$A$19),
  ROW($A$2:$A$19)-ROW($A$2)+1),ROWS($D$2:D2))),"")

What is this doing? I looked up ISNA; I know what IF and ROW are, but ya lost me on INDEX and SMALL

Is this saying if you find a number in column A put it in column D?

Also, both responders to my question have said "control+shift+enter, not just enter, and copy down" - what does that mean? When I copy down, I highlight, do CTRL+C, click on the first cell and scroll to the last cell that I want then do CTRL+V - I've never used any kind enter key when doing a copy 'n paste.

 

[Aladin Akyurek]

=IF(ROWS($D$2:D2)<=$D$1,
  INDEX($A$2:$A$19,SMALL(IF(1-ISNA($A$2:$A$19),
  ROW($A$2:$A$19)-ROW($A$2)+1),ROWS($D$2:D2))),"")

What is this doing? I looked up ISNA; I know what IF and ROW are, but ya lost me on INDEX and SMALL

Is this saying if you find a number in column A put it in column D?

Also, both responders to my question have said "control+shift+enter, not just enter, and copy down" - what does that mean? When I copy down, I highlight, do CTRL+C, click on the first cell and scroll to the last cell that I want then do CTRL+V - I've never used any kind enter key when doing a copy 'n paste.

Try first to follow the instructions.

Control+shift+enter means: Hold down the control and the shift keys and hit the enter key at the same time. When done properly, you see { and } appear around the formula on the formula bar. As such, you can drag it down.

Note. ISNA tests whether an entry is #NA. 1-ISNA(#N/A) would equal 0, otherwise 1.