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

compare 3 level data

PANKAJ SHAH

New Member
Hello Sir, require to compare data in 3 level.

For example: first bill number (unique), second product group name(can be multiple with same name in each bill), third will be quantity (product group wise) to be matched with other excel sheet with same style of data.

I require to see matched data and unmatched data in colour code separately.
 
Last edited by a moderator:
THANKS FOR YOUR REPLY SIR, I AM ATTACHING EXCEL WITH REQUIREMNTS NOTED IN IT.
RESULT OUTPUT
 

Attachments

  • New Microsoft Excel Worksheet.xlsx
    49.8 KB · Views: 13
PANKAJ SHAH
Seems You do not know are Your expected given result correct or something else ... hmm?
Screenshot 2020-10-26 at 20.08.51.png
Could You explain
... why Your 'RESULT SHEET' has above kind of ending?
... is there some reason, why Your have skipped rows from 2 to 4 from SALES-sheet?
 
Look at the attached and determine if this is what you need. It was created in Power Query by merging (Joining) the three variables
 

Attachments

  • New Microsoft Excel Worksheet.xlsx
    70.7 KB · Views: 6
PANKAJ SHAH
Seems You do not know are Your expected given result correct or something else ... hmm?
View attachment 71809
Could You explain
... why Your 'RESULT SHEET' has above kind of ending?
... is there some reason, why Your have skipped rows from 2 to 4 from SALES-sheet?

i made this example to show that i may be missing on few sales or purchase entries, which also requires to be highlighted as mismatch for me to check
 
Look at the attached and determine if this is what you need. It was created in Power Query by merging (Joining) the three variables


thanks for your help, i have few comments and questions
1. will this excel sheet work for any amount of data for my future data matching?
2. a few entries i deliberately made mismatch in qty and group product name, but they are all not found in your sheet1 (a few are missing)
 
A different Power Query take from Alan's in the Results sheet.
Yes it will work for any amount of data.
It can also work on the tables without the calculated columns.
 

Attachments

  • Chandoo45224New Microsoft Excel Worksheet.xlsx
    72.1 KB · Views: 6
A different Power Query take from Alan's in the Results sheet.
Yes it will work for any amount of data.
It can also work on the tables without the calculated columns.

thanks for the support and instant help
i would like to ask, where is the code written for my future reference?
 
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
PANKAJ SHAH
If You would save Your time and see the results at once like below
Screenshot 2020-10-27 at 11.35.35.png
Yellow cells shows 'need-to-do's as well as with comment, You could see other sheet's value.
>> Press [ Do It ]-button <<
 

Attachments

  • New Microsoft Excel Worksheet.xlsb
    42.4 KB · Views: 4
Back
Top