Receive new articles directly in your inbox!! Register your email address

Excel compare strings with VBA - unsorted lists

IN A PREVIOUS POST we talked about the methods available for Excel compare strings.

The INSTR and MID functions were used to generate a third list associating to each customer name elements of two original list

The below table shows the two original List and the final Output achieved thanks to Excel compare strings

The Output list associates to each customer surname his preferred fruit and city of residence

This post will address an additional level of difficulty regarding how the initial lists can be presented

More often than not the two original list will not have the same customer on the same row.

An example of what you would usually have is reported in the below picture

The two lists do not show customer names on the same line; in addition there are customers present in one list and not present in the other (i.e. Frank Water) or the other way around (i.e. Steve Land)

To ensure that data is analysed correctly we need to perform a double loop

HIGHLIGHTS

Excel compare strings with VBA - unsorted lists

1 Double Loop

2 Code Run Through

3 VBA Code

1 Double Loop

We have written on how to compare two lists IN THIS POST

When you are in need to compare two lists where the same item is reported on different rows and/or there are items present in list but not in the other the best approach is to perform a double loop

This is to say that each item of the first list needs to be compared with all items of the second list.

The below picture offers a visual representation of the concept

The first loop starts by focusing on the first elements in List 1.

The second loop then starts and goes through all the elements of List 2 and verifies if there is any item in List 2 matching the first item of List 1

Once all elements of List 2 have been analysed,the second element of List 1 is considered and the second loop restarts cycling through all the elements of List 2

A key consideration is that this set up will generate a final list where all the elements in list 1 and in list 2 will be reported

Elements present only in List 1 will not be part of the final list

Also elements present only in List 2 l not be part of the final list

The final output is as described in the below picture

2 Code Run Through

The code runs through two lists that are placed in an Excel spreadsheet as in the picture above and generates the output placed in the columns with header OUTPUT

The first loop is started and will go through the elements of List 1

The second loop will go through all the elements of List 2

For j = 4 To 7

For k = 4 To 7

The function INSTR and MID are used in order to identify the name of the person in LIST 1 currently analysed by the first loop and compare to the name of the person which is being analysed by the second loop

IN THIS POST YOU WILL FIND AN IN DEPTH EXPLANATION OF THE INSTR AND MID FUNCTIONS

i = InStr(1, Cells(k, 16).Value, " ", vbTextCompare)

src = Mid(Cells(k, 16).Value, 1, i - 1)

m = InStr(1, Cells(j, 8).Value, src, vbTextCompare)

If the name currently analysed by the second loop (src) is found in the name analysed by the first loop (m ), data from both lists are reported in columns S,T and U. If not no data is reported

If m > 0 Then

Cells(j, 19) = src

Cells(j, 20) = Cells(k, 17)

Cells(j, 21) = Cells(j, 9)

Else: End If

The second loop moves on to the next item and when it reaches the bottom then the first loop moves forward to the next item

Next k

Next j

End Sub