#1
|
|||
|
|||
Comparing data across multiple columns and rows
Hello, New member here. Thank you all ahead of time for your assistance. I am attempting to compare data across multiple columns and rows. I attached some example data. I am attempting to reconcile debits and credits that have the same date. I may have the same credit/debit amount with a different date so using conditional formatting doesn't work. Ideally, I would like to have an IF statement that would check for a matching date in column A, and then look for matching numbers in columns B & C. In my example data I would like a return of "Match" in column D if there is a match. No Match can be blank. I am dealing with thousands of lines of data, so identifying a one off of no match is daunting without some automation. I've been working on this for several days without luck. Thanks!! |
#2
|
|||
|
|||
The example file data does not show any matching Debit or Credit with different dates.
To assist you in getting the help you are requesting, please provide an example of "AFTER" ... what it would look like after the data has been processed. Thanks. |
#3
|
||||
|
||||
See attached spreadsheet.
I made it a table and added a Reconciliation column to show where the daily total doesn't equal zero. Then the conditional formats applied to each column show the problem days in orange and problem transactions in red.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#4
|
||||
|
||||
Columns E and F in the attached show pairings, if there might be more than one they are listed.
Separately a table to right-click and refresh at cell H1. |
#5
|
|||
|
|||
Thanks all for the replies. I am looking for a formula that would return "M" for Match or "NM" for No Match. What I need to ultimately identify are the credits that do not have a matching debt while confirming that the deposit date is the same on both the credit and debit.
In addition: Some matching credits and debits will not have the same deposit date. I would need the matching credits and debits that do not have the deposit date to be returned as no match. I added an updated sample data that provides results that I an seeking and also highlighted examples of when a credit and debit match but the deposit date does not. Let me know if I need to provide additional clarification. Thanks! Last edited by Cooper13300; 11-09-2021 at 05:03 PM. Reason: providing attachment and additional information |
#6
|
||||
|
||||
In the attached formula in column H.
You can see the steps used to building the formula in hidden columns E:G, but these columns aren't needed at all. There's a comment in cell H47. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Merging all data between two sheets into a third one without duplicates or rows or columns | Ricko_uk | Excel | 4 | 08-27-2020 01:16 AM |
Transpose multiple data form rows in columns matched with a key | Alex1s85 | Excel | 6 | 06-24-2020 07:08 AM |
How to Loop through rows and Columns to Offset Data | Josh1012 | Excel Programming | 4 | 09-06-2018 03:20 AM |
Hide rows in multiple columns based on zero values | Deane | Excel Programming | 19 | 06-23-2015 11:24 PM |
macro for comparing data from 3 columns and pasting into another worksheet | ashukla | Excel | 1 | 06-24-2009 05:01 PM |