Find a match to a specific value and search a list in a column, return data and stop at first blank cell

[Frank J]

I'm trying to use a specific value in Column A (Table: 01-DD102 for example) to find the related values in column C using the unique Trans ID. I need to return the values in the column in the same descending order then stop when I hit a blank cell so I can find the next value in column A using the same type of value from column C to return the next set of values from that column and stop at the next blank cell, and so on.

I'll then use those values to index match the Voucher Amount, Fee Amount and Total Amount columns for each Trans ID.

Sample data

 

[Frank J]

Here is my example, you can see the added row between the Asset # and the end of the one above's data. To the right side is how I want to format each Table with a column for Openers and one for Closers. Then there would be a column for the Open Hours that minuses the Open Time from the Close Time to give me that figure on each row.

Each of the Openers and Closers have a transaction type, 10000 for Openers and 10001 for Closers. I'd like only those Openers (10000) in the column labeled "Table Opener" and only those Closers (10001) in the column labeled "Table Closer". The formula you gave me doesn't allow for this, can it be modified to do so? If it can I believe I'm set.

Asset #	Transaction Type	Transaction Type ID	Year	Month	Day	Gaming Date Time	Modified Date Time		Table ID	Table Opener	Table Closer	Open Hrs
BJ 405	Table Opener	10000	2020	11	1	11/01/2020 01:38:51	11/01/2020 01:38:51		BJ 405	11/01/2020 01:38:51	11/01/2020 01:38:51	19:56:10
	Table Closer	10001	2020	11	1	11/01/2020 21:35:01	11/01/2020 21:35:00			11/01/2020 21:35:01	11/01/2020 21:35:01	Ideally the above should also be on
	Table Opener	10000	2020	11	2	11/02/2020 02:00:06	11/03/2020 02:00:06			11/02/2020 02:00:06	11/02/2020 02:00:06	every row for each combination
	Table Closer	10001	2020	11	2	11/02/2020 02:00:07	11/03/2020 02:00:06			11/02/2020 02:00:07	11/02/2020 02:00:07	of Opener and Closer
	Table Opener	10000	2020	11	3	11/03/2020 02:00:01	11/04/2020 02:00:01			11/03/2020 02:00:01	11/03/2020 02:00:01
	Table Closer	10001	2020	11	3	11/03/2020 02:00:02	11/04/2020 02:00:01			11/03/2020 02:00:02	11/03/2020 02:00:02
	Table Opener	10000	2020	11	4	11/04/2020 02:00:10	11/05/2020 02:00:10			11/04/2020 02:00:10	11/04/2020 02:00:10
	Table Closer	10001	2020	11	4	11/04/2020 02:00:11	11/05/2020 02:00:10			11/04/2020 02:00:11	11/04/2020 02:00:11
	Table Opener	10000	2020	11	5	11/05/2020 02:00:21	11/06/2020 02:00:20			11/05/2020 02:00:21	11/05/2020 02:00:21
	Table Closer	10001	2020	11	5	11/05/2020 02:00:21	11/06/2020 02:00:21			11/05/2020 02:00:21	11/05/2020 02:00:21
BJ 407	Table Opener	10000	2020	11	1	11/01/2020 01:40:01	11/01/2020 01:40:00
	Table Closer	10001	2020	11	1	11/01/2020 02:25:03	11/01/2020 02:25:02
	Table Opener	10000	2020	11	2	11/02/2020 02:00:07	11/03/2020 02:00:06
	Table Closer	10001	2020	11	2	11/02/2020 02:00:07	11/03/2020 02:00:07
	Table Opener	10000	2020	11	3	11/03/2020 02:00:02	11/04/2020 02:00:01
	Table Closer	10001	2020	11	3	11/03/2020 02:00:02	11/04/2020 02:00:01
	Table Opener	10000	2020	11	4	11/04/2020 02:00:11	11/05/2020 02:00:10
	Table Closer	10001	2020	11	4	11/04/2020 02:00:11	11/05/2020 02:00:10
	Table Opener	10000	2020	11	5	11/05/2020 02:00:21	11/06/2020 02:00:21			^^^^^	^^^^^	^^^^^
	Table Closer	10001	2020	11	5	11/05/2020 02:00:22	11/06/2020 02:00:21			This column needs to only display the Table Opener transaction from the 2nd column in the data table to the left.	This column needs to only display the Table Closer transaction from the 2nd column in the data table to the left.	To arrive at the Open Hrs above I used the actual Close time from the 2nd row to calculate. The column showing here for Table closer is just filled with the info from Gaming Date on the Left. Neither of the Table Openers or Closers are filtering them to the proper column as he formula doesn't separate the Transaction Types yet.

 

[Peter_SSs]

Thanks for the above but I'm afraid that it raises some more questions.

1. In this sample data, the second column is very sequential Opener, Closer, Opener, Closer, Opener, Closer, ...
   In your earlier data (eg post 15) that column was not always sequential like that
   
   
   
   
   What is the actual circumstance?
   If it is like post 15, then could we have some new sample data and expected results that shows the sorts of variation that might actually occur?
   
   
2. In the expected results above, why is the Table Closer column at the right identical to the Table Opener column at the right?
   Is that what you actually want?

 

[Frank J]

Thanks for the above but I'm afraid that it raises some more questions.

1. In this sample data, the second column is very sequential Opener, Closer, Opener, Closer, Opener, Closer, ...
   In your earlier data (eg post 15) that column was not always sequential like that
   View attachment 28904
   
   What is the actual circumstance?
   If it is like post 15, then could we have some new sample data and expected results that shows the sorts of variation that might actually occur?
   
   
2. In the expected results above, why is the Table Closer column at the right identical to the Table Opener column at the right?
   Is that what you actually want?

1. That data was sorted differently and the new data I sent is how it comes in the report. In either case if we could use the Transaction Type ID as a 2nd match to separate the openers from the closers it would keep them in the order of occurrence.

2. The columns are identical because I just place the same formula there and it pulls the same data as the opener column. I don't know how to separate them.

 

[Peter_SSs]

I'm afraid that I am not really 'getting' this.
In the left table there are 5 'Table Opener' rows for Asset BJ 405.
In the right table there are 10 'Table Opener' rows for that asset.