compare and find custom unique records

**Manii** · 11-18-2016, 07:53 AM

Hi, I have 2 lists containing thousands of records with numbers only having 3 to 17 digits each.
I need to compare the lists between them to find non partial matching records and I was able to do so however this is getting more complicated since I need to compare one digit at a time.
Then I need to merge the results and do another filtering.

Pls see below example to explan what I need since it is complicated .

List 1
123456
23232312
231
23232
456

List 2
12345
231321
456321
2323

The result that I have achieved is shown below

Result for List 2 when compared to List 1
12345 (NEW Entry since List 1 contains 123456 and is longer)
231321 (NEW Entry since List 1 does not contain this number)
456321 (Already exist since List 1 contains already 456 number and is shorter)
2323 (NEW Entry since List 1 contains 23232312 and is longer)

Now I need to merge both lists and filter. The result should be as follows.
Merged List
123456 (This will be deleted since 12345 already exists in List 2 and is shorter)
23232312 (This will be deleted since 2323 already exists in List 2 and is shorter)
231 (This entry will be kept since it is shorter than entry 231321 from List 2)
23232 (This will be deleted since 2323 already exists in List 2 and is shorter)
456 (This entry will be kept since there is no matching number)
12345 (This entry will be kept since 123456 from List 1 already exists however it is longer)
231321 (This will be deleted since 231 already exists in List 1 and is shorter)
456321 (This will be deleted since 456 already exists in List 1 and is shorter)
2323 (This entry will be kept since it is shorter than entry 23232312 from List 1)

So I should end up with the following results
Final List
231
456
12345
2323

I know that it is complicated however the above example might help to explain what I need. Any help please?
I can’t do this with basic filtering or sorting. I am using INDEX and MATCH to achieve the first part but now I need to achieve the last step.

Thanks

**dflak** · 11-20-2016, 12:18 AM

Please attach a workbook with sample data. Most of us don't have the time to recreate a spreadsheet that you probably already have. Even at that, we might misinterpret what the structure looks like. To attach a worksheet click on Go Advanced and scroll down to the Manage Attachment link. This will open a dialog box where you can navigate to and upload the spreadsheet.

**Manii** · 11-21-2016, 05:38 AM

Please find attached the sample.

The sheet contains 5 pages. There are 4 different lists that should be combined and finally merged in the Result sheet.
The Result sheet should be blank and populated with the formulas to get the numbers extracted from the other sheets.
The Result sheet shows the final results that should be obtained from the other 4 sheets. The 4 sheets have thousands of records. Some of them have over 15,000 records.

Also it would be ideal; if next to the result; I will have the sheet name from where the number was taken just for reference to be able to comapare records if required.

Thanks

**dflak** · 11-21-2016, 09:46 AM

Just to make sure I understand the requirement:

You want to compare List 1 to List 2, List 3 and List 4 and publish a number, if it is unique. Do not publish a number if part of it is already published someplace else. So if I have 1234 and I come across 12345, I don't publish 12345. Likewise if I have 12345 and I come across 1234, I drop the 12345 and use 1234. Is this correct?

Then continue and compare List 2 to List 3 and List 4 and finally List 3 to List 4.

**Manii** · 11-21-2016, 11:02 AM

Yes exactly, so finaly I get a comparison between the sheets and their results. The answer should be like the results sheet.

Tx

**dflak** · 11-21-2016, 11:37 AM

I had to resort to VBA to get this done.

I created a page called Big List and on it is a table called Table_Big_List. I like to use tables because they duplicate formulas and are easily addressable by column header both in VBA and normal Excel formulas.

The first part of the code concatenates the data from the various sheets into this table and sorts it by number so it is in the same order as the desired results.

Column A in the table is the number.

Column B in the table is the string representation of the number and has the formula: =[@Number]&"". This formula converts the number into a string so we can use LEFT and LEN formulas. VBA might be tolerant enough to work with the numbers themselves, but I come from a less forgiving environment ( C ) where data types are very strict.

I copied in the sheet names in column C as I was concatenating the data.

Column D is the results of the subroutine that does the comparison. This subroutine is called True_False and compares the elements of the string to itself to see if an element has a substring that matches it.

Column E is there solely for the purpose of QA. All it does is check the number to see if it’s in the desired results column (for the purposes of the test, I moved this to column C on the results sheet.) You can remove Column E.

I probably could have put the True_False subroutine in the main program. I developed it as a separate subroutine so I could test it easily.