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

Posted on 2011-04-20
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
    LVL 50

    Assisted Solution

    by:barry houdini
    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

    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

    Is the data numeric?

    If so you could use this formula


    or for any type of data


    regards, barry

    Author Closing Comment

    Perfect,  Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    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…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now