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
Joined
Feb 17, 2011
Messages
93
Office Version
  1. 365
Platform
  1. Windows
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
Sample Data.png
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
93
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
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
Joined
Feb 17, 2011
Messages
93
Office Version
  1. 365
Platform
  1. Windows
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.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
93
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 TypeTransaction Type IDYearMonthDayGaming Date TimeModified Date TimeTable IDTable OpenerTable CloserOpen Hrs
BJ 405Table Opener100002020103010/30/2020 21:10:2610/30/2020 21:10:26BJ 40510/30/2020 21:10:2610/30/2020 21:10:26
Table Closer100012020103010/30/2020 1:42:1810/31/2020 1:42:17
Table Opener100002020103110/31/2020 1:42:1910/31/2020 1:42:18
Table Opener100002020103110/31/2020 21:02:0610/31/2020 21:02:05
Table Closer100012020103110/31/2020 3:14:3810/31/2020 3:14:37
Table Closer100012020103110/31/2020 1:38:5111/01/2020 1:38:50
BJ 407Table Opener100002020103010/30/2020 22:02:1410/30/2020 22:02:13
Table Closer100012020103010/30/2020 1:44:1710/31/2020 1:44:16
Table Opener100002020103110/31/2020 1:44:1710/31/2020 1:44:17
Table Opener100002020103110/31/2020 21:09:2610/31/2020 21:09:25
Table Closer100012020103110/31/2020 3:10:2810/31/2020 3:10:27
Table Closer100012020103110/31/2020 1:40:0111/01/2020 1:40:00
BJ 702Table Opener100002020103010/30/2020 2:00:5910/31/2020 2:00:59
Table Closer100012020103010/30/2020 2:00:5910/31/2020 2:00:59
Table Opener100002020103110/31/2020 2:08:5511/01/2020 2:08:55
Table Closer100012020103110/31/2020 2:08:5511/01/2020 2:08:55
BP 106Table Opener100002020103010/30/2020 4:03:4210/30/2020 4:03:42
Table Closer100012020103010/30/2020 0:04:5110/31/2020 0:04:50
Table Opener100002020103110/31/2020 11:59:5010/31/2020 11:59:49
Table Closer100012020103110/31/2020 23:32:3710/31/2020 23:32:36
BP 205Table Opener100002020103010/30/2020 1:27:5110/30/2020 1:27:50
Table Closer100012020103010/30/2020 1:32:3410/31/2020 1:32:33
Table Opener100002020103110/31/2020 1:32:3510/31/2020 1:32:33
Table Opener100002020103110/31/2020 1:26:0111/01/2020 1:25:59
Table Closer100012020103110/31/2020 1:25:3211/01/2020 1:25:30
Table Closer100012020103110/31/2020 1:26:5911/01/2020 1:26:58
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Can you post the table again with all the expected results for BJ 405?
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
93
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 17, 2011
Messages
93
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top