Match/Index problem

[Cyrius]

I am having a problem with Match/Index results.


In my first set of data, I was able to pull all the data that matched 2 criteria using Match/Index with out any problem using this formula, (Each data entry was unique.)


=INDEX(DATA1,MATCH(DATA2,IF(RANGE1=DATA, RANGE2),0),1)


However, my second set of data had repeated entries. Each of these entries has a time stamp happening within the same day (see the example below)

Process Date/Time Product

1 3/1/2010 8:01 AM 1540

2 3/1/2010 8:10 AM 1541

1 3/1/2010 8:02 AM 1540

3 3/1/2010 8:15 AM 1542

1 3/2/2010 8:33 AM 1540


I need to pull what process number (1,2 or 3) product 1540 used on each date. There is usually only one process per product per day, but with multiple time stamps. I have the dates on separate columns and the list of products on rows. I need to analyze 6 months worth of data like these.


A pivot table sort of does the trick, but I still need to have the data displayed per day horizontally, like described in the paragraph above.


Thanks all for your help

 

[Hui]

Cyrius

You could add a couple more helper column breaking the date down to just the Date and a second column for the Time

And then keep going with the Pivot Tables

You can group Dates and Times as appropriate

or use Sumproduct to count the number of items and fill out a

[pre]________Proc 1  Proc 2 Proc 3
Date 1
Date 2
Date 3

[/pre]
Matrix

 

[Cyrius]

Thanks Hui


But my answer needs to be in this format

_________3/1/2010_____3/2/2010_______3/3/2010

Product 1_____1_________1

Product 2_____2

Product 3_____


I need to find out what process they used on each date


I've tried the approach of the helper column before. WHen I did, I was able to pull the data and ended up like this,

___________3/1/2010_______3/2/2010

Product 1___8:01 AM_______8:02 AM

Product 2___8:20 AM


The criteria search becomes the time. My goal is to pull from the process column and not the time, this is the reason why I used match/index

 

[Hui]

Can you post a sample of data somewhere?

 

[Cyrius]

Hui,


Here is the file


http://www.divshare.com/download/11879585-b52

 

[Cyrius]

I posted the wrong link. This is the correct one,


http://www.divshare.com/download/11879617-089

 

[Hui]

Cyrius

Can't you use a Pivot Table with Dates as Column Headings and set Group to day

and set Prod, Prod Code or Machine to the Row Header (I'm unsure which you want as all your examples above used Proc)

and Set the Sum values field to Date and change it to Count instead of Sum

 

[Cyrius]

I am trying to solve it without pivot tables since I get data like this all the time. I tried your approach, but I could not arrange properly the way you propose it.


I tried this other approach to obtain the machine use only from the lowest time stamp but no luck either.


=SMALL(MATCH(M1,INT(Table2[date]),0),1)


..."I'm unsure which you want as all your examples above used Proc", I labeled it machine but actually it is a procedure conducted in different machines. Sorry for the confusion.