Comparing & Mark different columns in excel VBA please

[tempo1]

Hello all

I need your exert advice in this regard, each day I am getiing 3 files ( 1 main file and 2 checking files) we just need to compare it against the main file, are you able to help me in this regard.

Compare String is Amount + Date (date HH:MM) but in my second sheet time is 30 minutes more than main sheet. So we need to less 30 minutes when comparing with main sheet

When Checking please Ignore Seconds

Second Sheet and Third Sheet are individual files

Main sheet contains all the data from second and third sheets, if it matches please mark it (orange & Green color)

Please see attached excel file, thank you, this will help my time immensely and I am getting old these days too

[MarvinP]

Hi tempo,

I think you need to convert all of your times using MRound to the nearest minute. You also need to make the middle table's dates, real dates instead of strings of text. I've added 3 helping columns to do the conversions. THEN I used conditional formatting to show Duplicates which I think is what you want. See the attached and ask questions if needed.
MRound Compare and Conditional Format Dups.xlsx

[kvsrinivasamurthy]

This macro for this sheet . That is all the three data should be in the same sheet.
Column D was Text format. I have changed it to data format.
Colum D is higher by 30mints compared to Column A. But you have written opposite to that in file.

Please Login or Register  to view this content.

[tempo1]

Really appreciated your help, I will check it and let you know about it, Thank you very much sir

[tempo1]

Thank you sir, but need some correction as some of the data is missing to compare, please see uploaded spreadsheet. Is it possible to highlight the Main sheet with same color as well-like green with green , orange with orange.

Sorry I didn't mentioned it earlier.

Thanks again

[kvsrinivasamurthy]

Modified code

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Slightly made faster.

Please Login or Register  to view this content.

[Marc L]

According to the post #5 attachment an Excel basics VBA demonstration for starters :

Please Login or Register  to view this content.

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[tempo1]

Slightly made faster.

Please Login or Register  to view this content.

Thank you Murthy sir, this solved my problem, all the best

[kvsrinivasamurthy]

Welcome. Pl mark the thread solved.

[tempo1]

Sorry I got an error when a fresh data copied to the spreadsheet, (Run time error-Type Mismatch Error). I think it is related to the data in D & E columns, please see attached and fix it, thank you

[kvsrinivasamurthy]

Data in D column is text format. This already told in Post #3.

How to find column data is text?
Select data
Home->In Number select for drop down
Then you see in Number and in Short Date same text appears.

Change data in D column to date.
Select the column D data.
Data -> Text to columns ->Delimited->Next-> Next -> select Date -> Finish.