When copy formula nothing happens.

Tofik

Board Regular
Joined
Feb 4, 2021
Messages
92
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 1Welder ID 2Tet Report No_Welder IDTest Report Number
PW-035N/ASRT-MQS-0005N/A
PW-013N/ASRT-MQS-0006PW-001SRT-MQS-0021,SRT-MQS-0011,SRT-MQS-0013,SRT-MQS-0014
PW-030N/ASRT-MQS-0003PW-003SRT-MQS-0044
PW-007N/ASRT-MQS-0004PW-005SRT-MQS-0002,SRT-MQS-0015,SRT-MQS-0051/0029
PW-031N/ASRT-MQS-0008PW-007SRT-MQS-0004,SRT-MQS-0009,SRT-MQS-0016,SRT-MQS-0034
PW-001N/ASRT-MQS-0021PW-013SRT-MQS-0006,SRT-MQS-0017,SRT-MQS-0032
PW-034N/ASRT-MQS-0007PW-026SRT-MQS-0044
PW-035N/ASRT-MQS-0020PW-030SRT-MQS-0003,SRT-MQS-0012
PW-005N/ASRT-MQS-0002PW-031SRT-MQS-0008
PW-007N/ASRT-MQS-0009PW-034SRT-MQS-0007
PW-036N/ASRT-MQS-0010PW-035SRT-MQS-0005,SRT-MQS-0020
PW-030N/ASRT-MQS-0012PW-036SRT-MQS-0010
PW-001N/ASRT-MQS-0011PW-037SRT-MQS-0033
PW-049N/ASRT-MQS-0018PW-040SRT-MQS-0038,SRT-MQS-0039
PW-001N/ASRT-MQS-0013PW-042SRT-MQS-0022
PW-001N/ASRT-MQS-0014PW-043SRT-MQS-0024
PW-007N/ASRT-MQS-0016PW-044SRT-MQS-0019,SRT-MQS-0028
PW-005N/ASRT-MQS-0015PW-045SRT-MQS-0041
PW-013N/ASRT-MQS-0017PW-048SRT-MQS-0023,SRT-MQS-0020,SRT-MQS-0027
PW-050N/ASRT-MQS-0040PW-049SRT-MQS-0018,SRT-MQS-0050/0042,SRT-MQS-0020,SRT-MQS-0027
PW-042N/ASRT-MQS-0022PW-050SRT-MQS-0040,SRT-MQS-0019,SRT-MQS-0028
PW-049N/ASRT-MQS-0050/0042PW-067SRT-MQS-0030
PW-048N/ASRT-MQS-0023
PW-050PW-044SRT-MQS-0019
PW-040N/ASRT-MQS-0038
PW-040N/ASRT-MQS-0039
PW-045N/ASRT-MQS-0041
PW-049PW-048SRT-MQS-0020
PW-044PW-050SRT-MQS-0028
PW-067N/ASRT-MQS-0030
PW-048PW-049SRT-MQS-0027
PW-005N/ASRT-MQS-0051/0029
PW-043N/ASRT-MQS-0024
PW-013N/ASRT-MQS-0032
PW-007N/ASRT-MQS-0034
PW-003PW-026SRT-MQS-0044
PW-037N/ASRT-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 1Welder ID 2Tet Report No_Welder IDTest Report Number
PW-035N/ASRT-MQS-00050
PW-013N/ASRT-MQS-00060
PW-030N/ASRT-MQS-00030
PW-007N/ASRT-MQS-00040
PW-031N/ASRT-MQS-00080
PW-001N/ASRT-MQS-00210
PW-034N/ASRT-MQS-00070
PW-035N/ASRT-MQS-00200
PW-005N/ASRT-MQS-00020
PW-007N/ASRT-MQS-00090
PW-036N/ASRT-MQS-00100
PW-030N/ASRT-MQS-00120
PW-001N/ASRT-MQS-00110
PW-049N/ASRT-MQS-00180
PW-001N/ASRT-MQS-00130
PW-001N/ASRT-MQS-00140
PW-007N/ASRT-MQS-00160
PW-005N/ASRT-MQS-00150
PW-013N/ASRT-MQS-00170
PW-050N/ASRT-MQS-00400
PW-042N/ASRT-MQS-00220
PW-049N/ASRT-MQS-0050/00420
PW-048N/ASRT-MQS-0023
PW-050PW-044SRT-MQS-0019
PW-040N/ASRT-MQS-0038
PW-040N/ASRT-MQS-0039
PW-045N/ASRT-MQS-0041
PW-049PW-048SRT-MQS-0020
PW-044PW-050SRT-MQS-0028
PW-067N/ASRT-MQS-0030
PW-048PW-049SRT-MQS-0027
PW-005N/ASRT-MQS-0051/0029
PW-043N/ASRT-MQS-0024
PW-013N/ASRT-MQS-0032
PW-007N/ASRT-MQS-0034
PW-003PW-026SRT-MQS-0044
PW-037N/ASRT-MQS-0033



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

1613117177922.png



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)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,357
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Change code to this (Add dim for T & Txt):
VBA Code:
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:
Excel Formula:
=JoinTXT($A$2:$A$38,E2,$C$2:$C$38)
 

Forum statistics

Threads
1,141,309
Messages
5,705,675
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top