We would need some type of indicater that will flag that there is two
different values in one cell that had a duplicate key. Coloring the cell or
something. The merging of the two in one cell can be easily missed by others.
Is there a way we can do this? Unfortunately we do not have a set precedence
for which value is more important so we will need to be flagged for additonal
cleanup in other databases that the information came from
"Dave Peterson" wrote:
You could even have it so that if there were two different values in rows that
had duplicate keys that the merged cell would have both values:
M and V (on different rows) would combine to be M,V in one cell.
Personally, I don't like working with colors--it may look nice, but there's not
much you can do with them just using regular worksheet formulas. I'd rather use
a cell (or multiple cells) to do the flagging. Then you can filter on them,
sort by them, and just use them any old way you want.
This was the original version...
Option Explicit
Sub testme()
'declare some variables
'wks is the worksheet that holds the data. If the worksheet name
'changes, then just fix it one spot and you're done.
Dim wks As Worksheet
'a variable that will be used to loop through the rows in that
'worksheet (starting from the bottom and going up.)
Dim iRow As Long
'a variable to loop through the columns
Dim iCol As Long
'just a variable that represents the first row with real data
'Change it once if you have different number of header rows
Dim FirstRow As Long
'the lastrow number on the worksheet
Dim LastRow As Long
'again, a variable to represent the number of columns to check
'change it in one spot if/when it needs to be changed.
Dim maxColsToCheck As Long
'here's that spot!
maxColsToCheck = 50
'fix the worksheet here and forget about it.
Set wks = Worksheets("sheet1")
'by using the with/end with structure, you don't need to type the fully
'qualified object.
With wks
FirstRow = 2 'headers in row 1???
'for instance, this could have been:
'LastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row
'instead of just:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'last row is determined by looking at A65536 and hitting
'End|then the up arrow key. Excel stops on that last used cell
'start at the bottom and work to the top
'that's why we have a negative number in the step portion
For iRow = LastRow To FirstRow + 1 Step -1
'say we're on row 25, then this line says
'to look at A25 and compare it with A24 (irow-1, in column
A)
If .Cells(iRow, "A").Value < .Cells(iRow - 1, "A").Value Then
'if they're different (that's what < means), then
'don't do anything (the next line is a comment to the reader
'do nothing
Else
'but if A25 = A24, then
'start at column 2 and go all the way to column 50
For iCol = 2 To maxColsToCheck
'and check to see if the previous row = "0"
If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
'if it is a "o", then
'plop the next row onto the previous row
'E24 is replaced with E25 (say)
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol
'after you're done with comparing columns 2-50, delete the row.
'delete that duplicate
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
The modifications just check to see if either value was PL or PC (if I recall
correctly), then do the work.
Jen wrote:
Dave, It worked this time, I needed to place the name in column A. In the
original worksheet name was in column D. I am guessing that is why my
"triplicates" were not consolidating.
As I get deeper into this, I have a few more questions.
I see you set the macro up to determine if cell value is = "O" look at other
value if duplicated and to pull value in if different. What if the name was
duplicated and for example value is "M" and the other is"V" is there a way to
flag these types of entries. Maybe a macro that could have conditional
formatting, such as shading the cell and not consolidating the rows. When
these types of records consolidate, the macro made its own determination what
took precedence, can these not be consolidated? I need to catch these types
of scenarios
Would you be able to breakdown what each line in the module stands for?
<<snipped
|