Which Formula ?

T

Tofik

Board Regular
Joined
Feb 4, 2021
Messages
114
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi guys which simple formula I can use to fill empathy cells (Problem is Welder 1 and Welder 2 can be duplicated in different rows)
I found a website with a different variety of fformulars.
exceljet.net

1000 Excel Formulas

Over 1000 working Excel formulas with detailed explanations, videos, and related links. Includes key functions like VLOOKUP, XLOOKUP, INDEX & MATCH, FILTER, RANK, ROUND, AVERAGE, COUNTIFS, SUMIFS, UNIQUE, SORT, TEXTSPLIT, and more.
exceljet.net exceljet.net







Welder 1Welder 2RT ReportWelder RT report
PW-035N/ASRT-MQS-0005
PW-013N/ASRT-MQS-0006
PW-030N/ASRT-MQS-0003
PW-007N/ASRT-MQS-0004
PW-031N/ASRT-MQS-0008
PW-001N/ASRT-MQS-0021
PW-034N/ASRT-MQS-0007
PW-035N/ASRT-MQS-0001
PW-005N/ASRT-MQS-0002
PW-007N/ASRT-MQS-0009
PW-036N/ASRT-MQS-0010
PW-030N/ASRT-MQS-0012
PW-001N/ASRT-MQS-0011
PW-049N/ASRT-MQS-0018
PW-001N/ASRT-MQS-0013
PW-001N/ASRT-MQS-0014
PW-007N/ASRT-MQS-0016
PW-005N/ASRT-MQS-0015
PW-013N/ASRT-MQS-0017
PW-050N/ASRT-MQS-0040
PW-042N/ASRT-MQS-0022
PW-049N/ASRT-MQS-0050/0042
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
So, what you want to do if found duplicate like PW-048? Which RT Report you want to choose? This probably the question asked.
 
Upvote 0
www.mrexcel.com

Smart Table

Hi guys. I have a question about my table. I work in a company which has very big tables in excel. I try to explain my problem and if you can help me it will be perfect because I lost a lot of time for research and don't know how I should ask correctly from Google. 1)Welder 1 and Welder 2 can...
www.mrexcel.com www.mrexcel.com

what is your Excel Office Ver ?
 
Last edited:
Upvote 0
Dossfm0q said:
www.mrexcel.com

Smart Table

Hi guys. I have a question about my table. I work in a company which has very big tables in excel. I try to explain my problem and if you can help me it will be perfect because I lost a lot of time for research and don't know how I should ask correctly from Google. 1)Welder 1 and Welder 2 can...
www.mrexcel.com www.mrexcel.com

what is your Excel Office Ver ?
Click to expand...
2016
 
Upvote 0
Zot said:
So, what you want to do if found duplicate like PW-048? Which RT Report you want to choose? This probably the question asked.
Click to expand...
Hi, I just don't know how I can create a table with unique values. The problem is I have 2 columns with welders and those welders can duplicate.


1I have my own weld log where I keep and input all information about welds that made welders,Welder 1Welder 2RT reportsdate of weld Welder s which had RT test after their jobRT reports
PW-030PW-034SRT-MQS-001825.01.2021PW-01
PW-026PW-003SRT-MQS-001325.01.2021PW-02
PW-026PW-003SRT-MQS-001426.01.2021PW-03
2most time welders work in pairs because of big pipe size, they should make weld at the same time to avoid deformation after weld that’s why I have welder 1 and welder 2PW-035PW-036SRT-MQS-001626.01.2021PW-04
PW-029N/ASRT-MQS-001526.01.2021PW-05
PW-042N/ASRT-MQS-001726.01.2021PW-06
PW-049N/AN/A26.01.2021PW-07
PW-067N/AN/A26.01.2021PW-08
3after welding, we perform a radiography test to check the weld metal defectsPW-001PW-009N/A27.01.2021PW-09
PW-001PW-009N/A27.01.2021PW-10
PW-048N/AN/A27.01.2021PW-11
PW-035PW-036SRT-MQS-004027.01.2021PW-12
PW-030PW-034N/A27.01.2021PW-13
4Now I must understand which welder have successful result after RT test and how many tests he passed through the all-time workingPW-050PW-044N/A27.01.2021PW-14
PW-040N/AN/A28.01.2021PW-15
PW-040N/AN/A30.02.2021PW-16
PW-045N/AN/A30.02.2021PW-17
PW-049PW-048SRT-MQS-002230.02.2021PW-18
PW-044PW-050SRT-MQS-0050/004230.02.2021PW-19
PW-067N/AN/A28.01.2021PW-20
PW-045N/AN/A27.01.2021PW-21
PW-048PW-049N/A31.01.2021PW-22
…............…....................….....….............................



I must create a table without duplicates only Uniq RT reports for each welder

Welders Which RT report I have
pw-001
pw-002
pw-003
pw-004
pw-005
pw-006
pw-007
pw-008
pw-009
pw-010
pw-011
pw-012
pw-013
pw-014
pw-015
pw-016
pw-017
pw-018
pw-019
pw-020
pw-021
pw-022
 
Upvote 0
@Dossfm0q
Please post your solution to the board, so that everyone can see it without having to download a file.
See Rule#4 thanks.
 
Upvote 0
First copy Below to Normal Module
VBA Code: 
Public Function JoinTxt(Rng1 As Range, Rng2 As Range, Rng3 As Range)
Dim R1 As Range, R2 As Range
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


Which Formula (1).xlsm
ABCDEF
1Welder ID 1Welder ID 2Tet Report No_Welder IDTest Report Number
2PW-035N/ASRT-MQS-0005N/A
3PW-013N/ASRT-MQS-0006PW-001SRT-MQS-0021,SRT-MQS-0011,SRT-MQS-0013,SRT-MQS-0014
4PW-030N/ASRT-MQS-0003PW-003SRT-MQS-0044
5PW-007N/ASRT-MQS-0004PW-005SRT-MQS-0002,SRT-MQS-0015,SRT-MQS-0051/0029
6PW-031N/ASRT-MQS-0008PW-007SRT-MQS-0004,SRT-MQS-0009,SRT-MQS-0016,SRT-MQS-0034
7PW-001N/ASRT-MQS-0021PW-013SRT-MQS-0006,SRT-MQS-0017,SRT-MQS-0032
8PW-034N/ASRT-MQS-0007PW-026SRT-MQS-0044
9PW-035N/ASRT-MQS-0001PW-030SRT-MQS-0003,SRT-MQS-0012
10PW-005N/ASRT-MQS-0002PW-031SRT-MQS-0008
11PW-007N/ASRT-MQS-0009PW-034SRT-MQS-0007
12PW-036N/ASRT-MQS-0010PW-035SRT-MQS-0005,SRT-MQS-0001
13PW-030N/ASRT-MQS-0012PW-036SRT-MQS-0010
14PW-001N/ASRT-MQS-0011PW-037SRT-MQS-0033
15PW-049N/ASRT-MQS-0018PW-040SRT-MQS-0038,SRT-MQS-0039
16PW-001N/ASRT-MQS-0013PW-042SRT-MQS-0022
17PW-001N/ASRT-MQS-0014PW-043SRT-MQS-0024
18PW-007N/ASRT-MQS-0016PW-044SRT-MQS-0019,SRT-MQS-0028
19PW-005N/ASRT-MQS-0015PW-045SRT-MQS-0041
20PW-013N/ASRT-MQS-0017PW-048SRT-MQS-0023,SRT-MQS-0020,SRT-MQS-0027
21PW-050N/ASRT-MQS-0040PW-049SRT-MQS-0018,SRT-MQS-0050/0042,SRT-MQS-0020,SRT-MQS-0027
22PW-042N/ASRT-MQS-0022PW-050SRT-MQS-0040,SRT-MQS-0019,SRT-MQS-0028
23PW-049N/ASRT-MQS-0050/0042PW-067SRT-MQS-0030
24PW-048N/ASRT-MQS-0023  
25PW-050PW-044SRT-MQS-0019  
26PW-040N/ASRT-MQS-0038  
27PW-040N/ASRT-MQS-0039  
28PW-045N/ASRT-MQS-0041  
29PW-049PW-048SRT-MQS-0020  
30PW-044PW-050SRT-MQS-0028  
31PW-067N/ASRT-MQS-0030  
32PW-048PW-049SRT-MQS-0027  
33PW-005N/ASRT-MQS-0051/0029  
34PW-043N/ASRT-MQS-0024  
35PW-013N/ASRT-MQS-0032  
36PW-007N/ASRT-MQS-0034  
37PW-003PW-026SRT-MQS-0044  
38PW-037N/ASRT-MQS-0033  
Sheet2
Cell Formulas
RangeFormula
E2:E38E2=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:E1))=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:E1))=1),1)-1,COLUMNS($A$2:$B$200))+1),"")
F3:F38F3=JoinTxt($A$2:$B$38,E3,$C$2:$C$38)
 
Upvote 0
Dossfm0q said:
First copy Below to Normal Module
VBA Code: 
Public Function JoinTxt(Rng1 As Range, Rng2 As Range, Rng3 As Range)
Dim R1 As Range, R2 As Range
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


Which Formula (1).xlsm
ABCDEF
1Welder ID 1Welder ID 2Tet Report No_Welder IDTest Report Number
2PW-035N/ASRT-MQS-0005N/A
3PW-013N/ASRT-MQS-0006PW-001SRT-MQS-0021,SRT-MQS-0011,SRT-MQS-0013,SRT-MQS-0014
4PW-030N/ASRT-MQS-0003PW-003SRT-MQS-0044
5PW-007N/ASRT-MQS-0004PW-005SRT-MQS-0002,SRT-MQS-0015,SRT-MQS-0051/0029
6PW-031N/ASRT-MQS-0008PW-007SRT-MQS-0004,SRT-MQS-0009,SRT-MQS-0016,SRT-MQS-0034
7PW-001N/ASRT-MQS-0021PW-013SRT-MQS-0006,SRT-MQS-0017,SRT-MQS-0032
8PW-034N/ASRT-MQS-0007PW-026SRT-MQS-0044
9PW-035N/ASRT-MQS-0001PW-030SRT-MQS-0003,SRT-MQS-0012
10PW-005N/ASRT-MQS-0002PW-031SRT-MQS-0008
11PW-007N/ASRT-MQS-0009PW-034SRT-MQS-0007
12PW-036N/ASRT-MQS-0010PW-035SRT-MQS-0005,SRT-MQS-0001
13PW-030N/ASRT-MQS-0012PW-036SRT-MQS-0010
14PW-001N/ASRT-MQS-0011PW-037SRT-MQS-0033
15PW-049N/ASRT-MQS-0018PW-040SRT-MQS-0038,SRT-MQS-0039
16PW-001N/ASRT-MQS-0013PW-042SRT-MQS-0022
17PW-001N/ASRT-MQS-0014PW-043SRT-MQS-0024
18PW-007N/ASRT-MQS-0016PW-044SRT-MQS-0019,SRT-MQS-0028
19PW-005N/ASRT-MQS-0015PW-045SRT-MQS-0041
20PW-013N/ASRT-MQS-0017PW-048SRT-MQS-0023,SRT-MQS-0020,SRT-MQS-0027
21PW-050N/ASRT-MQS-0040PW-049SRT-MQS-0018,SRT-MQS-0050/0042,SRT-MQS-0020,SRT-MQS-0027
22PW-042N/ASRT-MQS-0022PW-050SRT-MQS-0040,SRT-MQS-0019,SRT-MQS-0028
23PW-049N/ASRT-MQS-0050/0042PW-067SRT-MQS-0030
24PW-048N/ASRT-MQS-0023  
25PW-050PW-044SRT-MQS-0019  
26PW-040N/ASRT-MQS-0038  
27PW-040N/ASRT-MQS-0039  
28PW-045N/ASRT-MQS-0041  
29PW-049PW-048SRT-MQS-0020  
30PW-044PW-050SRT-MQS-0028  
31PW-067N/ASRT-MQS-0030  
32PW-048PW-049SRT-MQS-0027  
33PW-005N/ASRT-MQS-0051/0029  
34PW-043N/ASRT-MQS-0024  
35PW-013N/ASRT-MQS-0032  
36PW-007N/ASRT-MQS-0034  
37PW-003PW-026SRT-MQS-0044  
38PW-037N/ASRT-MQS-0033  
Sheet2
Cell Formulas
RangeFormula
E2:E38E2=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:E1))=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:E1))=1),1)-1,COLUMNS($A$2:$B$200))+1),"")
F3:F38F3=JoinTxt($A$2:$B$38,E3,$C$2:$C$38)
Click to expand...
SOMETHING GOING WRONG ((((

DRAWINGISOSPOOLINCWeld №Welder 1Welder 2RT DateRT ReportRT ResultRej LengthImperfection TypeRT Rej LengthRej Welder
HS523217-01-PIP-ISO-60105-003010-N2-0160105-AA23-NISP-0110WS01PW-001PW-002N/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-60105-002810-N2-0160105-AA23-NISP-0110WS01PW-009N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-60105-002410-N2-0160105-AA23-NISP-0110WS01PW-005N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-10207-001410-LLC-0110207-AA23-HCSP-0310WS04PW-007N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-10207-001410-LLC-0110207-AA23-HCSP-0510WS08PW-030N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-10207-001410-LLC-0110207-AA23-HCSP-0210WS02PW-034N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-10207-001410-LLC-0110207-AA23-HCSP-0410WS06PW-013N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-10207-001210-LLC-0110207-AA23-HCSP-0510WS08PW-026N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-10207-001810-LLC-0110207-AA23-HCSP-0510WS08PW-035N/A22.10.2020SRT-MQS-0005ACC100X400000#NAME?
HS523217-01-PIP-ISO-60105-002410-N2-0160105-AA23-NISP-0210WS03PW-035N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-60105-003010-N2-0160105-AA23-NISP-0210WS03PW-036N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-10207-001810-LLC-0110207-AA23-HCSP-0410WS06PW-013N/A22.10.2020SRT-MQS-0006ACC100X400000#NAME?
HS523217-01-PIP-ISO-00211-001612-P-0100211-BA51-NISP-0112WS01PW-005N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-60105-003110-N2-0160105-AA23-NISP-0310WS07PW-001N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-60105-003110-N2-0160105-AA23-NISP-0310WS08PW-001N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-00211-001812-P-0100211-BA51-NISP-0112WS01PW-030N/A22.10.2020SRT-MQS-0003ACC100X450000#NAME?
HS523217-01-PIP-ISO-20003-000912-P-0120003-BA51-ESP-0412WS08PW-009N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-20003-000912-P-0120003-BA51-ESP-0212WS04PW-034N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-20003-001112-P-0120003-BA51-ESP-0312WS05PW-007N/A22.10.2020SRT-MQS-0004ACC100X450000#NAME?
HS523217-01-PIP-ISO-20003-001112-P-0120003-BA51-ESP-0112WS01PW-026N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-20003-001112-P-0120003-BA51-ESP-0212WS03PW-029N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-00211-001812-P-0100211-BA51-NISP-0412WS07PW-036N/AN/AN/AN/AN/AN/A00#NAME?
HS523217-01-PIP-ISO-00211-001612-P-0100211-BA51-NISP-0312WS05PW-035N/AN/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-20003-000512-P-0120003-BA51-ESP-0412WS07PW-031N/A22.10.2020SRT-MQS-0008ACC100X45000
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
When copy formula nothing happens.
Replies
2
Views
566
Tofik
T
P
  • Solved
Macro which changes the inside formula
2
Replies
18
Views
205
PetarTen
P
L
  • Solved
Using CELL function result as part of range in an AVERAGE formula
Replies
3
Views
342
Fluff
Fluff
R
  • Question Question
I need to return a lookup on a table once a slicer has been selected.
Replies
3
Views
472
JamesCanale
JamesCanale
J
  • Solved
sumif or sumifs formula which does not sum if one or more of the values is zero.
2
Replies
12
Views
1K
Eric W
Eric W

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top