Stumped on a simple formula. Need help

[mdean32]

What formula could I use that would locate the first numeric value for Part A,B,C, and then give the correct date in which the first numeric value was located? Example, if I wanted to know the due date of Part C, how would I get the formula to return 4/22? Part B to return 4/20? etc Thank you in advance

Part	4/18	4/19	4/20	4/21	4/22
A		2
B			3
C					5

 

[GraH]

One way,

Book1
	A	B	C	D	E	F	G	H	I
1		apr/18	apr/19	apr/20	apr/21	apr/22			Date
2	A		2						apr/19
3	B			3					apr/20
4	C					5			apr/22
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=INDEX($B$1:$F$1,MATCH(9.9^99,B2:F2))

 

[mdean32]

Awesome that worked. One thing I left out. If I have numbers on different dates for the same part, the formula returns the value of the last date. How can I make it return the first date? Example
I need Part A to return the date of the first numeric value which would be 4/18. Thank you so much for your help

Part	4/18	4/19	4/20
A	2	5	3

 

[JoeMo]

Another way, assuming only one numeric value per part.

Book1
	A	B	C	D	E	F	G
1	Part	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	Due Date
2	A		2				19-Apr
3	B			3			20-Apr
4	C					5	22-Apr
Sheet2

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX($A$1:$F$4,1,MATCH(SUM($A2:$F2),$A2:$F2,0))

 

[GraH]

Perhaps like so

Book1
	A	B	C	D	E	F	G	H	I
1	Part	apr/18	apr/19	apr/20	apr/21	apr/22
2	A	1	2						apr/18
3	B		5	3					apr/19
4	C			2	7	5			apr/20
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=INDEX($B$1:$F$1,AGGREGATE(15,6,(COLUMN($B$1:$F$1)-1)/(B2:F2>0),1))

 

[mdean32]

GraH I can't get your last formula to work in a big data set. You're first formula gets me exactly what I need other than giving the last date of a numeric value instead of returning the first date of a numeric value. Thanks for all the help

 

[mdean32]

This formula worked great , except on row 4 and 6 it returned the date for the last value in the row instead of the date for the first value.

I2:I4

I2

=INDEX($B$1:$F$1,MATCH(9.9^99,B2:F2))

Material	OH Inv	Past Due	8-Apr	9-Apr	10-Apr	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	pd	next 5	Due Date
119825083M	0	60																60	0	Past Due
KE22207645M	0						40											40	0	4/12
116600122002M	0	9			18													27	0	4/10
116600122008M	-23							23										0	23	4/13
116600122011M	23					477				500								477	500	4/15

 

[mdean32]

This formula worked great , except on row 4 and 6 it returned the date for the last value in the row instead of the date for the first value.

I2:I4

I2

=INDEX($B$1:$F$1,MATCH(9.9^99,B2:F2))

Material	OH Inv	Past Due	8-Apr	9-Apr	10-Apr	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	pd	next 5	Due Date
119825083M	0	60																60	0	Past Due
KE22207645M	0						40											40	0	4/12
116600122002M	0	9			18													27	0	4/10
116600122008M	-23							23										0	23	4/13
116600122011M	23					477				500								477	500	4/15

So I assume there is no way to always get the first numerical value in a row instead of the last for the above spreadsheet?

 

[Double XL]

Hi mdean,

you could create a new function in VBA to do the work for you.

Put the following code into a standard module

Function duedate(target As Range)
  R = target.Row
  firstC = 3
  lastC = target.Column - 3
  For C = firstC To lastC
    If Cells(R, C) <> "" Then
      duedate = Cells(1, C)
      Exit For
    End If
  Next
End Function

and call it from the cell.

ie under "Due Date" in cell U2 put =duedate(u2)

hope that works for you.

 

[Double XL]

or, if you don't want to use VBA
you could put the formula in U2:
=INDEX($C$1:$R$1,MATCH(TRUE,INDEX((C2:R2<>0),0),0))

That might work also.