Help needed! 2 Columns, a search box and no link between values is causing me problems!

[Lordkit1]

This is honestly causeing me a headache! I know just enough to maybe toy with a solution but not enough to fix it. So here's the setup:

I have 2 columns that are going to fill up via userform entry which log the date a call was placed and the time and duration of the call (in the same column, don't ask me why the guy who orriginally made this is a numpty of the highest order.) Now I can't seperate these columns as the data kept accross multiples of this sheet is in depth and data loss is NOT an option. I've been delt a bad hand and presently i'm rolling with it. thankfully i can draw just the first 5 characters of the 2nd column to get the time as it is always in "00:00" format.

Back on topic, 2 columns with data. Presently there is a massive issue with duplication of entries and the creator of the sheet's answer to this was to highlight multiple entries. that's all well and good but some people spend 30 minuites listening to a call only to be told AFTER they've worked on a report that it's a duplicate.

My solution was a search box. Essentially you have the date and time of a call presented before you log it and can search the written entries to screen them for any calls made on the time and date you specify.

I did this with 2 boxes where you enter the date and time you are screening for and then a third box runs the following code to check the columns... i'm sure smarter people then me can see where this is going...

=IF(OR(J4="",J3=""),"result will display here.",IF(AND( COUNTIF( F7:F300000, J3 )>=1, COUNTIF( G7:G300000, "*" & J4 & "*" )>=1 ), "that call has already been logged","That call is a new entry"))

long story short if the date entered in J3 is in column F and the time entered in J4 is in column G, regardless of if they're from the same log, it returns "that call has already been logged".
------------------------------------------------------------
Example:

Date (F) - time (G)
12/12/2021 - 03:05
21/11/2021 - 04:06

J3 - 12/12/2021
J4 - 04:06
Result: "That call has already been logged."
------------------------------------------------------------
but it hasn't. anyone how i could fix this without having to delve into the mire that is the creator of this sheet's VBA code. (Seriously it hurts me.)

The other option i though of is that J4 becomes a drop down box which displays the cells next to any cell in column F that matches the value in J3.

honestly I'm really lost as to how to procceed here and any help would be appreciated. Appologies for any spelling mistakes it's been a long day and my head is getting real fuzzy!

 

[Toadstool]

Hi Lordkit1,

Excel 2013 should have COUNTIFS available which will check bot conidtions are satisfied for a row so you should be able to use COUNTIFS.

You say the time is held as time followed by duration so I assume it's a text field.

Lordkit1.xlsx
	F	G	H	I	J
1	Date	Time & duration
2	12-Dec-21	03:05 8:9
3	21-Nov-21	04:06 47:37		Date	12-Dec-21
4	09-Sep-21	12:04 16:7		Time	4:06
5	12-Dec-21	04:06 22:36		Result	that call has already been logged
6	21-Nov-21	04:06 35:6
7	12-Dec-21	03:05 27:22
8	21-Nov-21	04:06 33:44
Sheet1

Cell Formulas
Range	Formula
J5	J5	=IF(OR(J4="",J3=""),"result will display here.",IF(COUNTIFS($F$2:$F$300000,J3,$G$2:$G$300000,"*"&TEXT(J4,"hh:mm")&"*")>0,"that call has already been logged","That call is a new entry"))

 

[Lordkit1]

Hi Lordkit1,

Excel 2013 should have COUNTIFS available which will check bot conidtions are satisfied for a row so you should be able to use COUNTIFS.

You say the time is held as time followed by duration so I assume it's a text field.

I should have been more exact! Apologies! Your code looks like it could work except Column G actually contains the info like this:

Time and duration of call
11:03 - duration = 10:20

I know that it's madness to combine two sets of data like it has been done here but i can't undo the work of the guy who made it without risking breaking the sheet due to his stupid **** spagetti code under the hood.

 

[Lordkit1]

NEVERMIND! YOU ARE A BEAUTIFUL PERSON! Just ran it and it works! You are amazing! <3

 

[Toadstool]

You're welcome.

...but be careful! If your data is in that format you could match a time against a duration so I'd suggest removing that first wildcard "*" so it only matches against the first five characters.

Lordkit1.xlsx
	F	G	H	I	J
1	Date	Time & duration
2	12-Dec-21	03:05 8:9
3	21-Nov-21	04:06 47:37		Date	12-Dec-21
4	09-Sep-21	12:04 16:7		Time	22:36
5	12-Dec-21	04:06 22:36		Result	That call is a new entry
6	21-Nov-21	04:06 35:6
7	12-Dec-21	03:05 27:22
8	21-Nov-21	04:06 33:44
2nd

Cell Formulas
Range	Formula
J5	J5	=IF(OR(J4="",J3=""),"result will display here.",IF(COUNTIFS($F$2:$F$300000,J3,$G$2:$G$300000,TEXT(J4,"hh:mm")&"*")>0,"that call has already been logged","That call is a new entry"))