LinkBack URL
About LinkBacks
Hello,
There are two columns I need to compare - cost of sales and sales. The cost of sales account should have a corresponding sales account. I need to define those pairs and then create a formula to compare to find ones that do not match.
Need to define pairs in two columns and then build formula to compare these columns
Hello,
There are two columns I need to compare - cost of sales and sales. The cost of sales account should have a corresponding sales account. I need to define those pairs and then create a formula to compare to find ones that do not match.
Can you please be more specific, you're work makes sense to you but not to others.
Also can you upload a smale file with false data that can be used to further help explain. This can be done by clicking Go Advanced further down the page and then choosing manage attachments.
Say thanks, click *
I've attached the spreadsheet.
There are item numbers (col A) with associated cost of goods account numbers (col B) and sales account numbers (col C).
Any item number with a cost of goods account of 40200 must have corresponding sales account 30001.
I need to find out how to define what the pairs are (acct 40200 goes with 30001) and then compare column B and C to return a value like FALSE, when the pair does not exist, like in row 9.
My spreadsheet has 22,000 item numbers so I would prefer not to go through this manually. I've found a work around that is labor intensive as well as a last resort, but was hoping there was another way to do this.
Am I correct in saying then that col D and E must match up correctly, say 40200 in D means E must be 30001? If so you can create a table on another sheet to list what the pairings should be and use this to return TRUE of FALSE (or something else if needed).
As for comparing Col B and Col C I don't understand how you are comparing them...
Btw in Row 9 D and E are correct?
The attached file has a column which returns TRUE/FALSE if D and E are the correct match.
Thanks, Harribone. Sorry for the typo. I had simplified the spreadsheet more, but did not save and post that in my haste. It is column D and E that would need to be compared. Yes, if Column D is 40200, Column E must be 30001.
You may want to try this file.
On the 2nd sheet in Col A type in your cost of sales accounts and in B type in what the sales account should be.
Then on the first sheet Col E has a formula to automatically fill with the correct sales account.
I cannot use the 2nd file. This information was exported from our accounting program so I can check to make sure the items are entered correctly. I need to identify what is not correct so I can go in and fix it. Thank you for your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks