When copy formula nothing happens.

[Tofik]

Hi guys, I try to understand how Excel works. I am so sorry for my posts same posts in the forum.?
I am very thankful for Dossfm0q user who helped and wrote the formula and spend his time helping me.?

He sent me a file which I attached below.




It is the first sheet which wrote Dossfm0q and here is everything perfect, but when I copy all information from Sheet1 and past(values) in Sheet2 (which below) and then when I copy and paste the formula in the same rows as did it Dossfm0q it doesn't work.

Welder ID 1	Welder ID 2	Tet Report No_		Welder ID	Test Report Number
PW-035	N/A	SRT-MQS-0005		N/A
PW-013	N/A	SRT-MQS-0006		PW-001	SRT-MQS-0021,SRT-MQS-0011,SRT-MQS-0013,SRT-MQS-0014
PW-030	N/A	SRT-MQS-0003		PW-003	SRT-MQS-0044
PW-007	N/A	SRT-MQS-0004		PW-005	SRT-MQS-0002,SRT-MQS-0015,SRT-MQS-0051/0029
PW-031	N/A	SRT-MQS-0008		PW-007	SRT-MQS-0004,SRT-MQS-0009,SRT-MQS-0016,SRT-MQS-0034
PW-001	N/A	SRT-MQS-0021		PW-013	SRT-MQS-0006,SRT-MQS-0017,SRT-MQS-0032
PW-034	N/A	SRT-MQS-0007		PW-026	SRT-MQS-0044
PW-035	N/A	SRT-MQS-0020		PW-030	SRT-MQS-0003,SRT-MQS-0012
PW-005	N/A	SRT-MQS-0002		PW-031	SRT-MQS-0008
PW-007	N/A	SRT-MQS-0009		PW-034	SRT-MQS-0007
PW-036	N/A	SRT-MQS-0010		PW-035	SRT-MQS-0005,SRT-MQS-0020
PW-030	N/A	SRT-MQS-0012		PW-036	SRT-MQS-0010
PW-001	N/A	SRT-MQS-0011		PW-037	SRT-MQS-0033
PW-049	N/A	SRT-MQS-0018		PW-040	SRT-MQS-0038,SRT-MQS-0039
PW-001	N/A	SRT-MQS-0013		PW-042	SRT-MQS-0022
PW-001	N/A	SRT-MQS-0014		PW-043	SRT-MQS-0024
PW-007	N/A	SRT-MQS-0016		PW-044	SRT-MQS-0019,SRT-MQS-0028
PW-005	N/A	SRT-MQS-0015		PW-045	SRT-MQS-0041
PW-013	N/A	SRT-MQS-0017		PW-048	SRT-MQS-0023,SRT-MQS-0020,SRT-MQS-0027
PW-050	N/A	SRT-MQS-0040		PW-049	SRT-MQS-0018,SRT-MQS-0050/0042,SRT-MQS-0020,SRT-MQS-0027
PW-042	N/A	SRT-MQS-0022		PW-050	SRT-MQS-0040,SRT-MQS-0019,SRT-MQS-0028
PW-049	N/A	SRT-MQS-0050/0042		PW-067	SRT-MQS-0030
PW-048	N/A	SRT-MQS-0023
PW-050	PW-044	SRT-MQS-0019
PW-040	N/A	SRT-MQS-0038
PW-040	N/A	SRT-MQS-0039
PW-045	N/A	SRT-MQS-0041
PW-049	PW-048	SRT-MQS-0020
PW-044	PW-050	SRT-MQS-0028
PW-067	N/A	SRT-MQS-0030
PW-048	PW-049	SRT-MQS-0027
PW-005	N/A	SRT-MQS-0051/0029
PW-043	N/A	SRT-MQS-0024
PW-013	N/A	SRT-MQS-0032
PW-007	N/A	SRT-MQS-0034
PW-003	PW-026	SRT-MQS-0044
PW-037	N/A	SRT-MQS-0033

Sheet 2 The same rows and formula but the formula doesn't work. Why?
I heard about ctrl+shift+enter which add { } inside the formula but when I do it it doesn't happen.
My excel version is 2016.

Welder ID 1	Welder ID 2	Tet Report No_		Welder ID	Test Report Number
PW-035	N/A	SRT-MQS-0005		0
PW-013	N/A	SRT-MQS-0006		0
PW-030	N/A	SRT-MQS-0003		0
PW-007	N/A	SRT-MQS-0004		0
PW-031	N/A	SRT-MQS-0008		0
PW-001	N/A	SRT-MQS-0021		0
PW-034	N/A	SRT-MQS-0007		0
PW-035	N/A	SRT-MQS-0020		0
PW-005	N/A	SRT-MQS-0002		0
PW-007	N/A	SRT-MQS-0009		0
PW-036	N/A	SRT-MQS-0010		0
PW-030	N/A	SRT-MQS-0012		0
PW-001	N/A	SRT-MQS-0011		0
PW-049	N/A	SRT-MQS-0018		0
PW-001	N/A	SRT-MQS-0013		0
PW-001	N/A	SRT-MQS-0014		0
PW-007	N/A	SRT-MQS-0016		0
PW-005	N/A	SRT-MQS-0015		0
PW-013	N/A	SRT-MQS-0017		0
PW-050	N/A	SRT-MQS-0040		0
PW-042	N/A	SRT-MQS-0022		0
PW-049	N/A	SRT-MQS-0050/0042		0
PW-048	N/A	SRT-MQS-0023
PW-050	PW-044	SRT-MQS-0019
PW-040	N/A	SRT-MQS-0038
PW-040	N/A	SRT-MQS-0039
PW-045	N/A	SRT-MQS-0041
PW-049	PW-048	SRT-MQS-0020
PW-044	PW-050	SRT-MQS-0028
PW-067	N/A	SRT-MQS-0030
PW-048	PW-049	SRT-MQS-0027
PW-005	N/A	SRT-MQS-0051/0029
PW-043	N/A	SRT-MQS-0024
PW-013	N/A	SRT-MQS-0032
PW-007	N/A	SRT-MQS-0034
PW-003	PW-026	SRT-MQS-0044
PW-037	N/A	SRT-MQS-0033

And as I understood Module works in a whole file ( Sheet and Sheet 2 ).

The mystery of the century for me. ???
Thank you, guys.

Row E1
=IFERROR(@INDEX($A$2:$B$200;CEILING(AGGREGATE(15;6;((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/((COUNTIF($A$2:$B$200;"<"&$A$2:$B$200)+($A$2:$B$200<>""))+(($A$2:$B$200<>"")*(--($A$2:$B$200<>"N/A"))*--(ISNUMBER($A$2:$B$200)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$200)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$200;E$1:E2))=1);1);COLUMNS($A$2:$B$200))/COLUMNS($A$2:$B$200);MOD(AGGREGATE(15;6;((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/((COUNTIF($A$2:$B$200;"<"&$A$2:$B$200)+($A$2:$B$200<>""))+(($A$2:$B$200<>"")*(--($A$2:$B$200<>"N/A"))*--(ISNUMBER($A$2:$B$200)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$200)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$200;E$1:E2))=1);1)-1;COLUMNS($A$2:$B$200))+1);"")
Row F1
=@JoinTxt($A$2:$B$38;E3;$C$2:$C$38)

 

[maabadi]

Change code to this (Add dim for T & Txt):

Public Function JoinTXT(Rng1 As Range, Rng2 As Range, Rng3 As Range)
Dim R1 As Range, R2 As Range, Txt As String, T As String
Txt = ""
For Each R1 In Rng1
  If R1.Value = Rng2.Value Then
    For Each R2 In Rng3
    T = R2.Value
     If InStr(1, Txt, Cells(R1.Row, 3).Value, vbTextCompare) = 0 Then
     Txt = Txt & IIf(Txt <> "", ",", "") & Cells(R1.Row, 3).Value
     End If
    Next
  End If
Next
JoinTXT = Txt
End Function

Then Use this formula at F2:

=JoinTXT($A$2:$A$38,E2,$C$2:$C$38)

 

[Tofik]

UP