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

 

[denzo36]

Bit hard to understand your request - for PW-001 in your result table, should that not have 4 test report numbers? i.e. TKS-KOO-0005, TKS-KOO-0017, TKS-KOO-0018 and TKS-KOO-0019?

 

[Tofik]

Bit hard to understand your request - for PW-001 in your result table, should that not have 4 test report numbers? i.e. TKS-KOO-0005, TKS-KOO-0017, TKS-KOO-0018 and TKS-KOO-0019?

yes
I want to

 

[Tofik]

Bit hard to understand your request - for PW-001 in your result table, should that not have 4 test report numbers? i.e. TKS-KOO-0005, TKS-KOO-0017, TKS-KOO-0018 and TKS-KOO-0019?

I want to automate this table. the problem is that before starting work if a large volume of work is working simultaneously 2 welders and therefore I have Welder 1 and Welder 2.
I have a table with 2 numbers of welders and they can constantly change places in the list, there may also be duplicates (about welders) but with different report numbers. That's why VLOOKUP is useless.

My goal is to create a table that will contain all welders from 1 to 20, for example, and which will show how many unique reports each welder has.

 

[Tofik]

?

 

[Dossfm0q]

What Abut?
may I have Missing spelling.
Note: Ctrl + Shift +Enter For column "H" and "I"

Smart Table.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			n/a	TKS-KOO-0014,TKS-KOO-0015,TKS-KOO-0024,TKS-KOO-0025	n/a,n/a,n/a,n/a
3	PW-009	PW-034	TKS-KOO-0006	ACC			PW-001	TKS-KOO-0005,TKS-KOO-0017,TKS-KOO-0018,TKS-KOO-0019	ACC,ACC,ACC,ACC
4	PW-005	PW-007	TKS-KOO-0007	ACC			PW-005	TKS-KOO-0007,TKS-KOO-0017,TKS-KOO-0019	ACC,ACC,ACC
5	PW-007	PW-007	TKS-KOO-0008	ACC			PW-007	TKS-KOO-0007,TKS-KOO-0008,TKS-KOO-0008,TKS-KOO-0016,TKS-KOO-0020,TKS-KOO-0022,TKS-KOO-0023	ACC,ACC,ACC,ACC,ACC,ACC,ACC
6	PW-030	PW-029	TKS-KOO-0009	ACC			PW-009	TKS-KOO-0005,TKS-KOO-0006,TKS-KOO-0018,TKS-KOO-0021	ACC,ACC,ACC,ACC
7	PW-034	PW-034	TKS-KOO-0010	REJ			PW-013	TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0016	REJ,ACC,ACC
8	PW-013	PW-035	TKS-KOO-0011	REJ			PW-026	TKS-KOO-0012,TKS-KOO-0012,TKS-KOO-0023,TKS-KOO-0024	ACC,ACC,ACC,n/a
9	PW-026	PW-026	TKS-KOO-0012	ACC			PW-029	TKS-KOO-0009,TKS-KOO-0025	ACC,n/a
10	PW-035	PW-013	TKS-KOO-0013	ACC			PW-030	TKS-KOO-0009,TKS-KOO-0020	ACC,ACC
11	PW-035	n/a	TKS-KOO-0014	n/a			PW-034	TKS-KOO-0006,TKS-KOO-0010,TKS-KOO-0010,TKS-KOO-0021,TKS-KOO-0022	ACC,REJ,REJ,ACC,ACC
12	PW-036	n/a	TKS-KOO-0015	n/a			PW-035	TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0014	REJ,ACC,n/a
13	PW-013	PW-007	TKS-KOO-0016	ACC			PW-036	TKS-KOO-0015	n/a
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
Sheet3

Cell Formulas
Range	Formula
G2:G22	G2	=IFERROR(INDEX($A$2:$B$22,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$22)-COLUMN($A$2))+((ROW($A$2:$B$22)-ROW($A$2))*COLUMNS($A$2:$B$22)+1))/((COUNTIF($A$2:$B$22,"<"&$A$2:$B$22)+($A$2:$B$22<>""))+(($A$2:$B$22<>"")*--(ISNUMBER($A$2:$B$22)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$22)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$22,G$1:G1))=1),1),COLUMNS($A$2:$B$22))/COLUMNS($A$2:$B$22),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$22)-COLUMN($A$2))+((ROW($A$2:$B$22)-ROW($A$2))*COLUMNS($A$2:$B$22)+1))/((COUNTIF($A$2:$B$22,"<"&$A$2:$B$22)+($A$2:$B$22<>""))+(($A$2:$B$22<>"")*--(ISNUMBER($A$2:$B$22)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$22)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$22,G$1:G1))=1),1)-1,COLUMNS($A$2:$B$22))+1),"")
H2:I22	H2	=TEXTJOIN(",",1,IF($G2=$A$2:$B$22,C$2:C$22,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tofik]

What Abut?
may I have Missing spelling.
Note: Ctrl + Shift +Enter For column "H" and "I"

Smart Table.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			n/a	TKS-KOO-0014,TKS-KOO-0015,TKS-KOO-0024,TKS-KOO-0025	n/a,n/a,n/a,n/a
3	PW-009	PW-034	TKS-KOO-0006	ACC			PW-001	TKS-KOO-0005,TKS-KOO-0017,TKS-KOO-0018,TKS-KOO-0019	ACC,ACC,ACC,ACC
4	PW-005	PW-007	TKS-KOO-0007	ACC			PW-005	TKS-KOO-0007,TKS-KOO-0017,TKS-KOO-0019	ACC,ACC,ACC
5	PW-007	PW-007	TKS-KOO-0008	ACC			PW-007	TKS-KOO-0007,TKS-KOO-0008,TKS-KOO-0008,TKS-KOO-0016,TKS-KOO-0020,TKS-KOO-0022,TKS-KOO-0023	ACC,ACC,ACC,ACC,ACC,ACC,ACC
6	PW-030	PW-029	TKS-KOO-0009	ACC			PW-009	TKS-KOO-0005,TKS-KOO-0006,TKS-KOO-0018,TKS-KOO-0021	ACC,ACC,ACC,ACC
7	PW-034	PW-034	TKS-KOO-0010	REJ			PW-013	TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0016	REJ,ACC,ACC
8	PW-013	PW-035	TKS-KOO-0011	REJ			PW-026	TKS-KOO-0012,TKS-KOO-0012,TKS-KOO-0023,TKS-KOO-0024	ACC,ACC,ACC,n/a
9	PW-026	PW-026	TKS-KOO-0012	ACC			PW-029	TKS-KOO-0009,TKS-KOO-0025	ACC,n/a
10	PW-035	PW-013	TKS-KOO-0013	ACC			PW-030	TKS-KOO-0009,TKS-KOO-0020	ACC,ACC
11	PW-035	n/a	TKS-KOO-0014	n/a			PW-034	TKS-KOO-0006,TKS-KOO-0010,TKS-KOO-0010,TKS-KOO-0021,TKS-KOO-0022	ACC,REJ,REJ,ACC,ACC
12	PW-036	n/a	TKS-KOO-0015	n/a			PW-035	TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0014	REJ,ACC,n/a
13	PW-013	PW-007	TKS-KOO-0016	ACC			PW-036	TKS-KOO-0015	n/a
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
Sheet3

Cell Formulas
Range	Formula
G2:G22	G2	=IFERROR(INDEX($A$2:$B$22,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$22)-COLUMN($A$2))+((ROW($A$2:$B$22)-ROW($A$2))*COLUMNS($A$2:$B$22)+1))/((COUNTIF($A$2:$B$22,"<"&$A$2:$B$22)+($A$2:$B$22<>""))+(($A$2:$B$22<>"")*--(ISNUMBER($A$2:$B$22)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$22)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$22,G$1:G1))=1),1),COLUMNS($A$2:$B$22))/COLUMNS($A$2:$B$22),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$22)-COLUMN($A$2))+((ROW($A$2:$B$22)-ROW($A$2))*COLUMNS($A$2:$B$22)+1))/((COUNTIF($A$2:$B$22,"<"&$A$2:$B$22)+($A$2:$B$22<>""))+(($A$2:$B$22<>"")*--(ISNUMBER($A$2:$B$22)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$22)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$22,G$1:G1))=1),1)-1,COLUMNS($A$2:$B$22))+1),"")
H2:I22	H2	=TEXTJOIN(",",1,IF($G2=$A$2:$B$22,C$2:C$22,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Bro this formula gave me a mistake or I doing something wrong?
Also thank you for this formula, for me it's unreal.

 

[lrobbo314]

Could use Power Query.

TCOs working ELC Zones 2021 JJ.xlsx
	A	B	C	D	E	F	G	H
1	Welder ID 1	Welder ID 2	Tet Report No_	Status		Welder ID	Test Report Number	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, ACC, ACC, ACC
3	PW-009	PW-034	TKS-KOO-0006	ACC		PW-009	TKS-KOO-0005, TKS-KOO-0006, TKS-KOO-0018, TKS-KOO-0021	ACC, ACC, ACC, ACC
4	PW-005	PW-007	TKS-KOO-0007	ACC		PW-034	TKS-KOO-0006, TKS-KOO-0010, TKS-KOO-0010, TKS-KOO-0021, TKS-KOO-0022	ACC, REJ, REJ, ACC, ACC
5	PW-007	PW-007	TKS-KOO-0008	ACC		PW-005	TKS-KOO-0007, TKS-KOO-0017, TKS-KOO-0019	ACC, ACC, ACC
6	PW-030	PW-029	TKS-KOO-0009	ACC		PW-007	TKS-KOO-0007, TKS-KOO-0008, TKS-KOO-0008, TKS-KOO-0016, TKS-KOO-0020, TKS-KOO-0022, TKS-KOO-0023	ACC, ACC, ACC, ACC, ACC, ACC, ACC
7	PW-034	PW-034	TKS-KOO-0010	REJ		PW-030	TKS-KOO-0009, TKS-KOO-0020	ACC, ACC
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-013	TKS-KOO-0011, TKS-KOO-0013, TKS-KOO-0016	REJ, ACC, ACC
10	PW-035	PW-013	TKS-KOO-0013	ACC		PW-035	TKS-KOO-0011, TKS-KOO-0013, TKS-KOO-0014	REJ, ACC, n/a
11	PW-035	n/a	TKS-KOO-0014	n/a		PW-026	TKS-KOO-0012, TKS-KOO-0012, TKS-KOO-0023, TKS-KOO-0024	ACC, ACC, 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
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Tet Report No_", "Status"}, "Attribute", "Welder ID"),
    NA = Table.SelectRows(Unpivot, each ([Welder ID] <> "n/a")),
    Group = Table.Group(NA, {"Welder ID"}, {{"Count", each _, type table [Tet Report No_=nullable text, Status=nullable text, Attribute=text, Value=text]}}),
    Report = Table.AddColumn(Group, "Test Report Number", each Text.Combine([Count][Tet Report No_],", ")),
    Status = Table.AddColumn(Report, "Status", each Text.Combine([Count][Status],", "))
in
    Status

 

[Dossfm0q]

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

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

try this

 

[Dossfm0q]

=IFERROR(INDEX($A$2:$B$22;CEILING(AGGREGATE(15;6;((COLUMN($A$2:$B$22)-COLUMN($A$2))+((ROW($A$2:$B$22)-ROW($A$2))*COLUMNS($A$2:$B$22)+1))/((COUNTIF($A$2:$B$22;"<"&$A$2:$B$22)+($A$2:$B$22<>""))+(($A$2:$B$22<>"")*--(ISNUMBER($A$2:$B$22)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$22)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$22;G$1:G1))=1);1);COLUMNS($A$2:$B$22))/COLUMNS($A$2:$B$22);MOD(AGGREGATE(15;6;((COLUMN($A$2:$B$22)-COLUMN($A$2))+((ROW($A$2:$B$22)-ROW($A$2))*COLUMNS($A$2:$B$22)+1))/((COUNTIF($A$2:$B$22;"<"&$A$2:$B$22)+($A$2:$B$22<>""))+(($A$2:$B$22<>"")*--(ISNUMBER($A$2:$B$22)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$22)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$22;G$1:G1))=1);1)-1;COLUMNS($A$2:$B$22))+1);"")