Solved: Duplicate Data Concatenating

[krishhi]

Hi there,

Can anyone solve this probelm

I have a data in two columns that is "A" and "B". In "A" i have task numbers like,

00526
00568
25688
00569
00585
00585
00569
00569
00569
25688
25689

In "B" i have Description of the task numbers.
Here i want the description of the Same task numbers into Column "C".
for example, see above, the task number "00585" and "00569" has duplicate entry under the first one then i need the description of the second one into "C" column with first One.
and in another case, see the task number "25688" also has duplicate but i don't want that data to combine. because the sequence is not under the first one.

I think you got my point.? if not find the image what i want.

Any help on this situation?

Thanks in Advance,
Krrish

[domfootwear]

Hi there,

Can anyone solve this probelm

I have a data in two columns that is "A" and "B". In "A" i have task numbers like,

00526
00568
25688
00569
00585
00585
00569
00569
00569
25688
25689

In "B" i have Description of the task numbers.
Here i want the description of the Same task numbers into Column "C".
for example, see above, the task number "00585" and "00569" has duplicate entry under the first one then i need the description of the second one into "C" column with first One.
and in another case, see the task number "25688" also has duplicate but i don't want that data to combine. because the sequence is not under the first one.

I think you got my point.? if not find the image what i want.

Any help on this situation?

Thanks in Advance,
Krrish

You can use this UDF:

Function MyLookUp(rLook, rng As Range, nColumn As Long)
Dim rCell As Range, Result
    MyLookUp = CVErr(xlErrNA)
      For Each rCell In rng
        If rCell = rLook Then
            Result = Result & ", " & rCell.Offset(, nColumn - 1)
        End If
    Next rCell
       If Result <> "" Then
        Result = Right(Result, Len(Result) - 1)
        MyLookUp = Result
    End If
End Function

Pls see att. file

[krishhi]

@ domfootwear
Thanks for the reply,
But check once, it clubs the data which is not sequential. i.e. In example task number "00569"
I only need when there is a sequential duplicate, if the taks number has duplicate in other place(not sequential) i don't want that data.

for better understand please see my attached image.

[krishhi]

Guys,

any solutions please?

[SamT]

See if this works for you

[vba]
Option Explicit
Sub Duplicate_Data()
'First Select the Cells in Column A you wish to check
'For Duplicate Data
Dim i As Long
Dim j As Long
Dim CValue As String
With Selection
For i = 1 To Selection.Rows.Count
j = i
CValue = Range("B" & i).Value
'Check for duplicates
While Range("A" & j).Value = Range("A" & j + 1).Value
'Is Duplicate; Add column B to Cvalue
CValue = CValue & Chr(10) & Range("B" & j + 1).Value
j = j + 1
Wend
Range("C" & i).Value = CValue
i = j
Next
End With
Selection.Rows.Autofit
End Sub
[/vba]

[krishhi]

See if this works for you

[vba]
Option Explicit
Sub Duplicate_Data()
'First Select the Cells in Column A you wish to check
'For Duplicate Data
Dim i As Long
Dim j As Long
Dim CValue As String
With Selection
For i = 1 To Selection.Rows.Count
j = i
CValue = Range("B" & i).Value
'Check for duplicates
While Range("A" & j).Value = Range("A" & j + 1).Value
'Is Duplicate; Add column B to Cvalue
CValue = CValue & Chr(10) & Range("B" & j + 1).Value
j = j + 1
Wend
Range("C" & i).Value = CValue
i = j
Next
End With
Selection.Rows.Autofit
End Sub
[/vba]

Thanks,
Let me try.

[krishhi]

See if this works for you

[vba]
Option Explicit
Sub Duplicate_Data()
'First Select the Cells in Column A you wish to check
'For Duplicate Data
Dim i As Long
Dim j As Long
Dim CValue As String
With Selection
For i = 1 To Selection.Rows.Count
j = i
CValue = Range("B" & i).Value
'Check for duplicates
While Range("A" & j).Value = Range("A" & j + 1).Value
'Is Duplicate; Add column B to Cvalue
CValue = CValue & Chr(10) & Range("B" & j + 1).Value
j = j + 1
Wend
Range("C" & i).Value = CValue
i = j
Next
End With
Selection.Rows.Autofit
End Sub
[/vba]

nope, it's not working.

[GTO]

nope, it's not working.

Sam's example certainly worked for me. If you selected the values in column A, the results in Col C are what you asked for, excepting the vals are seperated by linefeeds rather than commas.

Simply change Chr(10) to Chr(44) and check the results. Given that you may have a header row, or that is, selection may start below row 1, you might try changing For i = 1... to:

For i = Selection.Row To Selection.Rows.Count + Selection.Row - 1

Hope that helps,

Mark

[krishhi]

Sam's example certainly worked for me. If you selected the values in column A, the results in Col C are what you asked for, excepting the vals are seperated by linefeeds rather than commas.

Simply change Chr(10) to Chr(44) and check the results. Given that you may have a header row, or that is, selection may start below row 1, you might try changing For i = 1... to:

For i = Selection.Row To Selection.Rows.Count + Selection.Row - 1

Hope that helps,

Mark

@GTO

Ya. I Got it now. I didn't select the cells. so it was not worked, now its works like Charm.

Thank you and Sams

[domfootwear]

Dear Sir,

Pls help me the file in the att.

Thanks so much

[krishhi]

Dear Sir,

Pls help me the file in the att.

Thanks so much

@ hi domfootwear

What do you need?

Can you explain more clearly.