Find time by name

[jaluht]

I am having trouble coming up with a formula to grab the first and last time scanned by name of scanner.

amoung other coloumns there is

F I K M
Scan Time Scanner ID Scanner ID First Time Scanned
2:45:00 ASL_12 ASL_12



assume that coloumn F and I have a large number of rows attached, perhaps 300+. What I need is to pull out the time of the first scan where coloumn K matches coloumn I, I have so far been unsuccessful. Can anyone help?

[Cheeky Charlie]

I can't tell which values are in which column, with a little more clarity (or an uploaded example) a formula should be fairly straightforward.

[jaluht]

Here is an attachment of the WKS I am using, hopefully this makes things more clear

[Cheeky Charlie]

The easiest solution for this is a pivot table:
Insert a dynamic named range for your dataset:
Alt, i, n, d (brings up named range interface)
New
Name: 'Scans'
Refers to: =$A$1:index($I:$I,counta($A:$A))
OK
Create a pivot:
Alt, d, p (brings up pivot table dialogue)
Next
Range: =Scans
Next
Existing worksheet, R1
Finish
Customise pivot:
Check in Scanner ID (adds to row fields)
First:
Drag 'Scanned' to values
Right-click 'Count of Scanned'
Value field settings
Summarize by min
Number format Time
Last: per first, except summarize by max
Scanned stations: Drag Scanner ID to Values

Done

[Kevin UK]

Hi jaluht

See the attached file. would this be of any help.

[kvsrinivasamurthy]

Try this in M2 and drag down.

Please Login or Register  to view this content.

[jaluht]

This worked excellent, thank you.

Now I seems to be encountering another problem. I am using a macro to make the pivot table which works fine, but I want to add a pivot chart and the macro keeps stalling because every time i enter new data it changes the chart name, ie Chart 1, Chart 2, etc. Is there any way to fix this problem?

[kvsrinivasamurthy]

Thanks for compliment .Pl mark the thread solved.

[Kevin UK]

@ kvsrinivasamurthy

This will give a incorrect result if a scanner is entered in row 25 with a earlier time! See the attached file.
=INDEX(F:F,MATCH(K2,I:I,0))

[Cheeky Charlie]

This worked excellent, thank you.

Now I seems to be encountering another problem. I am using a macro to make the pivot table which works fine, but I want to add a pivot chart and the macro keeps stalling because every time i enter new data it changes the chart name, ie Chart 1, Chart 2, etc. Is there any way to fix this problem?

Can you upload what you have? I wouldn't have thought you want multiple charts (may be wrong?) if not - an approach along the lines of:
(optionally) delete charts if the are already there - to avoid conflicts, but may be a problem depending on set up
Add new chart
Address it by index (slightly different approach depending on whether the chart is on-sheet or on its own tab) - basically the approach worksheets("x").charts(1) will always find the first chart - if there's only one, that will be reliable.

You might be able to follow the principles above, depending on your VBA skill, if not, I'd need an example to be more precise.

[kvsrinivasamurthy]

@ Kevin UK

THIS GIVES CORRECT RESULT

Please Login or Register  to view this content.