excel: find position of cells containing data in a row 1

[firegambler]

Hi Guys,

I have a table containing 9 columns. Each column in every row can, but does not have to, contain a value.
There is a maximum of 4 cells per row containing values.
What I would like to have is a formula that helps me to identify in which column per row I have my values as shown below.
As there are 4 values max per row i would like to have the positions of the values displayes in 4 columns.

Any idea?
Thanks a lot in advance

example: desired result
a b c d e f g h i || j k l m
5 6 9 2 || 1 2 4 6
7 3 2 4 || 2 4 5 9

 

[SkipVought]

Hi,

What is the business case for this scenario? This is not a usual table problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[firegambler]

Hi Skip,

the point behind it is that I have a set of machines with different setting options.
In total there are 9 different criteria that the machines can possibly have. But each machine has a maximum of 4 options out of the 9.

I have a table containing all settings for all machines.
I would now like to link those settings into file cards for every machine so that you can see the settings for each machine on the filecard.
I would like to have just 4 cells on the card displaying the settings of the respective machine instead of having 9 cells with all possible settings of any machine and then leaving some of them blank.

Cheers
F

 

[GlennUK]

You could an formula to find the first non-zero cell, and then use that ( and subsequent results ) in an OFFSET function call to limit further searches to columns not yet tested.

The first formula (in cell J2 ) would be like:

=MATCH(1,--(A2:I2>0),0)

entered using Ctrl-Shift-Enter, and the second formula would be:

=MATCH(1,--(OFFSET($A2,0,J2,1,9-J2)>0),0)+J2

also entered using Ctrl-Shift-Enter into cell K2 and copied across to L2 and M2.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[firegambler]

Hi Glenn,

thanks for your post! The first part works, but only for positive values, somehow negative figures are ignored.
The second part returns strange values to me like 138 for a 9 column spreadsheet.
Thanks for the effort invested though!

I figured out a workaround meanwhile.
I made a second table next to the one containing the data.
Then I applied an if-statement that looks like this:

=IF(COUNTIF(A2,A2)=0,"",COLUMN(AG2))

This returned the number of the column containing the value.
(COUNTIF returns a 0 for empty cells.)

Then I used

=CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2)

to get all column numbers into one cell.

Then I split up that cell using text functions

LEFT and MID

to get my (up to 4) values to 4 different cells.

Cheers