Compare and consolidating duplicated rows

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

Try this against a copy of your worksheet (it destroys the original data).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim iCol As Long

Dim FirstRow As Long
Dim LastRow As Long
Dim maxColsToCheck As Long

maxColsToCheck = 50

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value .Cells(iRow - 1, "A").Value Then
'do nothing
Else
For iCol = 2 To maxColsToCheck
If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol
'delete that duplicate
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jen wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1

If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

Yes. It would matter.

If it's a one time thing, I'd just Edit|replace 0 with O (zero with oh). Make
sure you check "match entire cell contents".

If you have to do it lots of times...

change this:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If

to:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" _
Or (IsEmpty(.Cells(iRow - 1, iCol).Value) = False _
And .Cells(iRow - 1, iCol).Value = 0) Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If


===
Blank cells have a value of 0, so the code has to check for that.

Jen wrote:

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson

Thank you for your help.

I also have another worksheet that has a similar issue, it would need to
look at the name to see if it is duplicated and then look at employee type,
employee type may have two to three types: PC, A, O, PL.
What I would need the macro to do is combined to one row, only if it has PL
and PC as employee type, is there additonal VBA code that can be added to the
beginning of the macro you had sent me ? Thank you again for your asssitance,
you have saved me alot of time.

"Dave Peterson" wrote:

Yes. It would matter.

If it's a one time thing, I'd just Edit|replace 0 with O (zero with oh). Make
sure you check "match entire cell contents".

If you have to do it lots of times...

change this:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If

to:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" _
Or (IsEmpty(.Cells(iRow - 1, iCol).Value) = False _
And .Cells(iRow - 1, iCol).Value = 0) Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If


===
Blank cells have a value of 0, so the code has to check for that.

Jen wrote:

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson

If one row has PL and one row has PC, then do the merge? In all other cases,
don't merge?

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???

I'm guessing that one of the PL/PC wins...

If yes to all that...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim iCol As Long

Dim FirstRow As Long
Dim LastRow As Long
Dim maxColsToCheck As Long
Dim EmplTypeCol As Long

maxColsToCheck = 50

Set wks = Worksheets("sheet1")

With wks
'where's the employee type column?
EmplTypeCol = .Range("b1").Column

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value .Cells(iRow - 1, "A").Value Then
'do nothing
Else
If (UCase(.Cells(iRow, EmplTypeCol).Value) = "PC" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PL") _
Or (UCase(.Cells(iRow, EmplTypeCol).Value) = "PL" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PC") _
Then
For iCol = 2 To maxColsToCheck
If iCol = EmplTypeCol Then
.Cells(iRow - 1, EmplTypeCol).Value _
= "PC" '"PL" ?????
Else
If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value _
= .Cells(iRow, iCol).Value
End If
End If
Next iCol
'delete that duplicate
.Rows(iRow).Delete
End If
End If
Next iRow
End With

End Sub

Look for some of those ??? where you might, er, probably will have to make
changes.

Jen wrote:

Thank you for your help.

I also have another worksheet that has a similar issue, it would need to
look at the name to see if it is duplicated and then look at employee type,
employee type may have two to three types: PC, A, O, PL.
What I would need the macro to do is combined to one row, only if it has PL
and PC as employee type, is there additonal VBA code that can be added to the
beginning of the macro you had sent me ? Thank you again for your asssitance,
you have saved me alot of time.

"Dave Peterson" wrote:

Yes. It would matter.

If it's a one time thing, I'd just Edit|replace 0 with O (zero with oh). Make
sure you check "match entire cell contents".

If you have to do it lots of times...

change this:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If

to:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" _
Or (IsEmpty(.Cells(iRow - 1, iCol).Value) = False _
And .Cells(iRow - 1, iCol).Value = 0) Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If


===
Blank cells have a value of 0, so the code has to check for that.

Jen wrote:

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson


--

Dave Peterson

Hi Dave, This in regards to the original inquiry, if there are three rows or
four of the same individual, is it possible to have the module combine to one
row, there are a few cases this does happen.. Also what does the module do if
rows are duplicated and the values equal the same value, may not be "O"

In regards to your questions on my other inquiry:
If one row has PL and one row has PC, then do the merge? Yes

In all other cases, don't merge? - Correct

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???
Does not matter

I'm guessing that one of the PL/PC wins... Either Or


Also wonder if their is more than one PC or PL type, for one individual will
this still work?



"Dave Peterson" wrote:

If one row has PL and one row has PC, then do the merge? In all other cases,
don't merge?

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???

I'm guessing that one of the PL/PC wins...

If yes to all that...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim iCol As Long

Dim FirstRow As Long
Dim LastRow As Long
Dim maxColsToCheck As Long
Dim EmplTypeCol As Long

maxColsToCheck = 50

Set wks = Worksheets("sheet1")

With wks
'where's the employee type column?
EmplTypeCol = .Range("b1").Column

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value .Cells(iRow - 1, "A").Value Then
'do nothing
Else
If (UCase(.Cells(iRow, EmplTypeCol).Value) = "PC" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PL") _
Or (UCase(.Cells(iRow, EmplTypeCol).Value) = "PL" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PC") _
Then
For iCol = 2 To maxColsToCheck
If iCol = EmplTypeCol Then
.Cells(iRow - 1, EmplTypeCol).Value _
= "PC" '"PL" ?????
Else
If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value _
= .Cells(iRow, iCol).Value
End If
End If
Next iCol
'delete that duplicate
.Rows(iRow).Delete
End If
End If
Next iRow
End With

End Sub

Look for some of those ??? where you might, er, probably will have to make
changes.

Jen wrote:

Thank you for your help.

I also have another worksheet that has a similar issue, it would need to
look at the name to see if it is duplicated and then look at employee type,
employee type may have two to three types: PC, A, O, PL.
What I would need the macro to do is combined to one row, only if it has PL
and PC as employee type, is there additonal VBA code that can be added to the
beginning of the macro you had sent me ? Thank you again for your asssitance,
you have saved me alot of time.

"Dave Peterson" wrote:

Yes. It would matter.

If it's a one time thing, I'd just Edit|replace 0 with O (zero with oh). Make
sure you check "match entire cell contents".

If you have to do it lots of times...

change this:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If

to:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" _
Or (IsEmpty(.Cells(iRow - 1, iCol).Value) = False _
And .Cells(iRow - 1, iCol).Value = 0) Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If


===
Blank cells have a value of 0, so the code has to check for that.

Jen wrote:

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson


--

Dave Peterson

Both of the routines handle each pair of rows separately.

So once you merge a couple rows into one, the next comparison will just do the
newly merged row and the next row.

If rows 3,4,5,6 all had the same key.

old 6 and old 5 are merged into a new 5
new 5 and old 4 are merged into a new 4
new 4 and old 3 are merged into a new 3

But depending on which routine you use, the merges won't occur. (The PC/PL was
more restricting on when merges would occur.)

And the existing data in top row of the comparison will win--unless the "merge"
can occur.

======
If there are more than 2 duplicate keys, you may want to make sure that your
data is sorted the way you want (I have no idea what that is).


Jen wrote:

Hi Dave, This in regards to the original inquiry, if there are three rows or
four of the same individual, is it possible to have the module combine to one
row, there are a few cases this does happen.. Also what does the module do if
rows are duplicated and the values equal the same value, may not be "O"

In regards to your questions on my other inquiry:
If one row has PL and one row has PC, then do the merge? Yes

In all other cases, don't merge? - Correct

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???
Does not matter

I'm guessing that one of the PL/PC wins... Either Or

Also wonder if their is more than one PC or PL type, for one individual will
this still work?


"Dave Peterson" wrote:

If one row has PL and one row has PC, then do the merge? In all other cases,
don't merge?

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???

I'm guessing that one of the PL/PC wins...

If yes to all that...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim iCol As Long

Dim FirstRow As Long
Dim LastRow As Long
Dim maxColsToCheck As Long
Dim EmplTypeCol As Long

maxColsToCheck = 50

Set wks = Worksheets("sheet1")

With wks
'where's the employee type column?
EmplTypeCol = .Range("b1").Column

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value .Cells(iRow - 1, "A").Value Then
'do nothing
Else
If (UCase(.Cells(iRow, EmplTypeCol).Value) = "PC" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PL") _
Or (UCase(.Cells(iRow, EmplTypeCol).Value) = "PL" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PC") _
Then
For iCol = 2 To maxColsToCheck
If iCol = EmplTypeCol Then
.Cells(iRow - 1, EmplTypeCol).Value _
= "PC" '"PL" ?????
Else
If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value _
= .Cells(iRow, iCol).Value
End If
End If
Next iCol
'delete that duplicate
.Rows(iRow).Delete
End If
End If
Next iRow
End With

End Sub

Look for some of those ??? where you might, er, probably will have to make
changes.

Jen wrote:

Thank you for your help.

I also have another worksheet that has a similar issue, it would need to
look at the name to see if it is duplicated and then look at employee type,
employee type may have two to three types: PC, A, O, PL.
What I would need the macro to do is combined to one row, only if it has PL
and PC as employee type, is there additonal VBA code that can be added to the
beginning of the macro you had sent me ? Thank you again for your asssitance,
you have saved me alot of time.

"Dave Peterson" wrote:

Yes. It would matter.

If it's a one time thing, I'd just Edit|replace 0 with O (zero with oh). Make
sure you check "match entire cell contents".

If you have to do it lots of times...

change this:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If

to:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" _
Or (IsEmpty(.Cells(iRow - 1, iCol).Value) = False _
And .Cells(iRow - 1, iCol).Value = 0) Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If


===
Blank cells have a value of 0, so the code has to check for that.

Jen wrote:

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

To understand your response below, Once I use the macro it merges the
duplicate rows, unfortunately if there is an employee with three rows of
information merges one of the rows to the other and still leaves me with two.
I tried rerunning the macro for it to look again for dupklicates and it does
not consolidate the two. Also seems if
I have the following:

Employee AK CA CT CO
JT M M 1 0
JT M O O M

It does not combine the two
to show:
Employee AK CA CT CO
JT M M 1 M

Any ideas?



"Dave Peterson" wrote:

Both of the routines handle each pair of rows separately.

So once you merge a couple rows into one, the next comparison will just do the
newly merged row and the next row.

If rows 3,4,5,6 all had the same key.

old 6 and old 5 are merged into a new 5
new 5 and old 4 are merged into a new 4
new 4 and old 3 are merged into a new 3

But depending on which routine you use, the merges won't occur. (The PC/PL was
more restricting on when merges would occur.)

And the existing data in top row of the comparison will win--unless the "merge"
can occur.

======
If there are more than 2 duplicate keys, you may want to make sure that your
data is sorted the way you want (I have no idea what that is).


Jen wrote:

Hi Dave, This in regards to the original inquiry, if there are three rows or
four of the same individual, is it possible to have the module combine to one
row, there are a few cases this does happen.. Also what does the module do if
rows are duplicated and the values equal the same value, may not be "O"

In regards to your questions on my other inquiry:
If one row has PL and one row has PC, then do the merge? Yes

In all other cases, don't merge? - Correct

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???
Does not matter

I'm guessing that one of the PL/PC wins... Either Or

Also wonder if their is more than one PC or PL type, for one individual will
this still work?


"Dave Peterson" wrote:

If one row has PL and one row has PC, then do the merge? In all other cases,
don't merge?

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???

I'm guessing that one of the PL/PC wins...

If yes to all that...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim iCol As Long

Dim FirstRow As Long
Dim LastRow As Long
Dim maxColsToCheck As Long
Dim EmplTypeCol As Long

maxColsToCheck = 50

Set wks = Worksheets("sheet1")

With wks
'where's the employee type column?
EmplTypeCol = .Range("b1").Column

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value .Cells(iRow - 1, "A").Value Then
'do nothing
Else
If (UCase(.Cells(iRow, EmplTypeCol).Value) = "PC" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PL") _
Or (UCase(.Cells(iRow, EmplTypeCol).Value) = "PL" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PC") _
Then
For iCol = 2 To maxColsToCheck
If iCol = EmplTypeCol Then
.Cells(iRow - 1, EmplTypeCol).Value _
= "PC" '"PL" ?????
Else
If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value _
= .Cells(iRow, iCol).Value
End If
End If
Next iCol
'delete that duplicate
.Rows(iRow).Delete
End If
End If
Next iRow
End With

End Sub

Look for some of those ??? where you might, er, probably will have to make
changes.

Jen wrote:

Thank you for your help.

I also have another worksheet that has a similar issue, it would need to
look at the name to see if it is duplicated and then look at employee type,
employee type may have two to three types: PC, A, O, PL.
What I would need the macro to do is combined to one row, only if it has PL
and PC as employee type, is there additonal VBA code that can be added to the
beginning of the macro you had sent me ? Thank you again for your asssitance,
you have saved me alot of time.

"Dave Peterson" wrote:

Yes. It would matter.

If it's a one time thing, I'd just Edit|replace 0 with O (zero with oh). Make
sure you check "match entire cell contents".

If you have to do it lots of times...

change this:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If

to:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" _
Or (IsEmpty(.Cells(iRow - 1, iCol).Value) = False _
And .Cells(iRow - 1, iCol).Value = 0) Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If


===
Blank cells have a value of 0, so the code has to check for that.

Jen wrote:

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Can you show the three rows of raw data?

Remember that only those cells with "0" get overwritten, too.

(And I didn't see if you were using the PL/PC version, too.)

Jen wrote:

To understand your response below, Once I use the macro it merges the
duplicate rows, unfortunately if there is an employee with three rows of
information merges one of the rows to the other and still leaves me with two.
I tried rerunning the macro for it to look again for dupklicates and it does
not consolidate the two. Also seems if
I have the following:

Employee AK CA CT CO
JT M M 1 0
JT M O O M

It does not combine the two
to show:
Employee AK CA CT CO
JT M M 1 M

Any ideas?

"Dave Peterson" wrote:

Both of the routines handle each pair of rows separately.

So once you merge a couple rows into one, the next comparison will just do the
newly merged row and the next row.

If rows 3,4,5,6 all had the same key.

old 6 and old 5 are merged into a new 5
new 5 and old 4 are merged into a new 4
new 4 and old 3 are merged into a new 3

But depending on which routine you use, the merges won't occur. (The PC/PL was
more restricting on when merges would occur.)

And the existing data in top row of the comparison will win--unless the "merge"
can occur.

======
If there are more than 2 duplicate keys, you may want to make sure that your
data is sorted the way you want (I have no idea what that is).


Jen wrote:

Hi Dave, This in regards to the original inquiry, if there are three rows or
four of the same individual, is it possible to have the module combine to one
row, there are a few cases this does happen.. Also what does the module do if
rows are duplicated and the values equal the same value, may not be "O"

In regards to your questions on my other inquiry:
If one row has PL and one row has PC, then do the merge? Yes

In all other cases, don't merge? - Correct

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???
Does not matter

I'm guessing that one of the PL/PC wins... Either Or

Also wonder if their is more than one PC or PL type, for one individual will
this still work?


"Dave Peterson" wrote:

If one row has PL and one row has PC, then do the merge? In all other cases,
don't merge?

And it doesn't matter which one (top or bottom of a pair of rows) has the PL
while the "opposite" one has the PC????

And when the employeecolumn merges, does PL win or PC or what' on top???

I'm guessing that one of the PL/PC wins...

If yes to all that...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim iCol As Long

Dim FirstRow As Long
Dim LastRow As Long
Dim maxColsToCheck As Long
Dim EmplTypeCol As Long

maxColsToCheck = 50

Set wks = Worksheets("sheet1")

With wks
'where's the employee type column?
EmplTypeCol = .Range("b1").Column

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value .Cells(iRow - 1, "A").Value Then
'do nothing
Else
If (UCase(.Cells(iRow, EmplTypeCol).Value) = "PC" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PL") _
Or (UCase(.Cells(iRow, EmplTypeCol).Value) = "PL" _
And UCase(.Cells(iRow - 1, EmplTypeCol).Value) = "PC") _
Then
For iCol = 2 To maxColsToCheck
If iCol = EmplTypeCol Then
.Cells(iRow - 1, EmplTypeCol).Value _
= "PC" '"PL" ?????
Else
If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value _
= .Cells(iRow, iCol).Value
End If
End If
Next iCol
'delete that duplicate
.Rows(iRow).Delete
End If
End If
Next iRow
End With

End Sub

Look for some of those ??? where you might, er, probably will have to make
changes.

Jen wrote:

Thank you for your help.

I also have another worksheet that has a similar issue, it would need to
look at the name to see if it is duplicated and then look at employee type,
employee type may have two to three types: PC, A, O, PL.
What I would need the macro to do is combined to one row, only if it has PL
and PC as employee type, is there additonal VBA code that can be added to the
beginning of the macro you had sent me ? Thank you again for your asssitance,
you have saved me alot of time.

"Dave Peterson" wrote:

Yes. It would matter.

If it's a one time thing, I'd just Edit|replace 0 with O (zero with oh). Make
sure you check "match entire cell contents".

If you have to do it lots of times...

change this:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If

to:

If UCase(.Cells(iRow - 1, iCol).Value) = "O" _
Or (IsEmpty(.Cells(iRow - 1, iCol).Value) = False _
And .Cells(iRow - 1, iCol).Value = 0) Then
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If


===
Blank cells have a value of 0, so the code has to check for that.

Jen wrote:

Thank you, this was very helpful. Save hours of work. Do I need to make sure
if cell value equals "O" (ALPHA). Would it matter if it was numeric?

"Jen" wrote:

I have a worksheet that has over 4000 rows and 50 columns of information. An
individual may be listed multiple times. I am looking to compare the
information and if duplicated consolidate the duplicated data to one row.

Here is an example of the worksheet:

colA colB colC colD colE colF colG
JSMITH O M DIST V O 1
JSMITH 1 M O O DIST 1

End result I would like to see:
colA colB colC colD colE colF colG
JSMITH 1 M DIST V DIST 1



If, JSMITH, is listed twice, have formula look at JSMITH's information for
each column and pull to one row, if "O" pull the other value if different.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson