Smart Table

T

Tofik

Board Regular
Joined
Feb 4, 2021
Messages
114
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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?
 
Upvote 0
denzo36 said:
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?
Click to expand...
yes
I want to
 
Upvote 0
denzo36 said:
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?
Click to expand...
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.
 
Upvote 0
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:
Upvote 0
Solution
Dossfm0q said:
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.
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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: 18
Last edited:
Upvote 0
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);"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Merge multiple tables into one
Replies
3
Views
917
FotS
F
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
335
JMWh1t3
J
C
  • Question Question
VBA to copy update data one on sheet and paste into data table on another sheet
Replies
1
Views
3K
sijpie
sijpie
C
  • Question Question
Mutiple criteria filtering to separate 2NF table (nested FILTER over FILTER formula?)
Replies
1
Views
504
Fluff
Fluff
L
  • Question Question
VLOOKUP in pivot table calculated field
Replies
2
Views
3K
lironprofit
L

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
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