VBA - Read a list item, check if it matches any previous ones - if not, write it in cell below previous, otherwise skip to next one.

[leighhobson89]

Hi guys,

Currently the simple code below reads two values on a row, one from column "C" and one from column "D". It concatenates them and writes the new string in column "B".

Sometimes the list in column C will contain more than one string exactly the same. I don't want it to write a value if it matches one previously written in the list, but I dont want empty rows, so I presume somewhere I need to include a one dimensional array and set a flag when a specific string is written, then before writing the next one, check all values in the array for a match, and skip if it finds one, otherwise write it in. I have read about arrays and I just can't get my head around them until I see a working example that I can relateto. Can anyone help finish this off for me? The code follows:

Private Sub ClearButton_Click()

Range("A5:D5000").Value = ""

End Sub

Private Sub MergeButton_Click()
Dim result As String
Dim dumpSongName As String
Dim dumpArtist As String
Dim searchOrder As Integer
Dim selectionCellArtist As String
Dim selectionCellSong As String
Dim counter As Integer

'Count number of rows

counter = 0
selectionCellArtist = Cells(counter + 5, 3).Value
selectionCellSong = Cells(counter + 5, 4).Value

Do While selectionCellArtist <> "" And selectionCellSong <> ""
    counter = counter + 1
    selectionCellArtist = Cells(counter + 5, 3).Value
    selectionCellSong = Cells(counter + 5, 4).Value
Loop
        
If counter = 0 Then
    MsgBox ("You need to dump something in both the Artist and Song dump columns to merge them!")
    End
End If


' Merge results and write in Search term field

searchOrder = 0

For i = 1 To counter [COLOR=#ff0000]' CHANGE THIS LOOP TO WRITE FIRST ITEM IN "Cells(i + 4), 2)" THEN CHECK IF FOLLOWING ITEMS MATCH ANY OF THE PREVIOUS ONES, IF NOT WRITE THEM IN THE CELL BELOW, IF MATCH THEN SKIP[/COLOR]

dumpArtist = Cells((i + 4), 3).Value
dumpSongName = Cells((i + 4), 4).Value

result = dumpArtist + " " + dumpSongName

If result = " " Then result = ""

If result = "" Then Exit For

Cells((i + 4), 2).Value = result

searchOrder = searchOrder + 1
Cells((i + 4), 1).Value = searchOrder

Next i

End Sub

 

[CalcSux78]

Since you're looking for unique values to add, I would suggest using a Dictionary (similar to an array, but only allows unique values)

Check out Paul's blog Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery Excel Macro Mastery

 

[leighhobson89]

Thanks,

I should mention that where i said "Sometimes the list in column C will contain more than one string exactly the same" - I actually meant B, my mistake.

So I want do the combining first and then when its done everything is in B. Thats where this starts. The basic procedure is as follows:

Set flag to True for first value,
Read second value,
Compare it with the one above it:
<if it="" matches="" first="" then="" skip="" to="" next="" value,="" if="" not="" write="" second="" value="" in="" cell="" down="">if there is a match, skip it, otherwise write this new value in the cell below the first value and then set the flag for this value to true.
Read next value
Compare with all other values above it individually and check if there is a match:
<if one="" match="" then="" skip="" otherwise="" write="" in="" next="" cell="" down.
if there is a match on any specific value, skip it, otherwise write this new value in the cell below the last one written in, and then set the flag for this value to true.

This is the idea - I can't seem to get it whatever i read, the loops etc are screwing around with my cranium, and although I need to read up more for sure, I have a deadline on this macro which is enormous and 99% complete. This part is the last part of a search feature i made within a huge database before running an equally huge macro. I just want this tiny bit polishing off! There must be some nice person who can help me, please </if></if>

 

[CalcSux78]

Could you post a selection of what we are matching? Maybe the first 20 rows of columns A:D? It would make figuring out your required loop a lot easier.

See Post 2 (HTML Maker)

 

[leighhobson89]

Hi, so below we have a screen shot of a part of the spreadsheet.

The "Merge" button is what runs the code. Columns "C" and "D" have had static data pasted there from another workbook automatically. The user can then press "Merge" to concatenate the value from C and D column into one string, which is written in B column as you can see (Its designed this way to give the user a chance to see what they have before continuing, so thats why I didn't automate the whole thing. There are sometimes duplicates though, as the information comes from multiple data sources in the other workbook via web query, so I want to weed those out as it writes the list in B by checking if any of the previous rows in B match the one currently being written. In the end there will be a list of unique records in B column, and obviously it will be a shorter list with no blank lines inbetween.


Does that make it clearer to understand what I am trying to achieve?

EDIT: Access the image here instead:
https://1drv.ms/i/s!AvBtCt0UcpvIgnC5gx1_w3tBeNy3

 

[leighhobson89]

Please ca anyone help with this?