need formula to look on 2 same numbers

[Tartesos]

I need help wiht a formula. I have a A column in sheet 1. In that column from A to A3000 I can type a ref. number. The data on B and C are taken fron sheet2 and sheet3.


A B C
<TABLE style="WIDTH: 224pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=299 border=0><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 101pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=135 height=20>2345</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 74pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=99> front side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 49pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=65>10"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>4567</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> back side </TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">3"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5432</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> front side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">10"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5432</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> front side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">3"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5467 </TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> right side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">10"</TD></TR></TBODY></TABLE>

What I was hoping is that you guys can help me is with a formula that will look into A and if it finds a duplicate number, it will copy the value in C from all the duplicates and add them to the firts duplicate number on the C column with a coma (, ) between them.

In this exem. the result should be:


A B C
<TABLE style="WIDTH: 224pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=299 border=0><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 101pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=135 height=20> 2345 </TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 74pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=99> front side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 49pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=65>10"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>4567</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> back side </TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">3"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5432</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> front side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">10",3"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5432</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> front side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5467</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> right side</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">10"</TD></TR></TBODY></TABLE>

I have situations with more than one duplicate number so in some cases 5432 can be 4 times there with different data on C , like: 10" on the firts , 3", 6 " , 7" and so on. Can anyone give me a hand with this please??

Regards.

 

[venkat1926]

the sample data is from A1 t 0 B6 as follow

<table width="138" border="0" cellpadding="0" cellspacing="0"><col style="width: 72pt;" width="96"> <col style="width: 32pt;" width="42"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 72pt;" width="96" height="17">ref no. </td> <td style="width: 32pt;" width="42">hdng2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2345 front side</td> <td>10"</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">4567back side </td> <td>3"</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">5432front side</td> <td>10"</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">5432front side</td> <td>3"</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">5467right side</td> <td>10"</td> </tr> </tbody></table>
try this macro"test" you get the results in rows 11 and down
the other macro undo is to undo the result of the macro for reteting.

Sub test()
Dim ra As Range, rfilt As Range, cfind As Range, cfilt As Range, add As String

Set rfilt = Range("A1").End(xlDown).Offset(5, 0)
Set ra = Range(Range("A1"), Range("A1").End(xlDown))
ra.AdvancedFilter action:=xlFilterCopy, copytorange:=rfilt, unique:=True
Set rfilt = Range(rfilt.Offset(1, 0), rfilt.End(xlDown))
For Each cfilt In rfilt
Set cfind = ra.Cells.Find(what:=cfilt.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
add = cfind.Address
cfilt.Offset(0, 1) = cfind.Offset(0, 1)
Else
GoTo nextcfilt
End If
Do
Set cfind = ra.Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
cfilt.End(xlToRight).Offset(0, 1) = cfind.Offset(0, 1)
Loop
nextcfilt:
Next cfilt
Columns("A:B").AutoFit
End Sub

Sub undo()
Dim r As Range
Set r = Range("A1").End(xlDown).Offset(5, 0)
Range(r, Cells(Rows.Count, "A")).EntireRow.Delete
End Sub

 

[Marcelo Branco]

Another possible solution

Copy this UDF to a standard module

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

A B C D
<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=282><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64 align=right>2345


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=67>front side</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>10"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=87>10"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>4567</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>back side </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>3"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>3"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>5432</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>front side</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>10"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>10", 3", 6", 7"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>5432</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>front side</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>3"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>5467</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>right side</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>10"


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2660002 class=xl63>10"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>5432</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>front side</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>6"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>5432</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>front side</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>7"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR></TBODY></TABLE>

Array-Formula in D1
=IF(COUNTIF($A$1:$A1,A1)=1,SUBSTITUTE(Aconcat(IF($A$1:$A$100=A1,", "&$C$1:$C$100,"")),", ","",1),"")
Ctrl+Shift+Enter
copy down

HTH

M.

 

[Tartesos]

Thank you guys I'm going to try this options, really, thanks ...

Regards

 

[Tartesos]

Marcelo I tried your formula and I get the #NAME? error on the cells where the output data should be showing.

This is how it looks on after making the coorect changes.

=IF(COUNTIF($B$4:$B4,B4)=1,SUBSTITUTE(Aconcat(IF($B$4:$B$2488=B1,", "&$AH$4:$AH$2488,"")),", ","",1),"")

B will do the A funtion on the exemp.
AH on sheet 2 is the is the C on the exemp.
AH on sheet 1 is D....
Do I have to specify that AH (C) is on another sheet?
The code is on the sheet 1 where the information will be show, do I need to add this code in the sheet 2 ?? ...
Please, explain it to me ....

Thanks

 

[Marcelo Branco]

Hi Tartesos,

1. Did you copy the Aconcat function to a standard module?
To do it
Ctl+C to copy the function exactly as i posted it
ALT+F11 to open VBEditor
Click on Insert pick Module
Paste (Ctrl+V) in the right-panel

The error #NAME indicates that Excel is not finding the function

2. In which columns exactly are your data A B C and starting at row 4?

3.The formula goes in D4 ?

HTH

M.

 

[Tartesos]

Ok, I did what you told me and the #name error is gone but the cells are empty no result are been show on these cells.

2. In which columns exactly are your data A B C and starting at row 4?

They start on row 4 and the data goes like this:

A in my exempl. will be B on my file on sheet 1.
From B4 to B3000. On this cells I type the ref. numbers I'm working with.
On Sheet 2 I have all this ref. numbers with their correct information that are on a number of columns. I use vlookup formulas to get information from the colums on sheet 2 relate to the ref. numbers.
Tht information will be show on columns on sheet 1 when you type any of the ref. numbers. Is like a database search file.

The B from my exempl. will be the title column on my real file that is K column on the sheet 2. So if you type a ref. number it will show the title on the sheet 1.

C on my exemp. will be AH column on my real file in sheet 2. On the AH column on sheet 2 I have the information in 10", 3" or even AV-4578 and so on. this the information I need to have on the sheet 1 on the same column name, AH.

I hope this can clear out a bit the situation. Now, the problem is that when I get duplicate numbers on the B column (real file - A on my exemp.). I need to have the information on the AH column in sheet 1 (real file - C on my exemp.), from the AH column in sheet 2.

Check this image for more details.

Thanks for your help so far.

 

[Marcelo Branco]

Hi,

I'm supposing the data on Sheet2 are in AH4:AH3000

If so try this array-formula in Sheet1 AH4
=IF(COUNTIF($B$4:B4,B4)=1,SUBSTITUTE(ACONCAT(IF($B$4:$B$3000=B4,", "&Sheet2!$AH$4:$AH$3000,"")),", ","",1),"")

Confirm with Ctrl+Shift+Enter (not just Enter)

If everything works ok, Excel wraps the formula with curly-braces

You see this in the formula-bar
{=IF(COUNTIF($B$4:B4,B4)=1,SUBSTITUTE(ACONCAT(IF($B$4:$B$3000=B4,", "&Sheet2!$AH$4:$AH$3000,"")),", ","",1),"")}

copy down till you need

HTH

M.

 

[Tartesos]

Hello again,

Marcelo thanks for your help, I did what you told me and everything looks good now, the only problem is that the formula finds duplicates but if there is not information on the AH column on the sheet 2 in puts the ",,,," and no information, what is normal. In my file sometimes it does happens that some of this ref. numbers have not information on the AH column on sheet 2. I think that the formula looks for the duplicates and when it finds them adds the ,,,, with no information between.
I was wondering if there is something I can do to avoid that. There is an easy solution for this? ..
I have also a question, if I want to change the comas , for ; where can I do it?
On this part ,"")),", ","",1),"")} ?

For the rest, the formula is just perfect, it save me tons of hours of work , thanks so mucht for your help.

Regards

 

[Marcelo Branco]

Hello again,

Marcelo thanks for your help, I did what you told me and everything looks good now, the only problem is that the formula finds duplicates but if there is not information on the AH column on the sheet 2 in puts the ",,,," and no information, what is normal. In my file sometimes it does happens that some of this ref. numbers have not information on the AH column on sheet 2. I think that the formula looks for the duplicates and when it finds them adds the ,,,, with no information between.
I was wondering if there is something I can do to avoid that. There is an easy solution for this? ..
I have also a question, if I want to change the comas , for ; where can I do it?
On this part ,"")),", ","",1),"")} ?

For the rest, the formula is just perfect, it save me tons of hours of work , thanks so mucht for your help.

Regards

You are welcome!

To use ; instead of , maybe this (i've not tested, but i think it should work)

=IF(COUNTIF($B$4:B4,B4)=1,SUBSTITUTE(ACONCAT(IF($B$4:$B$3000=B4,"; "&Sheet2!$AH$4:$AH$3000,"")),"; ","",1),"")

Ctrl+Shift+Enter
copy down

Later i'll think about the problem of no-data in AH column of sheet2

M.