Help clean file of unnecessary character in columns

Hi, would like to get help for columns B:J to be clean in columns L:T
for example I Did column B shows Date twice Jun 07, 11:45Jun 0711:45 and on L now shows only Jun 07, 11:45 manually corrected.

biggest issue here is I want column C tournament and D entry to just return 1 Row per Entry as you can see row 3 & 4 i entry 2 times showing
Jun 07, 11:00Jun 0711:00 356-L: $2.50 Friday Night Mini Fight [Bounty 6-Max]Buy-in $2.50 Prize $25,371.52 10,936
Jun 07, 11:00Jun 0711:00 356-L: $2.50 Friday Night Mini Fight [Bounty 6-Max]Buy-in $2.50 Prize $25,371.52 10,936 #2
I manually corrected and input in row 2

Note: would be great if row 2 M column tournaments has buy-in players and Total prize if it can fill columns O:Q
For Prize column T i am using formula
=IF(ISNUMBER(SEARCH("$",J2)),MID(J2,2,SEARCH(" ",J2)-SEARCH("$",J2))+0,"")

Should "356-L: $2.50 Friday Night Mini Fight [Bounty 6-Max]Buy-in $2.50 Prize $25,371.52 10,936" be in cell M2 or in cell M3?
I ask because cell L2 displays "Jun 07, 11:45" which is the start time for "Mini Hypersonic $2.50Buy-in $2.50 Prize $2,734.70 1,189" while cell L3 displays "Jun 07, 11:00" which is the start time for "356-L: $2.50 Friday Night Mini Fight [Bounty 6-Max]Buy-in $2.50 Prize $25,371.52 10,936".

Good morning, Thank you
yes 356-L: $2.50 Friday Night Mini Fight [Bounty 6-Max]Buy-in $2.50 Prize should be in row 3
and Mini Hypersonic $2.50Buy-in $2.50 Prize $2,734.70 should be in row 2
that is correct ty.
I think this would be the correct way, not sure if O:Q can be pulled from M for buy-in players and total prize.

Column K is populated as follows to indicate the row of data that should be copied: =IF(AND(B2<>B1,C2<>C1),1,0)
The foundational formula used to populate columns M:U is similar to: =IF($M2="","",INDEX(C$2:C$215,AGGREGATE(15,6,(ROW(C$2:C$215)-ROW(C$1))/($K$2:$K$215=1),ROWS(N$2:N2))))
There are variations in columns M, O and U

thank you so much.
what you mean by variations in columns M, O and U?
In this file I added the Gross profit column is T column
can we get formula to calculate buy-in times the re-buy entrys minize the prize amt.
Can end time using duration be calculated to get $/Hr in column Z.
for I1 Can Starting Bankroll: & $1,000.00 be in the same Cells in I1?
Hi,
I think some columns are hidding let me re-upload
can we please try to knocked up as many stats as we can? in tournaments sheet.

Column M, O and U is populated using:

Formula:

=IFERROR(LEFT(INDEX(B$2:B$215,AGGREGATE(15,6,(ROW(B$2:B$215)-ROW(B$1))/($K$2:$K$215=1),ROWS(M$2:M2))),13),"")

Column O is populated using:

Formula:

=IF($M2="","",SUMPRODUCT((LEFT(B$2:B$215,13)=M2)*(C$2:C$215=N2)))

Column U is populated using:

Formula:

=IF(OR(M2="",ISERROR(SEARCH("$",INDEX(J$2:J$215,AGGREGATE(15,6,(ROW(J$2:J$215)-ROW(J$1))/($K$2:$K$215=1),ROWS(U$2:U2)))))),"",0+MID(LEFT(INDEX(J$2:J$215,AGGREGATE(15,6,(ROW(J$2:J$215)-ROW(J$1))/($K$2:$K$215=1),ROWS(U$2:U2))),SEARCH(" ",INDEX(J$2:J$215,AGGREGATE(15,6,(ROW(J$2:J$215)-ROW(J$1))/($K$2:$K$215=1),ROWS(U$2:U2))))),2,99))

Gross profits on the Tournaments sheet (column S): =[@Rebuys]*([@[Buy-in]])+N([@Prize])
$/Hr: =IF([@Duration]="","",[@[Gross Profit]]/([@Duration]*24))
The rest need further explanation and expected results (manually calculated).

thank you. question can End time populate from Duration column N?
can we work of this file. I paste formula for ITM. but seems off

Tournament file in post#4 seems to be off.
not sure how many are incorrect, I did notice that row 66 in v66 shows no value when J66 has $114.68 3rd
the start time is off too shows jun 02 in column M and in column be is Jun 05
reason why numbers seem off from all deposits total is 925.10 with the Rakeback of 38.10

RE post #7
For End Time: =IF([@Duration]="","",SUM(DATE(2024,MATCH(LEFT([@[Start Time]],3),Info!AJ$2:AJ$13,0),MID([@[Start Time]],5,2)),[@Duration]))
For the ITM column: =IF(AND([@[Gross Profit]]<>"",N([@Prize])>0),1,0)

My mistake, instead of for duration I should have said for End Time. (see edited post #9)

Here is the file.

thank you so much.
can we please fixed errors in file clean tournaments in post# 4?
is the end time correct? as the actual end time or is just returning the Duration time ?
because not sure i understand row 33 19:10 is 7:10 end time Jun 06 1:10 not sure is correct or if it should be 20:10 ?
will double check in tournaments time okay for row 21 1st game in pokercraft show
start end
Jun 07, 11:45 - Jun 07, 15:10

if we can solved this this will solved all other sheets time i think
mate I done tone of changes. please lets work of this file. i have almost all Start time end time but need your help. I i could i would do all but i can't i get lots lots of things i can do myself. some times trying to figure out 1 little thing in a fomrula take me all day not doing anything until i decide to move on or i won't get Nada done.
I want to talk about the Category as i fixed in some sheets but need to know few things I also enter the rakeback as it was per date credit to account but i want to know which way is better just enter in 1 date all or as per day credit here is the file with all changes.

If the formula in End Time can populate as picture Jun 07, 15:10 would be great. same for other sheets.

End Time formula:

Formula:

=IF([@Duration]="","",SUM(DATEVALUE(LEFT([@[Start Time]],6)),TIMEVALUE(RIGHT([@[Start Time]],5)),[@Duration]))

mate
that returns this format 2:40 (sat) do i change format ?
when i use h:mm (ddd) gives the above
would this be the correct to use [$-en-US]mmmm d, hh:mm
Thank you, how can i make rakeback time not show the seconds? in time

I don't think formula in post# 15 works for other sheets like spin & gold as Duration is only Minutes less than 30 minutes.

For End Time on the Spin & Gold sheet:

Formula:

=IF([@[Duration	]]="","",SUM(DATEVALUE(LEFT([@[Start Time]],6)),TIMEVALUE(RIGHT([@[Start Time]],5)),TIMEVALUE([@[Duration	]])))

this file don't have an end time, i was trying to see formula because the one in post # 17 stop working giving #value
I could not figure out the End Time for My Staking.
3 Questions
1. Can Starting bankroll column H & amt. in I be combine in column H only same for other sheets. "My staking sheet"
2. can G & H for FInishes be combine? in only 1 column.
3. would cash column in tournament & My staking sheet be needed for total bankrolls? or can they be delete?
not sure how it all would come in play yeah. but for sure now i see progress. is been a very long marathon.

Thank you JeteMc for all your help and support on this project

We been thru the jumps & hoops with this, project anyone else I think, would of told me Hit the road jack. ... Ty

Please show us the file that is producing the error message, as I don't see any circular reference error messages in the file attached to post #16.

no when i try to paste either tournament of spin and go to get the end time fixed in my staking i get the circular refrences in this file.

I don't see a circular reference message in the file.
Are you saying that you are trying to paste end times from the tournament sheet into the my staking sheet?
If so then you will need to paste special as values, because the end times column on the tournaments sheet is populated using formulas that reference the duration column and that would produce a circular reference.

yes, I get it when i try to paste from tournaments to my staking i think i always use special not sure about value let me try.
i was just looking for spin & gold Clean up file could not find out thru the whole thread. I am working in other things from GG like the Freebie and checking many other things i get inactive here. let me try the copy paste. I do know that cirular refrence is poping up In many sheets i think when i sort date by newest to oldest. brb. ty
not sure im doing this right when i click special i get this pop up oh,
the most little simple things drive me Nutts im going to end up @ the Cuckoo's Nest

RE: Post #22.
I don't believe that you should be getting that pop up when copying end times from the tournaments sheet and pasting special values to the my staking sheet.
Try the following:
1. Select cell G12 on the tournaments sheet
2. Press the Ctrl, Shift and down arrow keys
3. Press the Ctrl and c keys
4. Select cell F51 on the my staking sheet
5. Press the Ctrl, Alt and v keys
6. Press the Alt and v keys
7. Select OK at the bottom of the dialog box
Note that you may need to change the formatting of the newly pasted cells as you did in post #16.

thank you, I am having trouble with this.
okay i did the above steps i copy as the instruction
since all the formulas paste below i when and copy everything to the bottom where i did the steps
then delete all the above rows leaving the bottoms rows and gives the same result. #Value!
change format to [$-en-US]mmmm d, hh:mm
i get more issues doing this steps as i need to delete all my deposits rakeback due to formulas been paste below row 51 then copy all text to the bottom will look into this
in a bit see if i can fixed it in a different way

We would need to see a file that shows the #Value! errors as I am totally lost as to what you are doing.

I didn't see any circular reference errors; however, I did see #Value! errors in the end time column on the spin & gold sheet.
Changing the formula in the end time column as follows remedies that:

Formula:

=IF([@Duration]="","",SUM(DATEVALUE(LEFT([@[Start Time]],6)),TIMEVALUE(RIGHT([@[Start Time]],5)),[@Duration]))

thank you,
I get this pop up when trying to paste formula in post #28 into file in #27
not sure whats going on but in file in post # 27 spin & gold show #value! too
I Requested a transactions history from GG Poker to help have everything to the perfection as i would like to get all bankroll stats figure out hope we can do some progress
hope the file helps. this is the file.
mate, after looking at the file for some reason Battle Royale is in type as tournaments mixed tournaments with battle royale games. only spin & gold seem to be in is own type along with cash. Side bets is part of the spin & gold insurance SGI sit N G I insurance it works as if you hit some type of multiplier they refund back the buyi-in or you pay dobule.

Do you think putting in the Battler Royale within tournaments be a good idea? or keep separate? I am going to eliminate lots of the things that casue trouble like Insurance the side bet. for cash i think is also combine the rush cash and the Hold'em since they are cash games.
if we can combine i think we can eliminate 2 sheets the hold'em & battler royale. please let me know your thoughts on this

can you please make some kind of pivot table for file Players balance History so i can try to analize whats going on with loss profit and games
okay as i am looking into this file I see Tournaments, Battle royale, and my staking games all mixed. though i mention this. i see daily hypersonic $50 -9.53 and 16.08 under tounaments

On the spin & gold sheet select cell F10 and then select the small down arrow at the right side of the formula bar. You'll see that there is some text " Attached Files Attached Files" on the second row. Starting with the last of the closed parentheses select and delete everything else then change the formula so that it reads like in post #28.
The circular reference that I see on the spin & gold is for cell I1 which is =TODAY(). Don't know why that should produce the error message.

The circular reference in cell T10 is produced by the formula in cell F10. The rest of the cells in column F are populated manually. Delete the formula in cell F10 and that should remove the circular reference.

how about the formula to get the End time i don't see any formula in F column in My staking
I combine rush & cash All-In or Fold and Hold'em into 1 sheet into Rush & cash sheet.
can you see why is the End time returning #value! in Rush & cash sheet in E15, E16
please let me know if combining battle with tournaments sheet would be a good idea?
not sure if staking would also be good. i really don't want to have tournaments & my staking mix but i wan't your opinion on this.
can you please see my prop bets End time folumn F in MY Prop Bets

On the my staking sheet there can either be a formula in the end time column or the duration column but not both.
On the rush & cash sheet the reason that end time returned an error is because D15:D16 are actual dates whereas D13:D14 are text entries.
Change the formula in the end time column to read:

Formula:

=IF([@Duration]="","",SUM(IF(ISNUMBER([@[Start Time]]),[@[Start Time]],SUM(DATEVALUE(LEFT([@[Start Time]],6)),TIMEVALUE(RIGHT([@[Start Time]],5)))),[@Duration]))

Combining different types of entries is going to make the formulas complex and difficult to troubleshoot if the need arises at some point in the future.
If there is not a good reason to combine the sheets then I wouldn't.
At some point in the near future the file may become so large that some of the sheets may need to be converted to their own workbook which may be easier if they are not combined.

thank you, i when an paste formula in Rush & cash works just fine.
Yes i think it would make it very complex mixing the formulas after combining sheets best to leave as is.
but i had to ask as your opinion counts much to make everything work as it should. ty.
which formula should be use in my staking end Time to make it work as the other sheets?
Okay i know why the my staking was just so confusing now. just realize i had copy the start time and end time from cvs file. No wonder why why i would tell myself this is not working as the other. sorry some things just take me time. I will be keeping duration formula.
I would like to fixed the tournaments games as i know they are wrong from file in post #4
would it be very complex to have
starting bankroll: $1,000.00 in 1 cell I1 and FInishes in only H Column combining G&H ?

You cannot use a formula for the end time column on the my staking sheet because the duration column is populated using a formula except for cell T10.
Please be specific about what is wrong with the tournaments column in the file attached to post #4.
Please be specific about which file and sheet when asking about combining columns G&H.

Thank you,
I understand what the issue was for my staking its been fixed now.
for file in post#4
row 66 is showing wrong start time in column M June 02 11:30, when in column B is Jun 05, 17:05 and in U66 has not amt. value of 114.68
not sure which others are the same or if any other issue. will check some more.
for G&H My Staking sheet but i want to do other if is possible. For Starting Bankroll and amt. $1,000.00 can be in cell H1 so it won't be to far apart.
and same for Finishes in tournament sheet. to G & H column to be in 1 Column

From post #1: I want column C tournament and D entry to just return 1 Row per Entry as you can see row 3 & 4 i entry 2 times showing

The data from the Jun 05, 17:05 game is in row 31 of the file attached to post #4.
I do see what the problem is with both the rank and prize columns, just not sure how to fix it at the moment.
On the Tournaments sheet
Paste the following into cell H1: ="Starting Bankroll: "&Info!B3
You can then select cells I1:J1 and press the delete key.
Paste the following into cell H3: ="1st "&COUNTIFS(tbl_Tournaments[Rank],1,tbl_Tournaments[Players],">=20")
You can then select cell G3 and press the delete key.
Follow the pattern to fill in cell H4:H9
Please do not post a new request until I/someone has a chance to work out a solution for the file in post #4.

In your first post you said that you combined the entries in rows 3 & 4 into row 2.
Why didn't you put the entry from C2:J2 into M2:T2 and the entries from C3:J4 into row 3?
Does it have anything to do with a dollar amount of in the My Prize column?

I believe that changing the formula in column K does what you want:

Formula:

=IF(COUNTIFS(B$2:B$215,B2,C$2:C$215,C2)=1,1,IF(AND(SUMPRODUCT((B$2:B$215=B2)*(C$2:C$215=C2)*(ISNUMBER(SEARCH("$",J$2:J$215))))>0,ISNUMBER(SEARCH("$",J2))),1,IF(AND(SUMPRODUCT((B$2:B$215=B2)*(C$2:C$215=C2)*(ISNUMBER(SEARCH("$",J$2:J$215))))=0,B2<>B1,C2<>C1),1,0)))

thank you. will download and see
nice will have to copy and paste all to file see if i notice anything as is hard for me to be going thru everything when i have ton of other things i have to arrange and check.
I want your opinion on lot of other things before i start doing anything else so like that we on the same page about how or which is the best way to go with.
in stats like ROI $/hr BB/100 in rush & cash an others that are similiar
not sure gross profit is correct after pasting new games in tournament sheet. only saw row 45 is tireing having to redo the deposits every time i need to paste data not sure if it could be a better set up.

What is wrong with the gross profit?
Please specify the sheet, expected amount and how it would be manually calculated.

Change the formula in the gross profit column to read: =N([@Prize])-[@Rebuys]*[@[Buy-in]]

The current formula on the tournaments sheet includes the previous bankroll amount, gross profit, and adjustment (which may not be needed).
I removed the #REF! argument from the formula so that it reads:

Formula:

=SUM(INDEX(tbl_PokerStake[[#All],[Bankroll]],ROW()-9),[@[Gross Profit]],IF(Y$8="Include Adjustment",[@Adjustment],0))

Paste the formula into cell Z10 and see if it yields the correct results.

thank you,
not sure is working the syntax name pops
not sure where the formula goes. you mention tournaments sheet. i try in z10 in tournament sheet. but i get that msg
also i see tbl_PokerStake in formula so not sure

I should have said cell Z10 on the my staking sheet.

okay that work ty.
i really would like a bettter set up for the deposit. I am looking into the deposits from original file results_monitoring
i am doing all the entries from all games in that file to see how everything work from deposit and transactions rake games works. as its hard to keep up with the games and having to delete enter the deposits rake again some times i forget and paste or delete over the rows and just cause to much trouble.
in this file i think i have pretty much all games play in history cvs file i have to check again since it has a balance of 168.86 not sure why
file is over size i can't

https://docs.google.com/spreadsheets...f=true&sd=true
the way I input this was in Satellites column D i input Spin & Gold
tournaments in tournaments, G column
Sit N Gos I input battle royale & my staking K column
Cash is cash and All-In-or fold and prop bets
In results monitoring file if you take the amt. 51.94 from investors. pay to help with buying in games in sem straight x
168.86 - 51.94 = 116.92 balance almost as deposit amt. of todays. of 110.92

(Received in a PM): not sure if you saw my last post of the original file.

Yes, but I don't know what it is that you want done.

okay, i will take off tomorrow and work on the entries. i want to see how long it takes me as is hard to do it when im on the grind.
as many things are still complicated as the deposits is the main thing i want to figure out. let me work on all entries and we go from there.
we have so much progress in a lot of things thru out the workbook that only needs adjustments.

Latest file with most recent updates.