Question to use VLOOUP, Index, Match or Combination to find and replace values in cell.

[geranuno]

Hello Everyone!

I am trying to do a cycle time tracker to measure the assembly time (For example) from Start to Finish the process.
We currently scan the serial number of a unit on the beginning … and at the end of a process,
The first step is done (To capture the start-up time)

My problem is on the second step, as I am having problems to paste the values (Paste special) or to create a mechanism that when we scan the unit at the end of the process (Grey area), the program look for that value on the serial number(in) and compare with the time difference.

The yellow block on the file is a fake …. I just did it as example of what I need.
For reference, please see the attached file.

Thanks so much,
(This is the best excel forum I’ve seen)
Gerardo Nuño
San Diego, Ca.

[/URL][/IMG]

 

[MarcelBeug]

It’s not completely clear what is happening and what you are trying to achieve.

My interpretation would be as follows:

When you scan at the end of the process, you have the current date/time in A1 and the serial number in D1.

I guess you need the serial number in column D (Serial# Out) , next to the Serial# In (column B?).
If so you need in D4 and copied down:

=IF(B4=D1,B4,”Unit Not Ready”)

In the “Check-Out” column (G?) you need in G4 and copied down:

=IF(D4=D1,A1,””)

I guess the same for column “Check out for calc”?

In the “Cycle Time” column (I?), you need in I4 and copied down:

=G4-E4

I would recommend to apply conditional formatting to highlight the cells of the row corresponding with the unit just scanned, as you need to replace the formulas with fixed values after scanning (copy the cells with formulas and paste back with values only).

I hope this makes sense and it is helpful.

 

[geranuno]

Thank you very much for the answer Marcel! ... But in reality my concern is HOW to make 'C' column paste with values when <> "Units not Ready".

Right now is working almost perfect, except that when I enter a new serial number ... I erase the previous one and so.

Hope this two images works better to explain myself.
(If I can send you the file ... just share your e-mail ... or simply write me at gerardonuno@eaton.com)

Thanks again for the quick respond, time and dedication to support on this forum.

GN

 

[MarcelBeug]

Hi,

As I already mentioned, after you scan a unit, you need to replace the formulas with fixed values after scanning (copy the cells with formulas and paste back with values only).

This is to prevent to values from being overwritten by the next scan.

That's why I recommended to highlight the values that were just scanned, so they can be easily identified and copied / pasted with values only.

Hope this makes sense.

Cheers,
Marcel

 

[geranuno]

Hey Marcel ... Thanks again

This is a non-dependable operation, and the operator does not know how to use excel ....
Therefore we need to do that automatically. That's exactly what we need using formulas or a VBA macro that automatically convert the serial# to value.

Thanks again ... we almost there

Cheers from San Diego, California!

GN

 

[MarcelBeug]

You're welcome, but ... I'm not experienced with VBA so at this point you need someone else to help you further.

Otherwise I think we made the requirements pretty clear, so I think this should be pretty straightforward for a VBA expert.

Cheers from Lelystad, the Netherlands (about 50 kilometers north east of Amsterdam)!

 

[geranuno]

Thank You very Much Marcel!

I hope to go the Netherlands, specially to the capital

Appreciate your recommendations!

I'll wait to see if some VBA expert can help me here!

Thanks again and very nice to meet you

GN