×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

First column with a value4

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?

RE: First column with a value

(OP)
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.

RE: First column with a value

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.

RE: First column with a value

=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.

RE: First column with a value

Quote (IFR)

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.

RE: First column with a value

(OP)
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...

RE: First column with a value

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

RE: First column with a value

(OP)
= 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...

RE: First column with a value

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/671435/excel-formul...

RE: First column with a value

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)?

RE: First column with a value

(OP)
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

RE: First column with a value

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

RE: First column with a value

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:

CODE --> vb

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/

RE: First column with a value

(OP)
Hey folks - these are all great suggestions!
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!