PDA

View Full Version : Solved: Duplicate Data Concatenating



krishhi
02-23-2010, 10:21 PM
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. :cloud9:

Any help on this situation?

Thanks in Advance,
Krrish

domfootwear
02-23-2010, 10:51 PM
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. :cloud9:

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
02-23-2010, 10:59 PM
@ domfootwear (http://www.vbaexpress.com/forum/member.php?u=28003)
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
02-25-2010, 04:03 AM
Guys,

any solutions please?

SamT
02-25-2010, 07:11 AM
See if this works for you


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

krishhi
02-25-2010, 08:45 AM
See if this works for you


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

Thanks,
Let me try.

krishhi
02-25-2010, 09:15 PM
See if this works for you


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


nope, it's not working.:(

GTO
02-26-2010, 03:34 AM
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
02-26-2010, 06:19 AM
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
:bow:

domfootwear
03-18-2010, 05:55 PM
Dear Sir,

Pls help me the file in the att.

Thanks so much

krishhi
04-22-2010, 01:47 AM
Dear Sir,

Pls help me the file in the att.

Thanks so much

@ hi domfootwear :hi:

What do you need?

Can you explain more clearly.