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

x
?
Solved

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

Posted on 2011-04-20
4
Medium Priority
?
478 Views
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.
0
Comment
Question by:wlwebb
  • 2
  • 2
4 Comments
 
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

=A2&B2&C2

copy that formula down the column

regards, barry
0
 

Author Comment

by:wlwebb
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.

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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 35435763
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
 

Author Closing Comment

by:wlwebb
ID: 35435861
Perfect,  Thanks
0

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