Excel vba code to match duplicates

[Ermias]

Hi guys,

This is to correct my previous post!

I need a VBA code that can solve the following problem:

Basically there are two sheets that contain the same document number. The “Lookup Value” sheet contains all the information I need for the relevant document number which is “Type” and “No.”. But the second sheet (the “Result” sheet) does not contain “Type” and “No”. Therefore, I want to bring “Item” and “No.” from the “Lookup Value” sheet by matching the document number which is the same in both sheets.


THE PROBLEM IS:

The normal VLOOKUP, INDEXMATCH formulas can never do this because the whole file is really messed up and here is why:


1. There are so many duplicate document numbers in the “lookup sheet” with varying “Type” and “No.” so when you use VLOOKUP or INDEXMATCH it gives a very biased information

2. The number of document no. columns in both sheets does not match.

IMPORTANT NOTICE: PLEASE CONVERT THE FILE EXTENSION FROM PDF TO XLSX TO GET THE EXCEL FILE AND DO THE REVERSE WHEN YOU RE-POST THE FILE.


Note: Please refer the filtered sample sheet to see the requirement but just to remind you that there are so many different scenarios than what is shown on the filtered sample sheet.


After completion I need to check and validate the result because I have tried this like a 100 times but it seemed to work fine when in fact it gives a totally wrong information which is also why I say that the person who solve it correctly without any flow is a real genius!



I really have no idea how to solve this so I want to get the help of an expert.


Thank you so much for the help

 

[Becky]

The pdf is corrupted, I can't open it Could you try doing a screenshot?

 

[Ermias]

nah, you wouldn't understand it with a screenshot can you rename the pdf file extension to xlsx because that is the only way to make it work with excel or the other option is i sent you a zip file containing the excel file which you can use.

if you provide a moc email that you dont use, i can forward it to you personally also but i dont recommend you to do that.

 

[Abraham Andres Luna]

It sounds to me like you should write a script to clean up the data so that you don't have duplicate numbers with different values.

 

[aeqquus]

Not sure of the objective of doing this but the issue is you have multiple numbers referencing multiple document number and type combinations. If you are trying to create some unique index on a type, you'll have to create an index with combination of type + item, if index is document and item, then create combination of document no and item. If the index is a many-to-many list, you're origina list is already the linked-list. See my uploaded file for ways to create different lists. Basically create a concatenated field with the relevant combination then create a unique list using Advanced filtering. (change extension - I'm using Excel2016)