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]

Yes that's about it, I can do it in 2 steps where I import the data from the other sheet and then have the sheet with your formula read it and fill the formatted data in. I can have it brought to the same sheet as the formula if that's what's needed.
In the end the data I collect will be added to another set of data to be analyzed to find performance of each table per hour.

 

[Peter_SSs]

Yes that's about it,

Then you should be able to have Sheet2 set up something like this. You just need to make sure where I have 100 in this formula you use a row number that will be big enough to always cover the data that is going to be placed into Sheet1.

Frank J.xlsm
	A	B
1	Table of Interest	Trans IDs
2	Table: 01-DD102	2220
3		2197
4		2196
5		2159
6
7
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX(Sheet1!C1:C100,SEQUENCE(MATCH(TRUE,INDEX(Sheet1!C1:C100,MATCH(A2,Sheet1!A1:A100,0)+2):Sheet1!C100="",0)-1,,MATCH(A2,Sheet1!A1:A100,0)+2))
Dynamic array formulas.

 

[Frank J]

As soon as I can get to it I'm going to try and I'll let you know. Thx so much for all the time and effort to help me.

 

[Peter_SSs]

You're welcome.

 

[Frank J]

I'm working on a 2nd issue with another problem and wondered if I could adapt the formula to use 2 criteria? In this one I'm trying to pull 2 types of transactions into 2 different columns from the data imported. In my example column B "Transaction Type" I have "Table Opener" and "Table Closer" I want to pull them to columns on the right named accordingly and have it do the same type of spill for each transaction of each type in the appropriate column. At the moment I can't get it to work.
My end result will be to calculate the hours a table is open by subtracting the Open time from the Close time for each instance.

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	10	30	10/30/2020 21:10:26	10/30/2020 21:10:26	BJ 405	10/30/2020 21:10:26	10/30/2020 21:10:26
	Table Closer	10001	2020	10	30	10/30/2020 1:42:18	10/31/2020 1:42:17
	Table Opener	10000	2020	10	31	10/31/2020 1:42:19	10/31/2020 1:42:18
	Table Opener	10000	2020	10	31	10/31/2020 21:02:06	10/31/2020 21:02:05
	Table Closer	10001	2020	10	31	10/31/2020 3:14:38	10/31/2020 3:14:37
	Table Closer	10001	2020	10	31	10/31/2020 1:38:51	11/01/2020 1:38:50
BJ 407	Table Opener	10000	2020	10	30	10/30/2020 22:02:14	10/30/2020 22:02:13
	Table Closer	10001	2020	10	30	10/30/2020 1:44:17	10/31/2020 1:44:16
	Table Opener	10000	2020	10	31	10/31/2020 1:44:17	10/31/2020 1:44:17
	Table Opener	10000	2020	10	31	10/31/2020 21:09:26	10/31/2020 21:09:25
	Table Closer	10001	2020	10	31	10/31/2020 3:10:28	10/31/2020 3:10:27
	Table Closer	10001	2020	10	31	10/31/2020 1:40:01	11/01/2020 1:40:00
BJ 702	Table Opener	10000	2020	10	30	10/30/2020 2:00:59	10/31/2020 2:00:59
	Table Closer	10001	2020	10	30	10/30/2020 2:00:59	10/31/2020 2:00:59
	Table Opener	10000	2020	10	31	10/31/2020 2:08:55	11/01/2020 2:08:55
	Table Closer	10001	2020	10	31	10/31/2020 2:08:55	11/01/2020 2:08:55
BP 106	Table Opener	10000	2020	10	30	10/30/2020 4:03:42	10/30/2020 4:03:42
	Table Closer	10001	2020	10	30	10/30/2020 0:04:51	10/31/2020 0:04:50
	Table Opener	10000	2020	10	31	10/31/2020 11:59:50	10/31/2020 11:59:49
	Table Closer	10001	2020	10	31	10/31/2020 23:32:37	10/31/2020 23:32:36
BP 205	Table Opener	10000	2020	10	30	10/30/2020 1:27:51	10/30/2020 1:27:50
	Table Closer	10001	2020	10	30	10/30/2020 1:32:34	10/31/2020 1:32:33
	Table Opener	10000	2020	10	31	10/31/2020 1:32:35	10/31/2020 1:32:33
	Table Opener	10000	2020	10	31	10/31/2020 1:26:01	11/01/2020 1:25:59
	Table Closer	10001	2020	10	31	10/31/2020 1:25:32	11/01/2020 1:25:30
	Table Closer	10001	2020	10	31	10/31/2020 1:26:59	11/01/2020 1:26:58

 

[Peter_SSs]

Can you post the table again with all the expected results for BJ 405?

 

[Frank J]

It's there above and to the right, need a column of the Table Opener transaction, Table Closer, and then calculate the Open hours. Some days will have multiple openers and closers due to our business.

 

[Peter_SSs]

It's there above and to the right,

Is it? You talked about something "spilling for each transaction of each type". I can't see what has spilled at the top right.
Also, I cannot see how you get 10/30/2020 21:10:26 for Table Closer. Can you explain?

 

[Frank J]

It’s not spilling g the opener column, I don’t know how to make it to do that. The blank line that appears in the report is removed when I export it so it’s continuous as shown. I want to get the correct return for the closer which is the line below the opener as you can see on the left. It’s returning the first entry in the modified column where the closer info will come from to give me the correct date.

it should read the 31st entry for the closer

 

[Peter_SSs]

It’s not spilling g the opener column, I don’t know how to make it to do that.

That again makes me think that you have not shown the full expected results for BJ 405 in post 15?

it should read the 31st entry for the closer

Which 31st entry? I can see four different 31st dates for Table Closer for BJ 405

Frank J.xlsm
	A	B	C	D	E	F	G	H
1	Asset #	Transaction Type	Transaction Type ID	Year	Month	Day	Gaming Date Time	Modified Date Time
2	BJ 405	Table Opener	10000	2020	10	30	10/30/2020 21:10:26	10/30/2020 21:10:26
3		Table Closer	10001	2020	10	30	10/30/2020 1:42:18	10/31/2020 1:42:17
4		Table Opener	10000	2020	10	31	10/31/2020 1:42:19	10/31/2020 1:42:18
5		Table Opener	10000	2020	10	31	10/31/2020 21:02:06	10/31/2020 21:02:05
6		Table Closer	10001	2020	10	31	10/31/2020 3:14:38	10/31/2020 3:14:37
7		Table Closer	10001	2020	10	31	10/31/2020 1:38:51	11/01/2020 1:38
Sheet3