Find and return the nth non blank cell value from a column with formula
For example, I have a column of data as following screenshot shown, now, I will get the third non blank cell value from this list.
Please enter this formula: =INDEX($A$1:$A$25,SMALL(ROW($A$1:$A$25)+(100*($A$1:$A$25="")), 3))&"" into a blank cell where you want to output the result, D2, for example, and then press Ctrl + Shift + Enter keys together to get the correct result, see screenshot:
Note: In above formula, A1:A25 is the data list that you want to use, and the number 3 indicates the third non blank cell value which you want to return, if you want to get the second non blank cell, you just need to change the number 3 to 2 as you need.
Find and return the nth non blank cell value from a row with formula
If you want to find and return the nth non blank cell value in a row, the following formula may help you, please do as this:
Enter this formula: =INDEX($A$1:$M$1,SMALL(IF($A$1:$M$1<>"",COLUMN($A$1:$M$1)-COLUMN($A$1)+1),4)) into a blank cell where you want to locate the result, and then press Ctrl + Shift + Enter keys together to get the result, see screenshot:
Note: In above formula, A1:M1 is the row values that you want to use, and the number 4 is the fourth non blank cell value which you want to return, if you want to get the second non blank cell, you just need to change the number 4 to 2 as you need.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
I'm running this formula on a ~400 row sheet with around 20 blanks mixed throughout the data, and it's working perfectly EXCEPT when I fill the series down, it sometimes duplicates a row for some reason. For example, it seems to think a value is somehow simultaneously the 331st non-blank and the 332nd.
Things I've noticed: -Duplications always happen in pairs (ex. 331 and 332 are duplicates, 333 and 334 will also be), then it goes back to normal for a while. -Raw data also has blanks in pairs, but not corresponding to the duplicates -No duplications occur in the first 94 rows, but they happen every 33-35 rows after that -Does not appear to be linked to the value in the cell being duplicated -I have entered as an array and as a standard formula, no difference in function
For reference, here's my actual formula:
=INDEX('PO Raw Data'!CR:CR,SMALL(ROW('PO Raw Data'!CR:CR)+(100*('PO Raw Data'!CR:CR="")),$W2)&"")
Note $W2 refers to a helper column and counts up as the data set is filled down, but only because I couldn't get it to count up when I just used a number instead of cell reference. When I've replaced that cell reference (ex. $W2) with the appropriate number, same result.
although when I return the nth non blank cell in a column set to DATE, the returned value appears as number(general number).formatting the cell into DATE also doesn't change the result. could you please give me a hand!
thanks a lot
the following formula can help you to get all the row numbers of the non-blank cells, please try it, thank you!
I have a table with numerous columns and rows. The rows have range names, and a description/location at the beginning of the row range with its own range, example "Building No.s". There are multiple Row Ranges/ Buildings. There are headers at the top of each columns for specific "Building Assembly Types". The table content is numeric quantities for each type "Building Assembly Type" for each specific "Building" description/location. There could be as many as (100) "Building Assembly Types" column headers but I will limit the assemblies to (10) per row range, so there will be numerous blank cells in each "Building No." range.
I have other summary tables set up for each "Building" that I want to pull the non-blank cells quantities to, along with the corresponding header "Assembly Type" for each "Building" with a (10) max "Assemblies" per page.
I can identify the non-blank cell quantities by use of the array formula above for rows. The problem I am having is identifying the header that corresponds to it.
Ultimately if I can identify the "Assembly" header, I can always index the "Quantities" once I have the header "Assembly" identified for each "Building". Then I would be able do a lookup for all the parts associated with each "Assembly" type for that "Building" from my Assembly parts database and multiply it times the "Quantity" of each assembly type.
Hello, I am looking for the third blank row in a column and I copied your formula and just changed the range from $A$1:$A:$25 to my range $D$306:$D$354. But when I change the range I get #REF! as my answer. I already converted to an array, control-shift-enter. Now the cell range I am referencing has formulas in them, so could that be why I am getting #REF. Your formula: =INDEX($A$1:$A$25,SMALL(ROW($A$1:$A$25)+(100*($A$1:$A$25="")), 3))&"" My formula: =INDEX($D$306:$D$354,SMALL(ROW($D$306:$D$354)+(1*($D$306:$D$354="")), 2))&""