Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-04-2021, 01:45 PM
Cooper13300 Cooper13300 is offline Comparing data across multiple columns and rows Windows 10 Comparing data across multiple columns and rows Office 2019
Novice
Comparing data across multiple columns and rows
 
Join Date: Nov 2021
Posts: 2
Cooper13300 is on a distinguished road
Default 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!!
Attached Files
File Type: xlsx Sample Data.xlsx (9.1 KB, 7 views)
Reply With Quote
  #2  
Old 11-04-2021, 05:41 PM
Logit Logit is offline Comparing data across multiple columns and rows Windows 10 Comparing data across multiple columns and rows Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #3  
Old 11-04-2021, 06:38 PM
Guessed's Avatar
Guessed Guessed is offline Comparing data across multiple columns and rows Windows 10 Comparing data across multiple columns and rows Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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.
Attached Files
File Type: xlsx Sample Data.xlsx (21.4 KB, 7 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #4  
Old 11-06-2021, 06:11 AM
p45cal's Avatar
p45cal p45cal is offline Comparing data across multiple columns and rows Windows 10 Comparing data across multiple columns and rows Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsx msofficeForums47925Sample Data.xlsx (20.2 KB, 8 views)
Reply With Quote
  #5  
Old 11-09-2021, 04:51 PM
Cooper13300 Cooper13300 is offline Comparing data across multiple columns and rows Windows 10 Comparing data across multiple columns and rows Office 2019
Novice
Comparing data across multiple columns and rows
 
Join Date: Nov 2021
Posts: 2
Cooper13300 is on a distinguished road
Default

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!
Attached Files
File Type: xlsx Sample Data.xlsx (10.2 KB, 6 views)

Last edited by Cooper13300; 11-09-2021 at 05:03 PM. Reason: providing attachment and additional information
Reply With Quote
  #6  
Old 11-13-2021, 10:04 AM
p45cal's Avatar
p45cal p45cal is offline Comparing data across multiple columns and rows Windows 10 Comparing data across multiple columns and rows Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsx msofficeforums47925SampleData_v2.xlsx (17.3 KB, 5 views)
Reply With Quote
Reply

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
Comparing data across multiple columns and rows 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft