First column with a value
First column with a value
(OP)
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(N3:DD3)
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?
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(N3:DD3)
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?





RE: First column with a value
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: First column with a value
RE: First column with a value
RE: First column with a value
It would be a bear to write it all out, but it would be easy enough to program the writing.
RE: First column with a value
How do I get the most recent cost?
Uh. It's in column N.
RE: First column with a value
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...
RE: First column with a value
http://www.tek-tips.com/faqs.cfm?fid=7103
RE: First column with a value
RE: First column with a value
RE: First column with a value
I can't make it get me the first item in the row yet.
Still working on it...
RE: First column with a value
=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/671435/excel-formul...
RE: First column with a value
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)?
RE: First column with a value
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
RE: First column with a value
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
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
RE: First column with a value
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:
CODE --> vb
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/
RE: First column with a value
=INDEX(N2:DD2,MATCH(TRUE,INDEX(N2:DD2<>0,),0))
I don't take credit for it. I found it here:
http://superuser.com/questions/671435/excel-formul...
-tg
http://xternal.me
RE: First column with a value
For now, I'm using =INDEX(N2:DD2,MATCH(TRUE,INDEX(N2:DD2<>0,),0)) even though I don't quite understand what it does well enough to teach someone else how to crate it.
Many thanks!