[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Excel function to return a row's column number that has data

Posted on 2011-04-20
Medium Priority
Last Modified: 2012-05-11
Hello Experts
Thanks as always in advance for your help.

I have a file that gets created from an export from a program.  The way the export program sends the data out causes the data for a particular row to either be in column A, B or C (it varies).   One row the data will be in A, the next in B the next back to A, the next to C etc..   Each row has only one column's cell that will have data.  

I have looked for a function that would look at columns A:C find which column of the particular row had data and get that data.  Essentially pulling all the data in to One Column for all rows.

I've looked at Hlookup; Index etc.. but can not get anything to work.  Probably because I'm writing the code wrong.
Question by:wlwebb
  • 2
  • 2
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 2000 total points
ID: 35435447
If the other two columns will always be blank then perhaps you can just concatenate all three, e.g. in D2


copy that formula down the column

regards, barry

Author Comment

ID: 35435707
Good lord that was simple.  So let me through a curveball to that.  Lets say A, B and C can each contain data within a row and I want to test for the right most column that has data and get the column number that has.


Lets say Row - {Column A}/{Column B}/{Column C} - I'll use **** to indicate blank
1 - *** / 123 / 123
2 - 123 / 123 / ***
3 - 123 / *** / 123
4 - 123 / ***/ ***
5 - *** / 123 / 123

Then the result I would want is:
1 - C
2 - B
3 - C
4 - A
5 - C

Is there a function/set of functions that could test for that?
LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 35435763
Is the data numeric?

If so you could use this formula


or for any type of data


regards, barry

Author Closing Comment

ID: 35435861
Perfect,  Thanks

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question