Brain Error ( not for everyone )

Tofik

Board Regular
Joined
Feb 4, 2021
Messages
114
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
DRAWINGISOSPOOLINCWeld №Welder 1Welder 2RT DateRT ReportRT ResultRej LengthImperfection TypeRT Rej LengthRej WelderRejection rate NEW TABLE ( WHICH I IMAGINE AND SHOULD WORK AUTOMATIC ) I JUST DESCRIBE SOME PRINCIPLES HOW IT SHOULD LOOK LIKE Total for a week periodTotal in jeneralold manual counted rejection rate
HS523217-01-PIP-ISO-10207-001210-LLC-0110207-AA23-HCSP-0210WS02PW-009N/AN/AN/AN/AN/AN/A0example:Report date or weekly cyclereport numberwelder 1welder 2rt resultRepaired JointsTARGETWEEKLYAveragejanuary%FullRT PerformedRepaired JointsRepair %
HS523217-01-PIP-ISO-10207-001210-LLC-0110207-AA23-HCSP-0410WS06PW-009N/AN/AN/AN/AN/AN/A0for week 1 in january23.01.2021QQS-SDF-0021WS03PW-001accthis inormation came from 3d party5%in %Averagefebruary%TargetweekAverage
HS523217-01-PIP-ISO-10207-001810-LLC-0110207-AA23-HCSP-0310WS04PW-036N/AN/AN/AN/AN/AN/A024.01.2021QQS-SDF-0010WS07PW-036rejand I write this number manuallyAveragemarch%######005%#DIV/0!#DIV/0!
HS523217-01-PIP-ISO-10207-001810-LLC-0110207-AA23-HCSP-0210WS02PW-036N/AN/AN/AN/AN/AN/A025.01.2021etc….etc….etc….etc….Averageapril%######215%50,00%50,00%
HS523217-01-PIP-ISO-20003-000512-P-0120003-BA51-ESP-0212WS03PW-035N/A23.01.2021QQS-SDF-0001ACC100X4500026.01.2021etc….etc….etc….etc….formula RT total / Repaired Joints * 100%Averagemay%######3905%0,00%2,44%
HS523217-01-PIP-ISO-20003-000912-P-0120003-BA51-ESP-0112WS02PW-005N/A22.01.2021QQS-SDF-0002ACC100X4500027.01.2021etc….etc….etc….etc….Averagejune%######4335%6,98%4,76%
HS523217-01-PIP-ISO-60105-002810-N2-0160105-AA23-NISP-0210WS03PW-034N/AN/AN/AN/AN/AN/A027.01.2021etc….etc….etc….etc….Averagejuly%######99125%12,12%8,74%
HS523217-01-PIP-ISO-60105-003110-N2-0160105-AA23-NISP-0310WS06PW-001N/AN/AN/AN/AN/AN/A028.01.2021etc….etc….etc….etc….Averageaugust%######7445%5,41%7,78%
HS523217-01-PIP-ISO-60105-003010-N2-0160105-AA23-NISP-0510WS09PW-013N/AN/AN/AN/AN/AN/A029.01.2021etc….etc….etc….etc….Averageseptember%######15345%2,61%5,85%
HS523217-01-PIP-ISO-60105-002410-N2-0160105-AA23-NISP-0510WS09PW-030N/AN/AN/AN/AN/AN/A0Total reports number :8Averageoctober%######11265%5,36%5,75%
HS523217-01-PIP-ISO-20003-000512-P-0120003-BA51-ESP-0112WS01PW-035N/AN/AN/AN/AN/AN/A0Averagenovember%######4025%5,00%5,69%
HS523217-01-PIP-ISO-00211-002212-P-0100211-BA51-NISP-0212WS05PW-031N/AN/AN/AN/AN/AN/A0Averagedecember%######1205%0,00%5,57%
HS523217-01-PIP-ISO-10819-001516-UW-0110819-AA52-NISP-0216WS03PW-003N/AN/AN/AN/AN/AN/A0######4435%6,82%5,66%
HS523217-01-PIP-ISO-60105-003110-N2-0160105-AA23-NISP-03 3/4WS04PW-001N/AN/AN/AN/AN/AN/A01)Note: when we are counting the Rejection rate we are start count from Friday till next Friday.######3915%2,56%5,48%
HS523217-01-PIP-ISO-20003-000512-P-0120003-BA51-ESP-0312WS05PW-007N/A24.01.2021QQS-SDF-0009ACC100X45000######2115%4,76%5,46%
HS523217-01-PIP-ISO-00211-002212-P-0100211-BA51-NISP-0312WS07PW-036N/A24.01.2021QQS-SDF-0010REJ100X45000######3935%7,69%5,58%
HS523217-01-PIP-ISO-10819-001516-UW-0110819-AA52-NISP-0116WS01PW-030N/A26.01.2021QQS-SDF-0012ACC100X55000etc…. For next weeks and months######5015%2,00%5,35%
HS523217-01-PIP-ISO-10405-001016-FG-0110405-AA51-NISP-0216WS03PW-035PW-036N/AN/AN/AN/AN/A0######2305%0,00%5,19%
HS523217-01-PIP-ISO-00220-001610-P-0100220-BA21-NISP-0210WS03PW-049N/A25.01.2021QQS-SDF-0018ACC100X40000######4835%6,25%5,25%
HS523217-01-PIP-ISO-10405-001016-FG-0110405-AA51-NISP-0116WS01PW-001N/A03.02.2021QQS-SDF-0013ACC100X550002)Report Number######4335%6,98%5,33%
HS523217-01-PIP-ISO-10819-002116-UW-0110819-AA52-NISP-0116WS01PW-001N/A25.01.2021QQS-SDF-0014REJ100X55015-200######5%#DIV/0!5,33%
HS523217-01-PIP-ISO-00211-002412-P-0100211-BA51-NISP-0212WS06PW-007N/A25.01.2021QQS-SDF-0016ACC100X45000######5%#DIV/0!5,33%
HS523217-01-PIP-ISO-00211-002212-P-0100211-BA51-NISP-0412WS09PW-005N/A25.01.2021QQS-SDF-0015ACC100X45000######5%#DIV/0!5,33%
HS523217-01-PIP-ISO-20003-000312-P-0120003-BA51-ESP-0312WS05PW-013N/A24.01.2021QQS-SDF-0017REJ100X450PW-0135-1005%#DIV/0!5,33%
HS523217-01-PIP-ISO-20003-000312-P-0120003-BA51-ESP-0312WS03PW-013N/AN/AN/AN/AN/AN/A05%#DIV/0!5,33%
HS523217-01-PIP-ISO-20003-000312-P-0120003-BA51-ESP-0312WS04PW-013N/AN/AN/AN/AN/AN/A03)Welder info5%#DIV/0!5,33%
HS523217-01-PIP-ISO-00211-002412-P-0100211-BA51-NISP-0212WS07PW-007N/AN/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-00211-002212-P-0100211-BA51-NISP-0112WS03PW-005N/AN/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-00220-001610-P-0100220-BA21-NISP-0110WS01PW-050N/AN/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-01006-002210-H2-0101006-BA21H-NISP-0210WS03PW-050N/A24.01.2021QQS-SDF-0040ACC100X40000
HS523217-01-PIP-ISO-10405-001216-FG-0110405-AA51-NISP-0516WS09PW-030PW-034N/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-10819-001316-UW-0110819-AA52-NISP-0416WS06PW-026PW-003N/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-10819-001316-UW-0110819-AA52-NISP-0416WS07PW-026PW-003N/AN/AN/AN/AN/A0Rejection rate (more productivity version) planning creating something like this
HS523217-01-PIP-ISO-10819-001516-UW-0110819-AA52-NISP-0316WS05PW-035PW-036N/AN/AN/AN/AN/A04)Rt esultold rejection rate
HS523217-01-PIP-ISO-00211-001512-P-0100211-BA51-NISP-0212WS04PW-029N/AN/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-00220-001810-P-0100220-BA21-NISP-0210WS03PW-042N/A23.01.2021QQS-SDF-0022ACC100X40000
HS523217-01-PIP-ISO-01006-002410-H2-0101006-BA21H-NISP-0210WS04PW-049N/A24.01.2021QQS-SDF-0050/0042ACC100X40000
HS523217-01-PIP-ISO-00220-001810-P-0100220-BA21-NISP-0310WS05PW-067N/AN/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-10405-001816-FG-0110405-AA51-NISP-0216WS03PW-001PW-009N/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-10405-001816-FG-0110405-AA51-NISP-0216WS04PW-001PW-009N/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-00501-00176-P-0100501-AA21-ESP-016WS02PW-048N/A24.01.2021QQS-SDF-0023ACC100X30000
HS523217-01-PIP-ISO-10819-002116-UW-0110819-AA52-NISP-0416WS07PW-035PW-036N/AN/AN/AN/AN/A05)Repaired Joints this inormation came from 3d party and I write this number manually
HS523217-01-PIP-ISO-10405-001616-FG-0110405-AA51-NISP-0516WS09PW-030PW-034N/AN/AN/AN/AN/A0
HS523217-01-PIP-ISO-10819-001916-UW-0110819-AA52-NISP-0316WS08PW-050PW-04424.01.2021QQS-SDF-0019ACC100X55000
sometimes Welders ID (Welder 1 + Welder 2 ) can be separated by slash example: QQS-SDF-0050/0042 it can be because a very big pipe and 2 welders work at the same time. I think it is our weak point in the database and I also want to separate them into a different row"
Main scope of information Note: my table consist from nearly 10,000 lines Just a small part for example


Hello. I am very surprised by the help on this forum, in comparison with Russian forums there is a lot of help here and much more. I am blogged for helping in the previous post to everyone who replied and wasted their time.
Moving on to the next step, I would like to automate the spreadsheet I work with, and this work is increasingly becoming a routine.
My request, if possible, determine the principles of how to create what I need or direct me in the direction where I can ask a request in Google search engine correctly.

Objective:
I have a very large data base of welders which consists of more than 10,000 lines. I receive reports that I drive in and process into my log. also, during each week, I have to give a detailed report that
1) how many rejection rate we have in persent %
2) which welder has rejection rate
4) Average value for the entire duration of the project. %

I tried to highlight with color what is related to each other.
And the biggest problem for me is when I have duplicate values in a column that need to be shown as unique in a separate example below,
please use as simple formulas as possible so that I can understand how to do it myself

Best regards,
Tofik





















MREXCEL EXAMPLE.xlsb
ABCDEFGHIJKLMNOP
1HS523217-01-PIP-ISO-20003-000512-P-0120003-BA51-ESP-0312WS05PW-007N/A24.01.2021QQS-SDF-0009ACC100X4500024.01.2021QQS-SDF-0009
2HS523217-01-PIP-ISO-00211-002212-P-0100211-BA51-NISP-0312WS07PW-036N/A24.01.2021QQS-SDF-0010REJ100X45000QQS-SDF-0010
3HS523217-01-PIP-ISO-10819-001516-UW-0110819-AA52-NISP-0116WS01PW-030N/A26.01.2021QQS-SDF-0012ACC100X55000QQS-SDF-0017
4HS523217-01-PIP-ISO-10405-001016-FG-0110405-AA51-NISP-0216WS03PW-035PW-036N/AN/AN/AN/AN/A0
5HS523217-01-PIP-ISO-00220-001610-P-0100220-BA21-NISP-0210WS03PW-049N/A25.01.2021QQS-SDF-0018ACC100X40000
6HS523217-01-PIP-ISO-10405-001016-FG-0110405-AA51-NISP-0116WS01PW-001N/A03.02.2021QQS-SDF-0013ACC100X55000
7HS523217-01-PIP-ISO-10819-002116-UW-0110819-AA52-NISP-0116WS01PW-001N/A25.01.2021QQS-SDF-0014REJ100X55015-20025.01.2021?????
8HS523217-01-PIP-ISO-00211-002412-P-0100211-BA51-NISP-0212WS06PW-007N/A25.01.2021QQS-SDF-0016ACC100X45000????
9HS523217-01-PIP-ISO-00211-002212-P-0100211-BA51-NISP-0412WS09PW-005N/A25.01.2021QQS-SDF-0015ACC100X45000How I can separet ?????
10HS523217-01-PIP-ISO-20003-000312-P-0120003-BA51-ESP-0312WS05PW-013N/A24.01.2021QQS-SDF-0017REJ100X4505-100
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J1:J10Cell Value="ACC"textNO
J1:J10Cell Value="REJ"textNO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I don't have Excel at the moment, but you may elaborate on these questions, so others may jump in and assist.
1) how many rejection rate we have in persent %....
Rich (BB code):
how do you reach this result currently ??
2) which welder has rejection rate..........
Rich (BB code):
as above
4) Average value for the entire duration of the project. %............
Rich (BB code):
as above
Can the data be sorted ?? If so, by which column ?
Can excess lines be removed ?? ie, duplicate data.....If so, what criteria specifies the removal of said lines ??
 
Upvote 0
MREXCEL EXAMPLE.xlsb
FGHIJKLMNOPQRSTUVWX
1Welder 1Welder 2RT DateRT ReportRT ResultRej LengthImperfection TypeRT Rej LengthRej WelderRejection rate NEW TABLE ( WHICH I IMAGINE AND SHOULD WORK AUTOMATIC ) I JUST DESCRIBE SOME PRINCIPLES HOW IT SHOULD LOOK LIKE
2PW-001N/AN/AN/AN/AN/AN/A0example:Report date or weekly cyclereport numberwelder 1welder 2rt resultRepaired Joints
3PW-009N/AN/AN/AN/AN/AN/A0for week 1 in january23.01.2021QQS-SDF-0021WS03PW-001accthis inormation came from 3d party
4PW-005N/AN/AN/AN/AN/AN/A024.01.2021QQS-SDF-0010WS07PW-036rejand I write this number manually
5PW-007N/AN/AN/AN/AN/AN/A025.01.2021etc….etc….etc….etc….
6PW-030N/AN/AN/AN/AN/AN/A026.01.2021etc….etc….etc….etc….
7PW-034N/AN/AN/AN/AN/AN/A027.01.2021etc….etc….etc….etc….
8PW-013N/AN/AN/AN/AN/AN/A027.01.2021etc….etc….etc….etc….
9PW-026N/AN/AN/AN/AN/AN/A028.01.2021etc….etc….etc….etc….
10PW-035N/A22.01.2021QQS-SDF-0005ACC100X4000029.01.2021etc….etc….etc….etc….
11PW-035N/AN/AN/AN/AN/AN/A0Total reports number :8
12PW-036N/AN/AN/AN/AN/AN/A0
13PW-013N/A22.01.2021QQS-SDF-0006ACC100X40000
14PW-005N/AN/AN/AN/AN/AN/A0
15PW-001N/AN/AN/AN/AN/AN/A0exampleRejection rate NEW TABLE ( WHICH I IMAGINE AND SHOULD WORK AUTOMATIC ) I JUST DESCRIBE SOME PRINCIPLES HOW IT SHOULD LOOK LIKE
16PW-001N/AN/AN/AN/AN/AN/A0for week 2 in januaryReport date or weekly cyclereport numberwelder 1welder 2rt resultRepaired Joints
17PW-030N/A28.01.2021QQS-SDF-0003ACC100X4500023.01.2021QQS-SDF-0021WS03PW-001accthis inormation came from 3d party
18PW-009N/AN/AN/AN/AN/AN/A024.01.2021QQS-SDF-0010WS07PW-036rejand I write this number manually
19PW-034N/AN/AN/AN/AN/AN/A025.01.2021etc….etc….etc….etc….
20PW-007N/A22.01.2021QQS-SDF-0004ACC100X4500026.01.2021etc….etc….etc….etc….
21PW-026N/AN/AN/AN/AN/AN/A027.01.2021etc….etc….etc….etc….
22PW-029N/AN/AN/AN/AN/AN/A027.01.2021etc….etc….etc….etc….
23PW-036N/AN/AN/AN/AN/AN/A028.01.2021etc….etc….etc….etc….
24PW-035N/AN/AN/AN/AN/AN/A029.01.2021etc….etc….etc….etc….
25PW-031N/A22.01.2021QQS-SDF-0008ACC100X45000Total reports number :8
26PW-033N/AN/AN/AN/AN/AN/A0
27PW-026N/AN/AN/AN/AN/AN/A0
28PW-026N/AN/AN/AN/AN/AN/A0exampleRejection rate NEW TABLE ( WHICH I IMAGINE AND SHOULD WORK AUTOMATIC ) I JUST DESCRIBE SOME PRINCIPLES HOW IT SHOULD LOOK LIKE
29PW-026N/AN/AN/AN/AN/AN/A0for week 3 in januaryReport date or weekly cyclereport numberwelder 1welder 2rt resultRepaired Joints
30PW-030N/AN/AN/AN/AN/AN/A023.01.2021QQS-SDF-0021WS03PW-001accthis inormation came from 3d party
31PW-001N/A23.01.2021QQS-SDF-0021ACC100X4500024.01.2021QQS-SDF-0010WS07PW-036rejand I write this number manually
32PW-034N/A23.01.2021QQS-SDF-0007REJ100X4500-35025.01.2021etc….etc….etc….etc….
33PW-013N/AN/AN/AN/AN/AN/A026.01.2021etc….etc….etc….etc….
34PW-001N/AN/AN/AN/AN/AN/A027.01.2021etc….etc….etc….etc….
35PW-009N/AN/AN/AN/AN/AN/A027.01.2021etc….etc….etc….etc….
36PW-003N/AN/AN/AN/AN/AN/A028.01.2021etc….etc….etc….etc….
37PW-005N/AN/AN/AN/AN/AN/A029.01.2021etc….etc….etc….etc….
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J2:J123Cell Value="ACC"textNO
J2:J123Cell Value="REJ"textNO
 
Upvote 0
Weld mapping is a fairly unusual application that not many members are likely to know much about. so to be clear, you are assessing which welders are not performing by analysing the weld map test results?
 
Upvote 0
Solution
Weld mapping is a fairly unusual application that not many members are likely to know much about. so to be clear, you are assessing which welders are not performing by analysing the weld map test results?
ok i try to split information by little peace
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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