Smart Table

[Tofik]

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 be duplicated in the first table, but not in the second.
2)Test reports shouldn't duplicate in the second table. (See line 7 same welder).
3) Status can be different. ( ACC or Reject or ACC, REJ at the same time).

P.S Thank you, guys.

Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Welder ID 1	Welder ID 2	Tet Report No_	Status			Welder ID	Test Reports No_	Status
2	PW-001	PW-009	TKS-KOO-0005	ACC			PW-001	TKS-KOO-0005, TKS-KOO-0017	ACC, ACC
3	PW-009	PW-034	TKS-KOO-0006	ACC			PW-002
4	PW-005	PW-007	TKS-KOO-0007	ACC			PW-003
5	PW-007	PW-007	TKS-KOO-0008	ACC			PW-004
6	PW-030	PW-029	TKS-KOO-0009	ACC			PW-005
7	PW-034	PW-034	TKS-KOO-0010	REJ			PW-006
8	PW-013	PW-035	TKS-KOO-0011	REJ			PW-007
9	PW-026	PW-026	TKS-KOO-0012	ACC			PW-008
10	PW-035	PW-013	TKS-KOO-0013	ACC			PW-009
11	PW-035	n/a	TKS-KOO-0014	n/a			PW-010
12	PW-036	n/a	TKS-KOO-0015	n/a			PW-011
13	PW-013	PW-007	TKS-KOO-0016	ACC			PW-012
14	PW-005	PW-001	TKS-KOO-0017	ACC			PW-013
15	PW-001	PW-009	TKS-KOO-0018	ACC			PW-014
16	PW-001	PW-005	TKS-KOO-0019	ACC			PW-015
17	PW-030	PW-007	TKS-KOO-0020	ACC			PW-016
18	PW-009	PW-034	TKS-KOO-0021	ACC			PW-017
19	PW-034	PW-007	TKS-KOO-0022	ACC			PW-018
20	PW-007	PW-026	TKS-KOO-0023	ACC			PW-019
21	PW-026	n/a	TKS-KOO-0024	n/a			PW-020
22	PW-029	n/a	TKS-KOO-0025	n/a			PW-021
23
24
25
Sheet1

 

[Dossfm0q]

Click File

 

[Peter_SSs]

Test reports shouldn't duplicate in the second table.

Did I misunderstand what you meant by the above? I note that you have marked post #6 as the solution but, for example, in cell H11 of that post "TKS-KOO-0010" is listed twice. Is that what you want?

If you are happy to use your Excel 365, could something much simpler like this be any use?

Tofik.xlsm
	A	B	C	D	E	F	G	H	I
1	Welder ID 1	Welder ID 2	Tet Report No_	Status			Welder ID	Test Reports No_	Status
2	PW-001	PW-009	TKS-KOO-0005	ACC			PW-001	TKS-KOO-0005, TKS-KOO-0017, TKS-KOO-0018, TKS-KOO-0019	ACC
3	PW-009	PW-034	TKS-KOO-0006	ACC			PW-005	TKS-KOO-0007, TKS-KOO-0017, TKS-KOO-0019	ACC
4	PW-005	PW-007	TKS-KOO-0007	ACC			PW-007	TKS-KOO-0007, TKS-KOO-0008, TKS-KOO-0016, TKS-KOO-0020, TKS-KOO-0022, TKS-KOO-0023	ACC
5	PW-007	PW-007	TKS-KOO-0008	ACC			PW-009	TKS-KOO-0005, TKS-KOO-0006, TKS-KOO-0018, TKS-KOO-0021	ACC
6	PW-030	PW-029	TKS-KOO-0009	ACC			PW-013	TKS-KOO-0011, TKS-KOO-0013, TKS-KOO-0016	REJ, ACC
7	PW-034	PW-034	TKS-KOO-0010	REJ			PW-026	TKS-KOO-0012, TKS-KOO-0023, TKS-KOO-0024	ACC, n/a
8	PW-013	PW-035	TKS-KOO-0011	REJ			PW-029	TKS-KOO-0009, TKS-KOO-0025	ACC, n/a
9	PW-026	PW-026	TKS-KOO-0012	ACC			PW-030	TKS-KOO-0009, TKS-KOO-0020	ACC
10	PW-035	PW-013	TKS-KOO-0013	ACC			PW-034	TKS-KOO-0006, TKS-KOO-0010, TKS-KOO-0021, TKS-KOO-0022	ACC, REJ
11	PW-035	n/a	TKS-KOO-0014	n/a			PW-035	TKS-KOO-0011, TKS-KOO-0013, TKS-KOO-0014	REJ, ACC, n/a
12	PW-036	n/a	TKS-KOO-0015	n/a			PW-036	TKS-KOO-0015	n/a
13	PW-013	PW-007	TKS-KOO-0016	ACC
14	PW-005	PW-001	TKS-KOO-0017	ACC
15	PW-001	PW-009	TKS-KOO-0018	ACC
16	PW-001	PW-005	TKS-KOO-0019	ACC
17	PW-030	PW-007	TKS-KOO-0020	ACC
18	PW-009	PW-034	TKS-KOO-0021	ACC
19	PW-034	PW-007	TKS-KOO-0022	ACC
20	PW-007	PW-026	TKS-KOO-0023	ACC
21	PW-026	n/a	TKS-KOO-0024	n/a
22	PW-029	n/a	TKS-KOO-0025	n/a
Sheet1

Cell Formulas
Range	Formula
G2:G12	G2	=SORT(UNIQUE(A2:A22))
H2:H12	H2	=TEXTJOIN(", ",1,UNIQUE(FILTER(C$2:C$22,(A$2:A$22=G2)+(B$2:B$22=G2),"")))
I2:I12	I2	=TEXTJOIN(", ",1,UNIQUE(FILTER(D$2:D$22,(A$2:A$22=G2)+(B$2:B$22=G2),"")))
Dynamic array formulas.

 

[Tofik]

View attachment 31251

View attachment 31252

This is because of International Setting between , comma and ; semicolon

Control Panel >> Clock and Region >> Region >>> Additional Settings

try this

Hi, I changed List Separator settings to , but it still gives me an error and wants ;