20

I am working with a list of data where one or multiple cells in a row can be blank.

Lets say the list is cells A1, A2, A3, A4. I am trying to create a function that will do the following:

IF A1 has a value I want the cell to return A1.
  IF A1 is empty then I want it to return A2.
   IF A1 and A2 are both empty I want it to return A3.
    If A1, A2 and A3 are all empty I want it to return A4.
Vincent De Smet
  • 4,859
  • 2
  • 34
  • 41
excelquestion
  • 201
  • 1
  • 2
  • 3
  • 1
    how many rows do you need to support, if it's few you can do nested if statements `=IF(A1<>"",A1,IF(A2<>"",A2,IF(A3<>"",IF(A4<>"",""))))` would handle your example – Vincent De Smet Apr 30 '15 at 12:13
  • 1
    I am working with financial data so the list is about 500 rows. the issue is certain dates will have blanks and I am trying to fill the blanks in with prior data. there are about 100 lists and each list will have different different dates missing. I had thought about the if blank function as there should never be more than 4 consecutive dates missing but couldn't figure out how to do it. this seems like a great solution thank you so much – excelquestion Apr 30 '15 at 14:39
  • in this case, your example & explanation did not really match your requirements. There might be a better approach to your issue. also, I made a mistake at the A3 part, there should be `A3,` between `,` and `IF(A4...` – Vincent De Smet Apr 30 '15 at 14:41
  • I added another answer based on your comment, sorry for changing your title as your description seemed different from what you needed. I don't think this can easily be done with a formula, the vba required to achieve what you need is quite short. have a look and have fun! – Vincent De Smet Apr 30 '15 at 14:56
  • can't excel just add a =COALESCE() function like in SQL ? – Rutger Hofste Sep 03 '18 at 14:50

5 Answers5

24

first result on google: http://chandoo.org/wp/2014/01/15/find-first-non-blank-item-in-a-list-excel-formulas/

This formula returns the first TEXT cell for a range B1:B100:

=VLOOKUP("*", B1:B100, 1,FALSE)

* is a wild card in Excel. When you ask VLOOKUP to find *, it finds the first cell that contains anything.

NOTE: This approach finds first cell that contains any TEXT. So if the first non-blank cell is a number (or date, % or Boolean value), the formula shows next cell that contains text.

If you need to find non-blank that url gives the following solution:

If you want to find first non-blank value, whether it is text or number, then you can use below array formula.

=INDEX(B1:B100, MATCH(FALSE, ISBLANK(B1:B100), 0))

Make sure you press CTRL+Shift+Enter after typing this formula.

How this formula works?

  • ISBLANK(B1:B100) portion: This gives us list of TRUE / FALSE values depending on the 98 cells in B1:B100 are blank or not. It looks like this: {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE; ...}

  • MATCH(FALSE, ISBLANK(…), 0) portion: Once we have the TRUE / FALSE values, we just need to find the first FALSE value (ie, first non-blank cell). That is what this MATCH function does. It finds an exact match of FALSE value in the list.

  • INDEX(B1:B100, MATCH(…)) portion: Once we know which cell is the first non-blank cell, we need its value. That is what INDEX does.

Community
  • 1
  • 1
Vincent De Smet
  • 4,859
  • 2
  • 34
  • 41
3

As indicated in your comment on your question, you have 500 rows interspersed with blank cells. You want to fill blank cells with the value of the last non blank cell.

I'd write some VBA code that'd work as follows: select the range of cells you want to back fill and run this VBA:

Sub fillBlanks()
  For Each c In Selection.Cells
    If c.Value <> "" Then
      lastVal = c.Value
    Else
      c.Value = lastVal
    End If
  Next c
End Sub

basically, if the cell is empty, use the value of the last non blank cell (if there were no blank cells above, it will remain blank). Else, if the cell is not empty, save this as the last non blank cell. Repeat for every cell in the selected range.

Step by Step instructions on using this vba code - for this sample worksheet:

sample worksheet

Make sure the range is selected, press ALT+F11.

This should open the Visual Basic Editor:

Visual Basic Editor

Press F7, This should bring up the code for the activesheet. Paste the VB code from above:

add Code

Press F5 (or use the menu to run the code).

run Code

The end result should be as follows:

result

Vincent De Smet
  • 4,859
  • 2
  • 34
  • 41
2

Select ColumnA:

HOME > Editing > Find & Select > Go To Special... > Blanks, OK, =, ↓, Ctrl+Enter.

pnuts
  • 58,317
  • 11
  • 87
  • 139
1

You can just put a rank.eq formula in the column next to it, and do a vlookup to bring all of your data to the top. This will bring all of your data to the top.

For example, in the image below I am ranking using the percentage, I want to bring the cells with data to the top for presentation, I will hide all columns other than where my vlookups are.

Screenshot of spreadsheet

Greenonline
  • 1,330
  • 8
  • 23
  • 31
Kevin
  • 11
  • 1
1

This did the trick for me

=LOOKUP(2,1/(A1:A13<>""),A1:A13)

Source credit: here

enter image description here

Shankar ARUL
  • 12,642
  • 11
  • 68
  • 69