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]

My apologies for not being specific, each Opener has a corresponding Closer, the Opener begins the "gaming day" and the Closer ends it. The column named Gaming Date is working off a 2 am to 1:59 am clock while the Modified column is working on a calendar date clock - midnight to 11:59. The modified column will allow me to be able to know when to say the actual date has changed but the gaming date has not. The 24 hrs I need to chart (it can be slightly more) is then clearer for me.

In this case the 31st I refer to is the first one in the Modified column you've highlighted, it's on the same gaming day as shown by the column to the left of it but now I can see that the table was opened at 9:21 PM on the 30th and "Closed" on the 31st at 1:42 am giving me approximately 4 hours of open time on the 30th.

On the next day of business, "gaming date 31st" we see 4 entries, 2 of each, this signifies the table was opened at 1:42 am to start the new day and was closed at 3:14 am (the first Closer for that date). It was re-opened at 9:02 pm and closed at 1:38 am on the 1st of Nov.

This gives me approximately 1 hr and 42 min the first time and 4 hrs the second for a total of 5 hrs and 42 min of open time on the 31st. I need the sheet to understand when an opener matched a closer and put the correct entry in the correct column then do the math.

I saw another post you answered about adding a row into a sheet automatically and since the export removes the one I have in the source data I think if I added a row just above each Asset # I could use a "Spill" to pull the correct entries for each column and then be able to total them properly. My ultimate goal will be to use this continuously going forward to chart the updates on a weekly or monthly basis.

 

[Peter_SSs]

That sounds complicated to me, and confirms that all the expected results for BJ 405 were not shown in post 15

So, let's try the easier part ..

... if I added a row just above each Asset # ...

... Try this

Sub Insert_Rows()
  Range("A3", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).EntireRow.Insert
End Sub

 

[Frank J]

Sub Insert_Rows() Range("A3", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).EntireRow.Insert End Sub

I did this and it's not working, I just copied it and put it into the sheet's vba but I'm getting error messages

 

[Peter_SSs]

I just copied it and put it into the sheet's vba

It would normally go in a general module, not the sheet's module though that shouldn't be causing any issues that I can see.

I did this and it's not working, .... I'm getting error messages

Those pieces of information give me nothing to go on.
Some details of exactly what it does or does not do as well as exact error message(s)?

 

[Frank J]

Ok these are 3 shots of what I mean, 1st is where I put the code, 2nd is the "400" error and then a different error. in the 2nd pic you can see that on the right I'm formatting the data with the formula and only trying to grab the Open Hour Time and to the right the corresponding Close Time then to calculate the hours open.

 

[Peter_SSs]

1st is where I put the code,

I cannot tell from that image where you put the code but I don't think that is the issue.

2nd is the "400" error

What I can see from that image is that your data appears to be in a formal Excel table which we didn't know about before. Since you said this was being imported from a csv file, I wasn't expecting that. Have you created the table manually or is there other vba code doing that?
Could you insert the extra rows using my previous suggested code before creating the formal table?
Otherwise try

Sub Insert_Rows_v2()
  Dim cell As Range
  
  For Each cell In ActiveSheet.ListObjects(1).DataBodyRange.Columns(1).Offset(1).SpecialCells(xlConstants)
    cell.ListObject.ListRows.Add (cell.Row - 1)
  Next cell
End Sub

 

[Frank J]

Peter,

Apologies for being out of touch for so long but I came down with covid and have just now returned to work. I'll try the new code you sent me, thx. As for the data table yes I moved the csv file data to it but I'm not set on it being there before the rows are added but it seemed easier since the data isn't formatted well in the csv form.
I've found aother way to export the data in excel 97-2003 format

 

[Frank J]

This is the new format I can now pull in Excel 97-2003 in it's raw form. It shows my tables on the far left, the type of trans action (opener or closer) and the time stamp. I need to separate the openers to a column of their own and the closers to another of their own so I can figure out the hours each is open on a given day.

How can we modify the formula you gave to sequence the time stamp column to separate the openers which use Transaction Type ID 10000 and the closers which use 10001? This and the added line should allow me to create the needed data for each table in a horizontal format where each table has it's own columns moving across the spreadsheet

Asset #	Transaction Type	Transaction Type ID	Year	Month	Day	Gaming Date Time
BJ 405	Table Opener	10000	2020	11	1	11/01/2020 01:38:51
	Table Closer	10001	2020	11	1	11/01/2020 21:35:01
	Table Opener	10000	2020	11	2	11/02/2020 02:00:06
	Table Closer	10001	2020	11	2	11/02/2020 02:00:07
	Table Opener	10000	2020	11	3	11/03/2020 02:00:01
	Table Closer	10001	2020	11	3	11/03/2020 02:00:02
	Table Opener	10000	2020	11	4	11/04/2020 02:00:10
	Table Closer	10001	2020	11	4	11/04/2020 02:00:11
	Table Opener	10000	2020	11	5	11/05/2020 02:00:21
	Table Closer	10001	2020	11	5	11/05/2020 02:00:21
BJ 407	Table Opener	10000	2020	11	1	11/01/2020 01:40:01
	Table Closer	10001	2020	11	1	11/01/2020 02:25:03
	Table Opener	10000	2020	11	2	11/02/2020 02:00:07
	Table Closer	10001	2020	11	2	11/02/2020 02:00:07
	Table Opener	10000	2020	11	3	11/03/2020 02:00:02
	Table Closer	10001	2020	11	3	11/03/2020 02:00:02
	Table Opener	10000	2020	11	4	11/04/2020 02:00:11
	Table Closer	10001	2020	11	4	11/04/2020 02:00:11
	Table Opener	10000	2020	11	5	11/05/2020 02:00:21
	Table Closer	10001	2020	11	5	11/05/2020 02:00:22
BJ 702	Table Opener	10000	2020	11	1	11/01/2020 01:59:58
	Table Closer	10001	2020	11	1	11/01/2020 01:59:58
	Table Opener	10000	2020	11	2	11/02/2020 02:00:08
	Table Closer	10001	2020	11	2	11/02/2020 02:00:09
	Table Opener	10000	2020	11	3	11/03/2020 02:00:03
	Table Closer	10001	2020	11	3	11/03/2020 02:00:03
	Table Opener	10000	2020	11	4	11/04/2020 02:00:13
	Table Closer	10001	2020	11	4	11/04/2020 02:00:13
	Table Opener	10000	2020	11	5	11/05/2020 02:00:22
	Table Closer	10001	2020	11	5	11/05/2020 02:00:22

 

[Frank J]

I just used the new code you gave me and it looks like that did the trick, now I only need to separate the openers from the closers as I said.

 

[Peter_SSs]

Apologies for being out of touch for so long but I came down with covid ..

Sorry to hear that but I hope that you have made, or are making, a full recovery!

I just used the new code you gave me and it looks like that did the trick,...

From that I assume that the data in post 28 is again in a formal table (ListObject)

.... now I only need to separate the openers from the closers as I said.

Could you refresh my memory of just what that means by posting the sample data again but this time also include the expected results along with any clarification about how to obtain those results?