• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching tables

P

Posthuman

New Member
Hello Excel Gurus!

I attached an excel file which contains 2 sheets, table A and table B.

  • I need to match the numbers for given type, period, currency and rates.
  • Columns and rows are in different positions due to different system outputs (It may be a little pain for you?)
What exactly I need is to make sure both table A and table B has same rates for given type, period and currency.

For example:

in table A there is a type of Leasing, for the currency USD, and period for 1M. Rate for this combination is 3,50 (yellow cell)

Then I go to the table B and find the same combination and check if it has the same rate. (yes it is!) If not it should tell me its FALSE, if it is same it should show TRUE, like in the attached file. The formula should do this for all types, currencies and periods. Is it possible to do that?

Thanks in advance!
 

Attachments

Posthuman said:
Hello Eloise, thanks for your efforts! But I couldn't make it work for other cells, what do I wrong?
Click to expand...
That's why I said, "...just a start." Someone much smarter in Excel than I may have to figure this one out. Hopefully you can see what I did with the formula.
I simply made a one-to-one comparison between the particular cells and enclosed the 4 comparisons with AND so that all 4 comparisons have to be TRUE to get the IF statement to return TRUE. Additionally, due to unwanted spaces in some of the comparison cells, I found it prudent to enclose each cell location with the TRIM function.

Do to the different layout/format of the spreadsheets, I don't know if there's a clever way to compare the two sheets completely without a lot of maticulous formula construction for each comparison needed. There's probably a way to do it, it is just beyond my Excel abilities at this point in time.

Thinking out loud, if you had a table on a third sheet that contained the different comparisons necessary....maybe that would be a solution? A question I have: Is this a one time need or will you be flushing and filling the sheets with different numbers on a regular basis. If so, a table on a third sheet (TABLE C) may be the way to go. Otherwise, you may have to go through the trouble of creating a specific formula for each comparison necessary.

BTW, I assume sheet 2 (TABLE B, Columns I, J, and K) is where you wanted the formulas to appear?

You may want to repost your question if you don't get a response soon.
 
Last edited:
Oh, first of all sorry for my late respond. Second, thanks for your answer, really!

Well, I tried to fix some of the columns/rows with $, but it did not work for other cells. Then I gave up soo fast! Because, you know, I am a noob.

It's not a one time need, I get two different excel file from two different departments and the formats of those files are totally different as you already aware. And almost every week I need to make sure this rates are same.. In normal conditions I do this old school style -checking with eyes-, but it takes like half an hour and my eyes burning in the end! So, I decided to come here to get an advice from excel gurus like you.

Yes, you are right about those I, J and K columns. So, I can just copy&paste those columns and ta daaaa! I, J, K would show me whats wrong with this numbers (and people).

I didn't get the idea of yours about the Table C, can you explain it a little more?

Thanks again, I appreciate your support!
 
You must log in or register to reply here.
Back
Top