First column with a value 4

[IFRs]

I have a worksheet with parts in column B and costs in columns N through DD.
For a given row (part number), there may be one or more than one cost in columns N through DD
The columns N through DD have data that is newest to oldest.
It is easy to get the average cost with Average(N3D3)
How do I get the most recent cost?

B N Q AB
Bolt $3 $4
Nut $1
Washer $1 $2

For Bolt the latest cost is $3
For Nut the latest cost is $1
For Washer the latest cost is $1

Manually I just use Ctrl-Right but how is that done in a formula?

 

[IRstuff]

CTRL-Right is something that can be programmed into a macro.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[IFRs]

Well yes, I could do that and automate it for the 600 or so line items but I was looking for a built-in function like find or index or hlookup that would stop at the first non-blank cell in each row and return a value.

 

[Denial]

There might be some way you can construct a clever, but convoluted, formula to do this using a combination of built-in functions, but I cannot think of one off the top of my head.[ ] However it would be very simple with VBA to create a user-defined function to do it.

 

[davidbeach]

=IF(Nx<>"",Nx,IF(Ox<>"",Ox,IF(Px<>"",Px,IF......)))

It would be a bear to write it all out, but it would be easy enough to program the writing.

 

[MintJulep]

The columns N through DD have data that is newest to oldest.

How do I get the most recent cost?

Uh. It's in column N.

 

[IFRs]

Sorry for not being clear Mr Julep - each column represents a purchase order, each row represents part numbers. A given purchase order only has values for a few parts. The Nth column has newest values for only a few parts.

David - Yes, I could do that but each time I added columns I'd have to revise all the formulas.

I'm thinking I might be able to get HLookup to do it...

Might have to use VBA...not my strong suit...

 

[MintJulep]

Normalize your data. Then it becomes really easy.

http://www.tek-tips.com/faqs.cfm?fid=7103

 

[IFRs]

I'm too ignorant to normalize data. Can you elaborate please?

 

[MintJulep]

http://technet.microsoft.com/en-us/library/ms191178(v=sql.105).aspx

 

[IFRs]

= HLookup(Cell,DataRange,1,TRUE) will get me the last value in a row if the value of Cell is a value larger than anything in the row ie: Max(DataRange+1)

I can't make it get me the first item in the row yet.

Still working on it...

 

[Denial]

I'm not clever enough to come up with this, but I found
=INDEX(DataRange,MATCH(TRUE,INDEX(DataRange<>0,),0))
which seems to work provided zero is not a valid cost.
Note that it returns #NA if DataRange is completely empty.

See

http://superuser.com/questions/6714...on-zero-value-in-row-and-return-column-header

 

[MortenA]

Sometimes its easier if you include a sample.

Also: They way you describe it "The columns N through DD have data that is newest to oldest" it seems to me that the "newest" data will always be in collumn N and the oldest to the far right(and thus not always in the same column if each purchase order does not have the same number of entries)? But i guess that its the other way around (oldest in collumn N and the newest to the right not always in the same collumn)?

 

[IFRs]

There are hundreds of rows - one for each part number
There are hundreds of columns - one for each purchase order
Each purchase order is for only a few parts
So, columns N through DD have mostly empty cells

 

[DSikes]

If you end up going the VB route, this is what I would do:

Dim i As Integer
Dim j As Integer
Dim myArray(*total number of rows*,*total number of columns*) As Double

i=*first row of data*
j=*first column of data* where A=1 B=2....so on

Do While IsEmpty(Cells(i, 2)) = False

Do While IsEmpty(Cells(i,j)) = False​

j = j + 1​

Loop​

myArray(i,j) = Cells(i,j).Value​

i = i + 1​

Loop

You could then use the array to put the data somewhere using a similar process but reversing the myArray = Cells to Cells=myArray and choosing the column you want the data to go in for the j number and leaving the i as is.

Apologize for any errors, typed in a hurry.

Daniel Sikes
Design Engineer
Young Touchstone
NX 8.0.3.4

 

[IDS]

Daniel, the second IsEmpty check should be:
Do While IsEmpty(Cells(i,j)) = True,
or just:
Do While IsEmpty(Cells(i,j))

Also the loops can be sped up greatly by converting the range to an array, and writing the result array back in a single operation, rather than stepping through it:

Function FirstCol(DataRange As Variant) As Variant
Dim Numrows As Long, Numcols As Long, i As Long, j As Long
Dim FirstColA() As Long

DataRange = DataRange.Value2
Numrows = UBound(DataRange)
Numcols = UBound(DataRange, 2)
ReDim FirstColA(1 To Numrows, 1 To 1)

For i = 1 To Numrows
j = 0
Do
j = j + 1
Loop While IsEmpty(DataRange(i, j))
FirstColA(i, 1) = j
Next i
FirstCol = FirstColA
End Function

Enter the function as an array function, with DataRange being columns N to DD x number of rows required. The column numbers returned will start at N = 1.

One other minor point, VBA converts integers to longs, so its actually slightly quicker to declare them as longs to start with, and then you don't have to worry about if the value will exceed the integer limit or not.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[telecomguy]

Here you go (for Row 2):
=INDEX(N2D2,MATCH(TRUE,INDEX(N2D2<>0,),0))

I don't take credit for it. I found it here:

http://superuser.com/questions/6714...on-zero-value-in-row-and-return-column-header

-tg

http://xternal.me

 

[IFRs]

Hey folks - these are all great suggestions!
For now, I'm using =INDEX(N2D2,MATCH(TRUE,INDEX(N2D2<>0,),0)) even though I don't quite understand what it does well enough to teach someone else how to crate it.
Many thanks!