• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

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

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.
0
wlwebb
Asked:
wlwebb
  • 2
  • 2
2 Solutions
 
barry houdiniCommented:
If the other two columns will always be blank then perhaps you can just concatenate all three, e.g. in D2

=A2&B2&C2

copy that formula down the column

regards, barry
0
 
wlwebbAuthor Commented:
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.

Example:

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?
0
 
barry houdiniCommented:
Is the data numeric?

If so you could use this formula

=LOOKUP(9.9E+307,A1:C1,{"A","B","C"})

or for any type of data

=LOOKUP(2,1/(A1:C1<>""),{"A","B","C"})

regards, barry
0
 
wlwebbAuthor Commented:
Perfect,  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now