Seeking Excel Advice: Identifying Unpaired Names with Matching Timestamps?

Question

I am using excel and I want to do something like checking for Margin Repayment that doesn't have transaction sold pair with it in the exact same time. After it check, it can either tell me the line or highlight it for me. Like the picture I manually uploaded. I tried to ask GPT4 but I keep pasting the formula in it doesnt work.

Example 1:

Exact same time by the seconds. For example 2023-06-15 19:52 Doesn't have a Transaction Sold.

Alternatively I also dont mind if there is a formula to highlight the exact same time in different colours so its easier to see for me like this. See how if its the exact time by minute, it is grouped together and coloured in.

Example 2:

Would help a lot if I can pay someone to do it?

I tried asking ChatGPT 4 but it keep giving me the wrong answers, I paste in multiple formulas that it give me and none of them work. I only know the very common things in excel and not skilled at all so I always need to watch videos or ask AI for help but I dont really know how to search for videos with this type of title.

Example 3:

Answer 1

I think your question could be clearer but, on the understanding that something un-matched will necessarily occur only once (per time-stamp) and thus have an 'odd count' you could set a conditional format such as

=ISODD(COUNTIF($B$40:$B$79,$B40))

I have used this formula because, on the basis of your Example 2, all of the matched items have an even number of timestamps (4 from rows 40 - 43, 2 each from rows 51-52 and 54-55, and 14 from rows 68-81) but you could obviously change it to, e.g. highlight only rows where that timestamp occurs only once in that column.

The formula could also, of course, be made more complex, e.g. if Funding Fee items were not to be highlighted but that isn't something I can know since, as mentioned previously, your question could be clearer.

Answer 2

Short answer: conditional format the range $A:$F using:

=AND($A1<>INDEX($A:$A, ROW()-1), $A1<>INDEX($A:$A, ROW()+1))

Since you don't do much programming or formula writing ... I've actually written a longer solution. Maybe this will lead to something you can apply in the future, however.

I made up some sample data for testing. For the future, if you paste actual data (instead of images), it will just make it easier for others to test and write a real solution.

First, you can go through your data and first mark any that match a particular title (if you're looking for Margin Repayments in particular, for example). I've done that via Column C.

Using a similar method, then you can mark anything that has a time that doesn't match the previous row - that's my Column D.

And, you can mark anything the other way: time doesn't match the next row - my Column E.

You're looking for everything that is missing both Columns D and E, so I've done that in Column F. You can conditional format everything by testing against that column: for range $A:$F, apply =$F1<>"".

First image is the test data that I used - if this doesn't suit your case, my solution is no good! Second is the formulas used. Third is the conditional formatting formula.

You can actually do all this by just changing the conditional formatting formula - no need to add or use the spare columns. Having all that separated may be clearer and easier for you to implement, though.