• 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.

Need help to compare the list

M

mu4you

New Member
Dear Sir,

I need help to compare 2 lists as attached, hope my file well understandable.

thanking you in advance and waiting for your reply.

regards,

Manzoor
 

Attachments

  • Book2.xlsx
    9.2 KB · Views: 9
It would be helpful if you explained in sufficient detail what you are comparing. The two tables are not exactly the same. What is your criteria for comparison. Once the comparison is made, what would you like to happen? If True? If not true? Help us to help you as we are not mind readers and can only assist if we understand the issue and what the expected results should be.
 
Sir,
the process of attached sheet is to compare the supplier statement of account.

Column A & B - supplier invoice no and amount
Column C & D - what we have received physically with the materials.(Inv & Amount)

So, to have smooth payment, we need reconcile with supplier invoice data with us.

Hope it's clear now.
 
I concur with Narayank991. Please expand your explanation as the information in the output bears no resemblance to the the data in the first table.
 
NARAYANK991 said:
Hi ,

The output amounts you have shown do not match the input amounts for 3 invoices.

How have you arrived at those output amounts ?

Narayan
Click to expand...
Mr. Narayan,

I have tried as follows:

1. I have made pivot table to add all duplicate invoice amount.
2. I have copied all invoices in 1 column column A & C.
3. =INDEX('Sheet1 (2)'!$A$2:$D$20,MATCH(A2,'Sheet1 (2)'!$A$2:$A$20,0),2) & =INDEX('Sheet1 (2)'!$C$2:$D$20,MATCH(A2,'Sheet1 (2)'!$C$2:$C$20,0),2) - but in my formula I am getting values twice wherever formula is true. So fed up
and to get the desired results I type manual on first sheet which you were asking.

thanking you in advance and hope my clarification well understood.
 
Mr. Narayan,

Please find attached.

I need to do the compare column A & B (supplier invoice # & amount) with
what we have received the materials which is in column C & D (we may have receive partial delivery in same invoice)
 

Attachments

  • Book4.xlsx
    45.1 KB · Views: 2
Attached is your file amended using the Power Query functionality. I joined the two tables in a Left Join so that you can see where All of Table D and only Table B where there are matches. Look at tutorials for Power Query. You can add a column to see the differences and then filter for those not equal to zero to find outstandings
 

Attachments

  • Book4.xlsx
    118.5 KB · Views: 4
Sir...... great respect.
Yeah.... this is exactly what I wanted....
Kindly explain me the formula if you can....
 
I just saw, this is what exactly what I want and thanks I will youtube about power query to learn more about it.
 
You must log in or register to reply here.
Back
Top