CODEWITHSUNDEEP
vbaexcel
MJ95
MJ95

Reputation: 479

Dynamically check if cell values exist more than once

I have a row of data with headers, and the same headers can appear n number of times. I need to loop over the headers in VBA, check if each one exists more than once in the whole row, and if so, add a string at the end of each one. 

HEADER 1 HEADER 2 HEADER 3 HEADER 1 HEADER 2 HEADER 3

and the result should be after looping (something like:

HEADER 1 OLD HEADER 2 OLD HEADER 3 OLD HEADER 1 NEW HEADER 2 NEW HEADER 3 NEW

I know i need to loop over the cells, but then the logic escapes me

For i = 1 to lastCol
    If ws.Cells(1, i).Value = "HEADER 1" Then
         headerCount = headerCount + 1
    End if
Next i

The logic after the for loop escapes me

Upvotes: 0

Views: 1106

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

Might not be the best approach, but you could loop through the list twice and compare. If you have a lot of headers, this may be slow.

For i = 1 to lastCol - 1
    numDuplicates = 0
    for j = i + 1 to lastCol
        if ws.Cells(1,i) = ws.Cells(1,j) then
            numDuplicates = numDuplicates + 1
            ws.Cells(1,j) = ws.Cells(1,j) & " " & numDuplicates
        end if
    next j
next i

Something like that? This retains the original header and then marks the duplicates with a 1, 2, ...

Upvotes: 1

Related Questions