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

#### Frank J

##### Board Regular
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

##### Board Regular
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.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
AB
1Table of InterestTrans IDs
2Table: 01-DD1022220
32197
42196
52159
6
7
Sheet2
Cell Formulas
RangeFormula
B2:B5B2=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

##### Board Regular
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.

You're welcome.

#### Frank J

##### Board Regular

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

##### MrExcel MVP, Moderator
Can you post the table again with all the expected results for BJ 405?

#### Frank J

##### Board Regular

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

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### MrExcel MVP, Moderator
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
ABCDEFGH
1Asset #Transaction TypeTransaction Type IDYearMonthDayGaming Date TimeModified Date Time
2BJ 405Table Opener100002020103010/30/2020 21:10:2610/30/2020 21:10:26
3Table Closer100012020103010/30/2020 1:42:1810/31/2020 1:42:17
4Table Opener100002020103110/31/2020 1:42:1910/31/2020 1:42:18
5Table Opener100002020103110/31/2020 21:02:0610/31/2020 21:02:05
6Table Closer100012020103110/31/2020 3:14:3810/31/2020 3:14:37
7Table Closer100012020103110/31/2020 1:38:5111/01/2020 1:38
Sheet3

Replies
2
Views
1K
Replies
9
Views
188
Replies
11
Views
92
Replies
3
Views
189
Replies
14
Views
221

1,128,016
Messages
5,628,150
Members
416,296
Latest member
smartua

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back