Excel vba code to match duplicates


Joined
Jul 12, 2018
Messages
2
Reaction score
0
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
 

Attachments

Becky

Webmistress
Administrator
Joined
Mar 25, 2003
Messages
7,346
Reaction score
1,473
The pdf is corrupted, I can't open it :( Could you try doing a screenshot?
 
Joined
Jul 12, 2018
Messages
2
Reaction score
0
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.
 

Attachments

Joined
Mar 14, 2018
Messages
446
Reaction score
139
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.
 
Joined
Jul 22, 2018
Messages
5
Reaction score
4
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)
 

Attachments

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top