Find first non empty cell in non-consecutive range without VB

[RodP]

Hi everyone,

I have a spreadsheet of products with a number of columns. Some of the columns help to categorise the product. For each row (product), one of the categorisation columns will not be blank. I'd like to report which column is not blank in a new column. The only thing is these columns are not consecutive.

I've tried creating a named range and using the match formula but it doens't seem to like ranges made up of non consecutive columns.

Can any of you suggest a way to get round this issue. I'd prefer without VB but if it's essential then please could you provide a quick example?

Many thanks in advance

RodP

 

[dhulbert]

is an if statement along the lines of

=if(A1 <> "", "Col A", if(c1 <> "", "Col C", "Col F"))

where A, C adn F are your colums.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[RodP]

Hi Dhulbert,

Thanks for the reply - I have about 20 - 30 columns I'm afraid and so an if statement is going to be too complex. Good suggestion though but am hoping there is something else I can use.

Any other thoughts anyone and everyone?

Thanks

RodP

 

[DirkStruan]

Assuming your columns (one of which is not blank) are A to E and you need to find which is non-blank, try the following (based on row 1):

=MATCH(CONCATENATE(A1,B1,C1,D1,E1),A1:E1,0)

D

 

[AnotherHiggins]

Have a look at Pivot Tables.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[RodP]

Hi DirkStruan,

That seems to work but only as long as none of the columns inbetween the key ones don't have the same word as a column to the right of it. If this was the case, MATCH would give the wrong column number. Is there a way to resolve this issue and use a range in which non-consecutive columns are referenced?

Thanks

Rod

 

[DirkStruan]

Hi Rod

Can you show example data where it works and doesn't work?

 

[DirkStruan]

Sorry - Reread the post and see I forgot the nonconsecutive part of the question (will go back to the drawing board)

 

[RodP]

Hi,



a b (c) d e f
1 P1 2 1
2 P2 3 3
3 P3 3 3 2

Using DirkStruan's sugggestion, using a range of b3:f3 would give an answer of 2 in cell f3. The real answer needs to be 4 as column c needs to be excluded from the range.

Does this help explain my problem.

<<anotherhiggins>>...Pivot tables may be an idea but how would you return the column number that non blank item was found in?

Thanks

RodP

 

[DirkStruan]

Rod

One answer would be similar to the one I just posted in thread68-1289549. It's not great, but it may be the way to go.

D

 

[KenWright]

Using your example data, add in a row either at the top or at the bottom of your data (I'll assume the top, so it becomes row 1), and in each column that you want to be included in your range, put in the column number, or just use =COLUMN(A1), and copy across for each of those cells.

Now just use SUMPRODUCT in the following manner:-

=SUMPRODUCT($A$1:$F$1,--(A2:F2<>""))

and copy down as necessary

Possibly more efficient though to use the same approach but with SUMIF:-

=SUMIF(A2:F2,"<>"&"",$A$1:$F$1)

Assumes there is only ONE value for any row in the range given.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[RodP]

Hi Ken,

Many thanks for your suggestion. I forgot to say though that the actual data is text and not numbers. Sorry! Am reading around a little more and looking at DirkStruan's other thread as above but incase you have some other suggestions, please let me know.

Many thanks

Rod

 

[KenWright]

Doesn't matter. It works for both, as all it is doing is checking each cell in your range to see if it is empty or not, then returning a TRUE/FALSE which subsequently gets converted to binary ie 1/0, and then multiplying that by each of the numbers in the helper row and summing them all.

So what you end up with is something like:-

[tt]
2 5 7 8 10 13 15
0 0 0 1 0 0 0
[/tt]
Assuming you have a value in column 8, then the formula will multiply them out and add them up and you will get 8, which is what you said you wanted.

I'd like to report which column is not blank in a new column.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[RodP]

Hiya Ken,

That looks like what I need. I'll test it out and let you know.

Thanks again (to everyone else too)

RodP