VBA that adds up the number of differences in a row of text data

[orinos21]

Hi all,

I am working in a spreadsheet and am trying to write a code that looks at a row and deciphers whether the information in the next cell is different. If the data is different, it sums up the number of differences. Searching for unique values does not work because it is possible some of the cells may have the same information but still be different than the cell directly next to it. I want to be able to look at the row and know the total number of changes, not just for unique values. If anyone has any ideas it would be greatly helpful.

Thanks

[JBeaucaire]

Show us exactly. what you mean. Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Mock up your expected results from the sample data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[orinos21]

I have attached a very simple version of my file. I am monitoring dates for a project (ABC) and am taking weekly snapshots of where the dates are. I not only want to know where the dates currently stand, i also want to know how many times they changed. As you can see in my file, i currently have an equation in column H that counts the number of changes using unique identifiers, so it says there were 2 changes. In reality, there are 3 changes but it does not recognize the last switch back to TBD because that entry already exists. What i am looking for is a macro that will take the entry in B2 and compare it to the entry in C2, then compare C2 to D2 and if there are differences sum them together. This way I will get the actual number of changes as opposed to changes from unique identifiers.

[JBeaucaire]

How about this simple formula in H2, copied down:

=SUMPRODUCT(--(B2:E2<>C2:F2))

[orinos21]

This appears to have done the trick. Thank you!