Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance handling with duplicates
most of the code and help is on removing duplicates. however, my problem is
that my duplicates data occur becos my other dept key in data w/o a standard format and we can not make changes to the main data sheet except to sort it out to other sheet ourself.I will need to combine data from both duplicates into one data. Illustration: orginal data: Name Jan Feb March alan 0 0 1 alan 1 0 0 what i want in the end: Name Jan Feb March alan 1 0 1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance handling with duplicates
Sub Test()
Dim iLastRow As Long Dim iLastCol As Long Dim i As Long, j As Long Dim rng As Range Application.ScreenUpdating = False iLastRow = Cells(Rows.Count, "A").End(xlUp).Row iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column For i = 1 To iLastRow If Cells(i, "A").Value = Cells(i + 1, "A").Value Then For j = 2 To iLastCol Cells(i + 1, j).Value = Cells(i + 1, j).Value + _ Cells(i, j).Value If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If Next j End If Next i If Not rng Is Nothing Then rng.Delete End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "violet" wrote in message ... most of the code and help is on removing duplicates. however, my problem is that my duplicates data occur becos my other dept key in data w/o a standard format and we can not make changes to the main data sheet except to sort it out to other sheet ourself.I will need to combine data from both duplicates into one data. Illustration: orginal data: Name Jan Feb March alan 0 0 1 alan 1 0 0 what i want in the end: Name Jan Feb March alan 1 0 1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance handling with duplicates
hi,but i still got a problem. i don't want all the colunm to be add up..only
for selected column. in addition, i gt null value in the column where i need to check for duplicates.i will like the row with null in value in that column to remain w/o any changes. so how can i do it? "Bob Phillips" wrote: Sub Test() Dim iLastRow As Long Dim iLastCol As Long Dim i As Long, j As Long Dim rng As Range Application.ScreenUpdating = False iLastRow = Cells(Rows.Count, "A").End(xlUp).Row iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column For i = 1 To iLastRow If Cells(i, "A").Value = Cells(i + 1, "A").Value Then For j = 2 To iLastCol Cells(i + 1, j).Value = Cells(i + 1, j).Value + _ Cells(i, j).Value If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If Next j End If Next i If Not rng Is Nothing Then rng.Delete End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "violet" wrote in message ... most of the code and help is on removing duplicates. however, my problem is that my duplicates data occur becos my other dept key in data w/o a standard format and we can not make changes to the main data sheet except to sort it out to other sheet ourself.I will need to combine data from both duplicates into one data. Illustration: orginal data: Name Jan Feb March alan 0 0 1 alan 1 0 0 what i want in the end: Name Jan Feb March alan 1 0 1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
An Advance IF | Excel Worksheet Functions | |||
I need a little help...thanks in advance | Excel Discussion (Misc queries) | |||
Here's one for you...(thanks for the help in advance) | Excel Discussion (Misc queries) | |||
Error handling with a handling routine | Excel Programming | |||
Help Please! Thanks in advance. | Excel Programming |