Hi, first, a quick thanks for all who've contributed to this forum. I've used it many times in the past and I hope to pass
back some of that help.
In the meantime, I have a question I can't seem to find an answer for here or elsewhere.
I have a spreadsheet with two columns. The first is a list of names that are all unique, but in that list are several that
are very similar.
To illustrate this problem, visualize a list of software listed with versions and a count of
(sorry, not sure how to type in a spreadsheet. I'm trying to show row number and columns A and B
separated by a | )
A | B
1 Excel 220.127.116.11 | 3
2 Excel 18.104.22.168 | 7
3 Excel 22.214.171.124 | 10
4 Powerpoint 126.96.36.199.0 | 8
5 Powerpoint 188.8.131.52.1 | 3
6 Powerpoint 184.108.40.206.1 | 1
7 Word 1.3.2 | 3
8 Word 1.3.0 | 11
9 Adobe Reader 4.2.5 | 7
10 Java 8.23 | 15
Is there a way to compare each cell to the one below and add the count if the two cells are similar?
The following code works pretty well:
' set the initial values
' get the total row count
x = Cells(Rows.Count, 1).End(xlUp).Row
' y will track the rows on the second spreadsheet.
y = 1
' get the value of the first cell
subtot = Cells(2, 2)
' Count each finding by comparing the finding name in one cell to the finding name in the next cell
' if the first five characters are the same, consider them a match
' Then add the counts together. If they aren't get the subtotal and paste the finding name and total count
' on the consolidation spreadsheet
For w = 2 To x
' the last row has a bunch of ZZZZ's so if this is a match, stop the routine
If Left(Cells(w, 1), 6) = "ZZZZZZ" Then Exit Sub
comp = Left((Cells(w, 1)), 6)
Comp1 = Left((Cells(w + 1, 1)), 6)
If comp Comp1 Then
Sheets("Consolidation").Cells(y, 1) = Left((Cells(w, 1)), 6)
Sheets("Consolidation").Cells(y, 2) = subtot
subtot = Cells(w + 1, 3)
y = y + 1
subtot = subtot + Cells(w + 1, 3)
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above code yields the following results:
Word 1 14
Java 8 15
As you can see, by comparing the first x characters in each cell I can get a pretty good total for
each software. However, I don't get a good names (Powerp, Word 1, Java 8) and picking how many LEFT characters to compare is
tricky. Too many and you don't get enough matches and too few and you get too many matches.
So my question is
this: is there a way to compare the entire cells and determine a percentile match? That is, if the cells are 90% similar
then add the counts?
(BTW, the ZZZZZZ is there for other reasons not pertinent to this question)