Smart Table

Tofik

Board Regular
Joined
Feb 4, 2021
Messages
92
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHI
1Welder ID 1Welder ID 2Tet Report No_StatusWelder IDTest Reports No_Status
2PW-001PW-009TKS-KOO-0005ACCPW-001TKS-KOO-0005, TKS-KOO-0017ACC, ACC
3PW-009PW-034TKS-KOO-0006ACCPW-002
4PW-005PW-007TKS-KOO-0007ACCPW-003
5PW-007PW-007TKS-KOO-0008ACCPW-004
6PW-030PW-029TKS-KOO-0009ACCPW-005
7PW-034PW-034TKS-KOO-0010REJPW-006
8PW-013PW-035TKS-KOO-0011REJPW-007
9PW-026PW-026TKS-KOO-0012ACCPW-008
10PW-035PW-013TKS-KOO-0013ACCPW-009
11PW-035n/aTKS-KOO-0014n/aPW-010
12PW-036n/aTKS-KOO-0015n/aPW-011
13PW-013PW-007TKS-KOO-0016ACCPW-012
14PW-005PW-001TKS-KOO-0017ACCPW-013
15PW-001PW-009TKS-KOO-0018ACCPW-014
16PW-001PW-005TKS-KOO-0019ACCPW-015
17PW-030PW-007TKS-KOO-0020ACCPW-016
18PW-009PW-034TKS-KOO-0021ACCPW-017
19PW-034PW-007TKS-KOO-0022ACCPW-018
20PW-007PW-026TKS-KOO-0023ACCPW-019
21PW-026n/aTKS-KOO-0024n/aPW-020
22PW-029n/aTKS-KOO-0025n/aPW-021
23
24
25
Sheet1


jd3tqudrjff61.png
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
152
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2021
Messages
92
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2021
Messages
92
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2021
Messages
92
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

🙏
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
What Abut?
may I have Missing spelling.
Note: Ctrl + Shift +Enter For column "H" and "I"


Smart Table.xlsx
ABCDEFGHI
1Welder ID 1Welder ID 2Tet Report No_StatusWelder IDTest Reports No_Status
2PW-001PW-009TKS-KOO-0005ACCn/aTKS-KOO-0014,TKS-KOO-0015,TKS-KOO-0024,TKS-KOO-0025n/a,n/a,n/a,n/a
3PW-009PW-034TKS-KOO-0006ACCPW-001TKS-KOO-0005,TKS-KOO-0017,TKS-KOO-0018,TKS-KOO-0019ACC,ACC,ACC,ACC
4PW-005PW-007TKS-KOO-0007ACCPW-005TKS-KOO-0007,TKS-KOO-0017,TKS-KOO-0019ACC,ACC,ACC
5PW-007PW-007TKS-KOO-0008ACCPW-007TKS-KOO-0007,TKS-KOO-0008,TKS-KOO-0008,TKS-KOO-0016,TKS-KOO-0020,TKS-KOO-0022,TKS-KOO-0023ACC,ACC,ACC,ACC,ACC,ACC,ACC
6PW-030PW-029TKS-KOO-0009ACCPW-009TKS-KOO-0005,TKS-KOO-0006,TKS-KOO-0018,TKS-KOO-0021ACC,ACC,ACC,ACC
7PW-034PW-034TKS-KOO-0010REJPW-013TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0016REJ,ACC,ACC
8PW-013PW-035TKS-KOO-0011REJPW-026TKS-KOO-0012,TKS-KOO-0012,TKS-KOO-0023,TKS-KOO-0024ACC,ACC,ACC,n/a
9PW-026PW-026TKS-KOO-0012ACCPW-029TKS-KOO-0009,TKS-KOO-0025ACC,n/a
10PW-035PW-013TKS-KOO-0013ACCPW-030TKS-KOO-0009,TKS-KOO-0020ACC,ACC
11PW-035n/aTKS-KOO-0014n/aPW-034TKS-KOO-0006,TKS-KOO-0010,TKS-KOO-0010,TKS-KOO-0021,TKS-KOO-0022ACC,REJ,REJ,ACC,ACC
12PW-036n/aTKS-KOO-0015n/aPW-035TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0014REJ,ACC,n/a
13PW-013PW-007TKS-KOO-0016ACCPW-036TKS-KOO-0015n/a
14PW-005PW-001TKS-KOO-0017ACC   
15PW-001PW-009TKS-KOO-0018ACC   
16PW-001PW-005TKS-KOO-0019ACC   
17PW-030PW-007TKS-KOO-0020ACC   
18PW-009PW-034TKS-KOO-0021ACC   
19PW-034PW-007TKS-KOO-0022ACC   
20PW-007PW-026TKS-KOO-0023ACC   
21PW-026n/aTKS-KOO-0024n/a   
22PW-029n/aTKS-KOO-0025n/a   
Sheet3
Cell Formulas
RangeFormula
G2:G22G2=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:I22H2=TEXTJOIN(",",1,IF($G2=$A$2:$B$22,C$2:C$22,""))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Solution

Tofik

Board Regular
Joined
Feb 4, 2021
Messages
92
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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


Smart Table.xlsx
ABCDEFGHI
1Welder ID 1Welder ID 2Tet Report No_StatusWelder IDTest Reports No_Status
2PW-001PW-009TKS-KOO-0005ACCn/aTKS-KOO-0014,TKS-KOO-0015,TKS-KOO-0024,TKS-KOO-0025n/a,n/a,n/a,n/a
3PW-009PW-034TKS-KOO-0006ACCPW-001TKS-KOO-0005,TKS-KOO-0017,TKS-KOO-0018,TKS-KOO-0019ACC,ACC,ACC,ACC
4PW-005PW-007TKS-KOO-0007ACCPW-005TKS-KOO-0007,TKS-KOO-0017,TKS-KOO-0019ACC,ACC,ACC
5PW-007PW-007TKS-KOO-0008ACCPW-007TKS-KOO-0007,TKS-KOO-0008,TKS-KOO-0008,TKS-KOO-0016,TKS-KOO-0020,TKS-KOO-0022,TKS-KOO-0023ACC,ACC,ACC,ACC,ACC,ACC,ACC
6PW-030PW-029TKS-KOO-0009ACCPW-009TKS-KOO-0005,TKS-KOO-0006,TKS-KOO-0018,TKS-KOO-0021ACC,ACC,ACC,ACC
7PW-034PW-034TKS-KOO-0010REJPW-013TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0016REJ,ACC,ACC
8PW-013PW-035TKS-KOO-0011REJPW-026TKS-KOO-0012,TKS-KOO-0012,TKS-KOO-0023,TKS-KOO-0024ACC,ACC,ACC,n/a
9PW-026PW-026TKS-KOO-0012ACCPW-029TKS-KOO-0009,TKS-KOO-0025ACC,n/a
10PW-035PW-013TKS-KOO-0013ACCPW-030TKS-KOO-0009,TKS-KOO-0020ACC,ACC
11PW-035n/aTKS-KOO-0014n/aPW-034TKS-KOO-0006,TKS-KOO-0010,TKS-KOO-0010,TKS-KOO-0021,TKS-KOO-0022ACC,REJ,REJ,ACC,ACC
12PW-036n/aTKS-KOO-0015n/aPW-035TKS-KOO-0011,TKS-KOO-0013,TKS-KOO-0014REJ,ACC,n/a
13PW-013PW-007TKS-KOO-0016ACCPW-036TKS-KOO-0015n/a
14PW-005PW-001TKS-KOO-0017ACC   
15PW-001PW-009TKS-KOO-0018ACC   
16PW-001PW-005TKS-KOO-0019ACC   
17PW-030PW-007TKS-KOO-0020ACC   
18PW-009PW-034TKS-KOO-0021ACC   
19PW-034PW-007TKS-KOO-0022ACC   
20PW-007PW-026TKS-KOO-0023ACC   
21PW-026n/aTKS-KOO-0024n/a   
22PW-029n/aTKS-KOO-0025n/a   
Sheet3
Cell Formulas
RangeFormula
G2:G22G2=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:I22H2=TEXTJOIN(",",1,IF($G2=$A$2:$B$22,C$2:C$22,""))
Press CTRL+SHIFT+ENTER to enter array formulas.
1612441945457.png

1612441976767.png


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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,252
Office Version
  1. 365
Platform
  1. Windows
Could use Power Query.

TCOs working ELC Zones 2021 JJ.xlsx
ABCDEFGH
1Welder ID 1Welder ID 2Tet Report No_StatusWelder IDTest Report NumberStatus
2PW-001PW-009TKS-KOO-0005ACCPW-001TKS-KOO-0005, TKS-KOO-0017, TKS-KOO-0018, TKS-KOO-0019ACC, ACC, ACC, ACC
3PW-009PW-034TKS-KOO-0006ACCPW-009TKS-KOO-0005, TKS-KOO-0006, TKS-KOO-0018, TKS-KOO-0021ACC, ACC, ACC, ACC
4PW-005PW-007TKS-KOO-0007ACCPW-034TKS-KOO-0006, TKS-KOO-0010, TKS-KOO-0010, TKS-KOO-0021, TKS-KOO-0022ACC, REJ, REJ, ACC, ACC
5PW-007PW-007TKS-KOO-0008ACCPW-005TKS-KOO-0007, TKS-KOO-0017, TKS-KOO-0019ACC, ACC, ACC
6PW-030PW-029TKS-KOO-0009ACCPW-007TKS-KOO-0007, TKS-KOO-0008, TKS-KOO-0008, TKS-KOO-0016, TKS-KOO-0020, TKS-KOO-0022, TKS-KOO-0023ACC, ACC, ACC, ACC, ACC, ACC, ACC
7PW-034PW-034TKS-KOO-0010REJPW-030TKS-KOO-0009, TKS-KOO-0020ACC, ACC
8PW-013PW-035TKS-KOO-0011REJPW-029TKS-KOO-0009, TKS-KOO-0025ACC, n/a
9PW-026PW-026TKS-KOO-0012ACCPW-013TKS-KOO-0011, TKS-KOO-0013, TKS-KOO-0016REJ, ACC, ACC
10PW-035PW-013TKS-KOO-0013ACCPW-035TKS-KOO-0011, TKS-KOO-0013, TKS-KOO-0014REJ, ACC, n/a
11PW-035n/aTKS-KOO-0014n/aPW-026TKS-KOO-0012, TKS-KOO-0012, TKS-KOO-0023, TKS-KOO-0024ACC, ACC, ACC, n/a
12PW-036n/aTKS-KOO-0015n/aPW-036TKS-KOO-0015n/a
13PW-013PW-007TKS-KOO-0016ACC
14PW-005PW-001TKS-KOO-0017ACC
15PW-001PW-009TKS-KOO-0018ACC
16PW-001PW-005TKS-KOO-0019ACC
17PW-030PW-007TKS-KOO-0020ACC
18PW-009PW-034TKS-KOO-0021ACC
19PW-034PW-007TKS-KOO-0022ACC
20PW-007PW-026TKS-KOO-0023ACC
21PW-026n/aTKS-KOO-0024n/a
22PW-029n/aTKS-KOO-0025n/a
Sheet3


Power Query:
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

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Picture1.png


Picture2.png


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

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

try this
 

Attachments

  • Picture1.png
    Picture1.png
    3.1 KB · Views: 1
Last edited:

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Excel Formula:
=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);"")
 

Forum statistics

Threads
1,141,485
Messages
5,706,654
Members
421,459
Latest member
Taamrak

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