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

F

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
104
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
 
Sort by date Sort by votes
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 TypeTransaction Type IDYearMonthDayGaming Date TimeModified Date TimeTable IDTable OpenerTable CloserOpen Hrs
BJ 405Table Opener10000202011111/01/2020 01:38:5111/01/2020 01:38:51BJ 40511/01/2020 01:38:5111/01/2020 01:38:5119:56:10
Table Closer10001202011111/01/2020 21:35:0111/01/2020 21:35:0011/01/2020 21:35:0111/01/2020 21:35:01Ideally the above should also be on
Table Opener10000202011211/02/2020 02:00:0611/03/2020 02:00:0611/02/2020 02:00:0611/02/2020 02:00:06every row for each combination
Table Closer10001202011211/02/2020 02:00:0711/03/2020 02:00:0611/02/2020 02:00:0711/02/2020 02:00:07of Opener and Closer
Table Opener10000202011311/03/2020 02:00:0111/04/2020 02:00:0111/03/2020 02:00:0111/03/2020 02:00:01
Table Closer10001202011311/03/2020 02:00:0211/04/2020 02:00:0111/03/2020 02:00:0211/03/2020 02:00:02
Table Opener10000202011411/04/2020 02:00:1011/05/2020 02:00:1011/04/2020 02:00:1011/04/2020 02:00:10
Table Closer10001202011411/04/2020 02:00:1111/05/2020 02:00:1011/04/2020 02:00:1111/04/2020 02:00:11
Table Opener10000202011511/05/2020 02:00:2111/06/2020 02:00:2011/05/2020 02:00:2111/05/2020 02:00:21
Table Closer10001202011511/05/2020 02:00:2111/06/2020 02:00:2111/05/2020 02:00:2111/05/2020 02:00:21
BJ 407Table Opener10000202011111/01/2020 01:40:0111/01/2020 01:40:00
Table Closer10001202011111/01/2020 02:25:0311/01/2020 02:25:02
Table Opener10000202011211/02/2020 02:00:0711/03/2020 02:00:06
Table Closer10001202011211/02/2020 02:00:0711/03/2020 02:00:07
Table Opener10000202011311/03/2020 02:00:0211/04/2020 02:00:01
Table Closer10001202011311/03/2020 02:00:0211/04/2020 02:00:01
Table Opener10000202011411/04/2020 02:00:1111/05/2020 02:00:10
Table Closer10001202011411/04/2020 02:00:1111/05/2020 02:00:10
Table Opener10000202011511/05/2020 02:00:2111/06/2020 02:00:21^^^^^^^^^^^^^^^
Table Closer10001202011511/05/2020 02:00:2211/06/2020 02:00:21This 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.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
    1609545791200.png


    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?
 
Upvote 0
Peter_SSs said:
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?
Click to expand...
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.
 
Upvote 0
I'm afraid that I am not really 'getting' this. :confused:
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
How to return closest value based on two criteria?
Replies
6
Views
417
Peter_SSs
Peter_SSs
M
  • Question Question
Cell with 0 value showing blank
2
Replies
15
Views
803
mhessnm
M
R
  • Question Question
Look for a cell value then return row header
Replies
9
Views
94
Cubist
Cubist
J
  • Question Question
vba to find specific text in a column and then when found copy all the rows above it
Replies
1
Views
226
mumps
mumps
A
  • Solved
referenced blank cell returned zero (0) as values
Replies
5
Views
528
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top