Is there a coalesce-like function in Excel

0 votes

I need to fill a cell with the first non-empty entry in a set of columns (from left to right) in the same row - similar to coalesce() in SQL.

In the following example sheet

---------------------------------------
|     |  A   |   B   |   C   |    D   |
---------------------------------------
|  1  |      |   x   |   y   |    z   |
---------------------------------------
|  2  |      |       |   y   |        |
---------------------------------------
|  3  |      |       |       |    z   |
---------------------------------------

I want to put a cell function in each cell of row A such that I will get:

---------------------------------------
|     |  A   |   B   |   C   |    D   |
---------------------------------------
|  1  |  x   |   x   |   y   |    z   |
---------------------------------------
|  2  |  y   |       |   y   |        |
---------------------------------------
|  3  |  z   |       |       |    z   |
---------------------------------------

I know I could do this with a cascade of IF functions, but in my real sheet, I have 30 columns to select from, so I would be happy if there were a simpler way.

Oct 20 in Others by Kithuzzz
• 20,660 points
84 views

1 answer to this question.

0 votes
To have Excel analyze the formula as an Array Formula after you enter it, hit CTRL + Shift + Enter. The first non-blank value within the specified cell range is returned by this. The formula is typed in the column with the header "a" for your example.
answered Oct 21 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
53 views
0 votes
1 answer

Is there a way in Microsoft Excel to give specific bins different bin widths when making a histogram plot?

Excel built-in histogram tool only allows equal ...READ MORE

answered Oct 2 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer
0 votes
0 answers

Export SQL query data to Excel

My query returns a huge amount of ...READ MORE

Aug 18 in Database by Kithuzzz
• 20,660 points
47 views
0 votes
1 answer

Removing duplicates from Excel rows by adding values of some columns

Copy the first three columns to another ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
48 views
0 votes
1 answer

Xls to csv converter

Use xlrd - it's faster, cross-platform, and ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
36 views
0 votes
1 answer

Excel viewer lib

 Try http://viewerjs.org. You will need to save your ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
40 views
0 votes
1 answer

Is there a function to unhide columns in excel through python

Excel file : df.to_excel('demofile.xlsx',index=False) import openpyxl py = openpyxl.load_workbook('demofile.xlsx') exlsheet = ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
58 views
0 votes
1 answer

Is there a function in excel to find duplicates

Solution: You can use Conditional formatting inside the ...READ MORE

answered Nov 14 in Others by narikkadan
• 37,660 points
23 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP