Compare 2 different lists and create a new table with the data

[DionV98]

Hello everybody, I am new here. I have 2 lists representing data from my company, which more specifically are for the years 2023 and 2024 the sold items per customer, alongside with the quantity of items purchased and the total price. What i want, is to compare those 2 lists and create a new list or table with data from both lists, in ascending order with the highest value purchased from each customer. I will attach a test file i created which is very similar to my data. Really looking forward to an answer.

test.xlsx

[AliGW]

Welcome to the forum.

Are you really using Excel 2013?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[AliGW]

Thanks for adding a workbook, however you have not includesd a manual mock-up of the results data. Please add this to show what we are aiming for. Thanks.

[DionV98]

Thank you for your quick response! I just added to my file the result as i would want it (or somehow similar to it). I hope this helps. Thank you!

*I editted the post and re-add it

[AliGW]

Are you really using Excel 2013?

[DionV98]

Yes, I am using this Pro version of Excel

[AliGW]

OK - this makes it harder. I'll have a look.

[DionV98]

Thank you! At least, something that could help and be a great start for me would be if i could somehow bind all the data for each customer (ID,Products etc) so that i can sort them but without losing the initial stracture. I thought about making 1 table for each customer, but that would take ages as i think of it since they are around 100 this time just for this group of products.

[AliGW]

Would a VBA solution be acceptable? Someone might be able to do this easier that way for you.

[AliGW]

Also, please be aware that any solution will not be able to merge cells for you - cell merging should be avoided at all costs.

[avk]

Dear DionV98 > I have seen with your data. It's not seems data structures are correctly. If you require changes in data structure, please confirm.

[mojirhi]

This is you requested VBA macro.
but, there is no merge cells and formatting cells.

[DionV98]

Thank you all very much! For now, i did it manually, even though it took me a lot of time, it is ok for now. When there is a new need to compare a huge amount of data, maybe I will check back here to find a way.

[avk]

Instead of manually work, kindly go with this file. I changes your data structure. It may be help for you in future.
See sheet compair1 & compair2 [two type of result]
Firstly i extract unique pr1, pr2 ... so on

Please Login or Register  to view this content.

by one time pressing shift+ctrl+enter
in Q2 apply data validation you can selete code.
2023 & 2024 year : apply simple sumifs formula.
File attached for your ref.