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

Lordkit1

New Member
Joined
Oct 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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
FGHIJ
1DateTime & duration
212-Dec-2103:05 8:9
321-Nov-2104:06 47:37Date12-Dec-21
409-Sep-2112:04 16:7Time4:06
512-Dec-2104:06 22:36Resultthat call has already been logged
621-Nov-2104:06 35:6
712-Dec-2103:05 27:22
821-Nov-2104:06 33:44
Sheet1
Cell Formulas
RangeFormula
J5J5=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"))
 
Solution

Lordkit1

New Member
Joined
Oct 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
NEVERMIND! YOU ARE A BEAUTIFUL PERSON! Just ran it and it works! You are amazing! <3
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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
FGHIJ
1DateTime & duration
212-Dec-2103:05 8:9
321-Nov-2104:06 47:37Date12-Dec-21
409-Sep-2112:04 16:7Time22:36
512-Dec-2104:06 22:36ResultThat call is a new entry
621-Nov-2104:06 35:6
712-Dec-2103:05 27:22
821-Nov-2104:06 33:44
2nd
Cell Formulas
RangeFormula
J5J5=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"))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,228
Messages
5,836,090
Members
430,404
Latest member
goncaloColt

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
Top