Using Vlookup with 2 criteria (or other function) 2

[Mikeb1970]

Hi all,

I have a spreadsheet that contains all production codes and times from all our machines.

What i'm trying to make is a visual view off the time used for each machine to do their steps.

I have this working quit well, using the match offset combination.

I now wanna add some features, but need to look at two criteria instead of one for finding a start time on a machine. I know this can be done using an advanced filter, but due to some practical reasons don't prefer this way.

Is there a way to make a formula using whatever function needed???

With regards and happy holidays

Mike

 

[bluedragon2]

DGET and DLOOKUP are some options. If you give some sample data, may come up with more options.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Mikeb1970]

Hi Blue,

Made a small example (should have done this in firts post)
It is not much, but should give you an idea.

The real file contains about 18000 rows for just one week of data, containing data for 12 machines.

Prod# Machine Start End Code Time used
352001 AFS1 11:23 11:24 Start 1
352001 AFS1 11:24 11:35 Blow O2 11
352001 AFS1 11:35 11:35 Stop O2 1
352001 AFS1 11:36 11:36 End 1
352002 AFS1 12:12 12:13 Start 1
352002 AFS1 12:13 12:45 Blow O2 32
352002 AFS1 12:45 12:46 Stop O2 1
352002 AFS1 12:52 12:57 End 5
352003 AFS1 14:24 14:25 Start 1
352003 AFS1 14:32 14:54 Blow O2 22
352003 AFS1 14:54 14:55 Stop O2 1
352003 AFS1 15:00 15:01 End 1


Now, letts say i want the start time for Prod# 352002 at the machine called AFS1

All data is sorted twice after the query (VBA) first on time then on machine.

also already made some formulas determining the array that contains the data for each machine.

Hope this will give you some ideas

With regards and thnx for your time

Mike

 

[KenWright]

Have you considered using a Pivot table to analyse your data. It looks made for it, and would give you flexibility that would amaze you if you haven't used a Pivot table before. In case you want to try, take a look at Debra Dalgleish's introduction:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

Regards
Ken................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[Mikeb1970]

Hi Ken,

Have looked at pivot tables before, maybe i need to study them more closely.

Can i make a pivot table dynamic, meaning recalculate from changing a cell from userform?

The idea is to run the spreadsheet in background, only showing the userform when the file opens, because most users are novice when it comes to using excel, and if there is one thing (cell) you forgot, they will try to enter data in there, therefore choose for this solution


Mike

 

[bluedragon2]

Mike,

I put together a little sheet with some examples on it, if you like I can send it to you.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Mikeb1970]

Hi Blue,

would be very much appreciated,

just send to m.biermans@skynet.be

thnx and a happy new year

Mike

 

[Mikeb1970]

Hi Blue,

This is a great solution, no need for allot of changes in the sheet


thnx

Mike

 

[KenWright]

You can have it do pretty much anything you like. You can have dynamic ranges (again see Debra's site for that as well), and using VBA you can have anything recalculate really.

Regards
Ken................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[KenWright]

Sounds like you have a working solution, so by all means stick with it.

Regards
Ken.................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[Mikeb1970]

Hi Ken,

Still gonna look for the pivot table option, already working on another project, a bit more complicated, gonna try to calculate the time lost in production

Mike

 

[atruhoo]

Mike or Blue,
is it possible to post solution or piece of in case one of your fellow posters has a similar situation.
Thanks

 

[bluedragon2]

The sheet just showed examples of using the vlookup and dget functions.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Mikeb1970]

Tried it at work today on the data,
worked great , until i had two lines with a start
code for same machine. So i got an error there,
my fault, hadn't considered that.

So back to drawing board

Mike

 

[bluedragon2]

Mike,

I added something to the sample sheet and emailed it to you.

Take a look



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Mikeb1970]

Hi Blue,

This works, but can it be adapted that when i only have one start code it would also work. Then i could loose the DGET
function and rely on the code, will then trigger it by a cel change. Asking this because this could also work for another feature i want to add. As the product moves throw the plant the quality should improve, but when something strange happens sometimes a product getts downgraded and will be moved back in production. Asking this because i'm quite a novice in VBA.

Thnx for your help sofar

Mike

 

[bluedragon2]

The example will work if you have anywhere from 1 to max number of start's. I only clear 15 so if you have more then 15 start's, then you need to adjust that part. The code is intended to replace the DGET function. As for the change cell event, I will leave that to the VBA guru's here. They should be able to better the piece of code.

To the VBA guru's, I don't have time today to research how to do this on a change cell event, maybe you can help:

'Set Result start area and Reference Variables
fr = 5
fc = 10
prodref = Range("h2&quot.Value
machref = Range("i2&quot.Value
coderef = Range("j2&quot.Value

'Clear result area
For clr = 5 To 20
Cells(clr, fc).Value = ""
Cells(clr, fc + 1).Value = ""
Cells(clr, fc + 2).Value = ""
Next clr

'Check for matches
For i = 2 To 13 'need to adjust to your array last row number
If Cells(i, 1).Value = prodref And Cells(i, 2).Value = machref And Cells(i, 5).Value = coderef Then
Cells(fr, fc).Value = Cells(i, 1).Value
Cells(fr, fc + 1).Value = Cells(i, 2).Value
Cells(fr, fc + 2).Value = Cells(i, 3).Value
fr = fr + 1
End If
Next i




Blue

If I wasn't Blue, I would just be a Dragon...

 

[Mikeb1970]

Hi Blue,

I know how to trigger the code from the change cell event,
learning this coding as the need rises, and learned allot from experts like you here, for which i am very greatfull.
In return i try to help those that are new to excel, and also because i love programming. The sky is the limit.


Thnx again for your great help

Mike

 

[bluedragon2]

My pleaseure,

Oh, and on the sample worksheet, Cell E2 has white spaces in it, so just type Start in that cell, and change the criteria to that Prod # and you will see it works for just one also.

When I copied your data over, I didn't clean it up.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Mikeb1970]

Hi Blue,

Doesn't work here for only one match, but i found the reason

You prob copied A1:C1 to J4:L4 therefore linking the start
code as a column heading. I don't know why, and find it strange, and don't have time now to look for this, but i copied and pasted the start code from E2 to L5 and now it works great

Thnx again

Mike

Happy new year