DRAWING | ISO | SPOOL | INC | Weld № | Welder 1 | Welder 2 | RT Date | RT Report | RT Result | Rej Length | Imperfection Type | RT Rej Length | Rej Welder | Rejection rate NEW TABLE ( WHICH I IMAGINE AND SHOULD WORK AUTOMATIC ) I JUST DESCRIBE SOME PRINCIPLES HOW IT SHOULD LOOK LIKE | Total for a week period | Total in jeneral | old manual counted rejection rate | ||||||||||||||||||||||
HS523217-01-PIP-ISO-10207-0012 | 10-LLC-0110207-AA23-HC | SP-02 | 10 | WS02 | PW-009 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | example | : | Report date or weekly cycle | report number | welder 1 | welder 2 | rt result | Repaired Joints | TARGET | WEEKLY | Average | january | % | Full | RT Performed | Repaired Joints | Repair % | ||||||||||
HS523217-01-PIP-ISO-10207-0012 | 10-LLC-0110207-AA23-HC | SP-04 | 10 | WS06 | PW-009 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | for week 1 in january | 23.01.2021 | QQS-SDF-0021 | WS03 | PW-001 | acc | this inormation came from 3d party | 5% | in % | Average | february | % | Target | week | Average | ||||||||||||
HS523217-01-PIP-ISO-10207-0018 | 10-LLC-0110207-AA23-HC | SP-03 | 10 | WS04 | PW-036 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 24.01.2021 | QQS-SDF-0010 | WS07 | PW-036 | rej | and I write this number manually | Average | march | % | ###### | 0 | 0 | 5% | #DIV/0! | #DIV/0! | ||||||||||||
HS523217-01-PIP-ISO-10207-0018 | 10-LLC-0110207-AA23-HC | SP-02 | 10 | WS02 | PW-036 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 25.01.2021 | etc…. | etc…. | etc…. | etc…. | Average | april | % | ###### | 2 | 1 | 5% | 50,00% | 50,00% | |||||||||||||
HS523217-01-PIP-ISO-20003-0005 | 12-P-0120003-BA51-E | SP-02 | 12 | WS03 | PW-035 | N/A | 23.01.2021 | QQS-SDF-0001 | ACC | 100X450 | 0 | 0 | 26.01.2021 | etc…. | etc…. | etc…. | etc…. | formula RT total / Repaired Joints * 100% | Average | may | % | ###### | 39 | 0 | 5% | 0,00% | 2,44% | ||||||||||||
HS523217-01-PIP-ISO-20003-0009 | 12-P-0120003-BA51-E | SP-01 | 12 | WS02 | PW-005 | N/A | 22.01.2021 | QQS-SDF-0002 | ACC | 100X450 | 0 | 0 | 27.01.2021 | etc…. | etc…. | etc…. | etc…. | Average | june | % | ###### | 43 | 3 | 5% | 6,98% | 4,76% | |||||||||||||
HS523217-01-PIP-ISO-60105-0028 | 10-N2-0160105-AA23-NI | SP-02 | 10 | WS03 | PW-034 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 27.01.2021 | etc…. | etc…. | etc…. | etc…. | Average | july | % | ###### | 99 | 12 | 5% | 12,12% | 8,74% | |||||||||||||
HS523217-01-PIP-ISO-60105-0031 | 10-N2-0160105-AA23-NI | SP-03 | 10 | WS06 | PW-001 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 28.01.2021 | etc…. | etc…. | etc…. | etc…. | Average | august | % | ###### | 74 | 4 | 5% | 5,41% | 7,78% | |||||||||||||
HS523217-01-PIP-ISO-60105-0030 | 10-N2-0160105-AA23-NI | SP-05 | 10 | WS09 | PW-013 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 29.01.2021 | etc…. | etc…. | etc…. | etc…. | Average | september | % | ###### | 153 | 4 | 5% | 2,61% | 5,85% | |||||||||||||
HS523217-01-PIP-ISO-60105-0024 | 10-N2-0160105-AA23-NI | SP-05 | 10 | WS09 | PW-030 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | Total reports number : | 8 | Average | october | % | ###### | 112 | 6 | 5% | 5,36% | 5,75% | ||||||||||||||||
HS523217-01-PIP-ISO-20003-0005 | 12-P-0120003-BA51-E | SP-01 | 12 | WS01 | PW-035 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | Average | november | % | ###### | 40 | 2 | 5% | 5,00% | 5,69% | ||||||||||||||||||
HS523217-01-PIP-ISO-00211-0022 | 12-P-0100211-BA51-NI | SP-02 | 12 | WS05 | PW-031 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | Average | december | % | ###### | 12 | 0 | 5% | 0,00% | 5,57% | ||||||||||||||||||
HS523217-01-PIP-ISO-10819-0015 | 16-UW-0110819-AA52-NI | SP-02 | 16 | WS03 | PW-003 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | ###### | 44 | 3 | 5% | 6,82% | 5,66% | |||||||||||||||||||||
HS523217-01-PIP-ISO-60105-0031 | 10-N2-0160105-AA23-NI | SP-03 | 3/4 | WS04 | PW-001 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 1) | Note: when we are counting the Rejection rate we are start count from Friday till next Friday. | ###### | 39 | 1 | 5% | 2,56% | 5,48% | |||||||||||||||||||
HS523217-01-PIP-ISO-20003-0005 | 12-P-0120003-BA51-E | SP-03 | 12 | WS05 | PW-007 | N/A | 24.01.2021 | QQS-SDF-0009 | ACC | 100X450 | 0 | 0 | ###### | 21 | 1 | 5% | 4,76% | 5,46% | |||||||||||||||||||||
HS523217-01-PIP-ISO-00211-0022 | 12-P-0100211-BA51-NI | SP-03 | 12 | WS07 | PW-036 | N/A | 24.01.2021 | QQS-SDF-0010 | REJ | 100X450 | 0 | 0 | ###### | 39 | 3 | 5% | 7,69% | 5,58% | |||||||||||||||||||||
HS523217-01-PIP-ISO-10819-0015 | 16-UW-0110819-AA52-NI | SP-01 | 16 | WS01 | PW-030 | N/A | 26.01.2021 | QQS-SDF-0012 | ACC | 100X550 | 0 | 0 | etc…. For next weeks and months | ###### | 50 | 1 | 5% | 2,00% | 5,35% | ||||||||||||||||||||
HS523217-01-PIP-ISO-10405-0010 | 16-FG-0110405-AA51-NI | SP-02 | 16 | WS03 | PW-035 | PW-036 | N/A | N/A | N/A | N/A | N/A | 0 | ###### | 23 | 0 | 5% | 0,00% | 5,19% | |||||||||||||||||||||
HS523217-01-PIP-ISO-00220-0016 | 10-P-0100220-BA21-NI | SP-02 | 10 | WS03 | PW-049 | N/A | 25.01.2021 | QQS-SDF-0018 | ACC | 100X400 | 0 | 0 | ###### | 48 | 3 | 5% | 6,25% | 5,25% | |||||||||||||||||||||
HS523217-01-PIP-ISO-10405-0010 | 16-FG-0110405-AA51-NI | SP-01 | 16 | WS01 | PW-001 | N/A | 03.02.2021 | QQS-SDF-0013 | ACC | 100X550 | 0 | 0 | 2) | Report Number | ###### | 43 | 3 | 5% | 6,98% | 5,33% | |||||||||||||||||||
HS523217-01-PIP-ISO-10819-0021 | 16-UW-0110819-AA52-NI | SP-01 | 16 | WS01 | PW-001 | N/A | 25.01.2021 | QQS-SDF-0014 | REJ | 100X550 | 15-20 | 0 | ###### | 5% | #DIV/0! | 5,33% | |||||||||||||||||||||||
HS523217-01-PIP-ISO-00211-0024 | 12-P-0100211-BA51-NI | SP-02 | 12 | WS06 | PW-007 | N/A | 25.01.2021 | QQS-SDF-0016 | ACC | 100X450 | 0 | 0 | ###### | 5% | #DIV/0! | 5,33% | |||||||||||||||||||||||
HS523217-01-PIP-ISO-00211-0022 | 12-P-0100211-BA51-NI | SP-04 | 12 | WS09 | PW-005 | N/A | 25.01.2021 | QQS-SDF-0015 | ACC | 100X450 | 0 | 0 | ###### | 5% | #DIV/0! | 5,33% | |||||||||||||||||||||||
HS523217-01-PIP-ISO-20003-0003 | 12-P-0120003-BA51-E | SP-03 | 12 | WS05 | PW-013 | N/A | 24.01.2021 | QQS-SDF-0017 | REJ | 100X450 | PW-013 | 5-10 | 0 | 5% | #DIV/0! | 5,33% | |||||||||||||||||||||||
HS523217-01-PIP-ISO-20003-0003 | 12-P-0120003-BA51-E | SP-03 | 12 | WS03 | PW-013 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 5% | #DIV/0! | 5,33% | ||||||||||||||||||||||||
HS523217-01-PIP-ISO-20003-0003 | 12-P-0120003-BA51-E | SP-03 | 12 | WS04 | PW-013 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | 3) | Welder info | 5% | #DIV/0! | 5,33% | ||||||||||||||||||||||
HS523217-01-PIP-ISO-00211-0024 | 12-P-0100211-BA51-NI | SP-02 | 12 | WS07 | PW-007 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-00211-0022 | 12-P-0100211-BA51-NI | SP-01 | 12 | WS03 | PW-005 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-00220-0016 | 10-P-0100220-BA21-NI | SP-01 | 10 | WS01 | PW-050 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-01006-0022 | 10-H2-0101006-BA21H-NI | SP-02 | 10 | WS03 | PW-050 | N/A | 24.01.2021 | QQS-SDF-0040 | ACC | 100X400 | 0 | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10405-0012 | 16-FG-0110405-AA51-NI | SP-05 | 16 | WS09 | PW-030 | PW-034 | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10819-0013 | 16-UW-0110819-AA52-NI | SP-04 | 16 | WS06 | PW-026 | PW-003 | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10819-0013 | 16-UW-0110819-AA52-NI | SP-04 | 16 | WS07 | PW-026 | PW-003 | N/A | N/A | N/A | N/A | N/A | 0 | Rejection rate (more productivity version) planning creating something like this | ||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10819-0015 | 16-UW-0110819-AA52-NI | SP-03 | 16 | WS05 | PW-035 | PW-036 | N/A | N/A | N/A | N/A | N/A | 0 | 4) | Rt esult | old rejection rate | ||||||||||||||||||||||||
HS523217-01-PIP-ISO-00211-0015 | 12-P-0100211-BA51-NI | SP-02 | 12 | WS04 | PW-029 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-00220-0018 | 10-P-0100220-BA21-NI | SP-02 | 10 | WS03 | PW-042 | N/A | 23.01.2021 | QQS-SDF-0022 | ACC | 100X400 | 0 | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-01006-0024 | 10-H2-0101006-BA21H-NI | SP-02 | 10 | WS04 | PW-049 | N/A | 24.01.2021 | QQS-SDF-0050/0042 | ACC | 100X400 | 0 | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-00220-0018 | 10-P-0100220-BA21-NI | SP-03 | 10 | WS05 | PW-067 | N/A | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10405-0018 | 16-FG-0110405-AA51-NI | SP-02 | 16 | WS03 | PW-001 | PW-009 | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10405-0018 | 16-FG-0110405-AA51-NI | SP-02 | 16 | WS04 | PW-001 | PW-009 | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-00501-0017 | 6-P-0100501-AA21-E | SP-01 | 6 | WS02 | PW-048 | N/A | 24.01.2021 | QQS-SDF-0023 | ACC | 100X300 | 0 | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10819-0021 | 16-UW-0110819-AA52-NI | SP-04 | 16 | WS07 | PW-035 | PW-036 | N/A | N/A | N/A | N/A | N/A | 0 | 5) | Repaired Joints this inormation came from 3d party and I write this number manually | |||||||||||||||||||||||||
HS523217-01-PIP-ISO-10405-0016 | 16-FG-0110405-AA51-NI | SP-05 | 16 | WS09 | PW-030 | PW-034 | N/A | N/A | N/A | N/A | N/A | 0 | |||||||||||||||||||||||||||
HS523217-01-PIP-ISO-10819-0019 | 16-UW-0110819-AA52-NI | SP-03 | 16 | WS08 | PW-050 | PW-044 | 24.01.2021 | QQS-SDF-0019 | ACC | 100X550 | 0 | 0 | |||||||||||||||||||||||||||
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 | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | HS523217-01-PIP-ISO-20003-0005 | 12-P-0120003-BA51-E | SP-03 | 12 | WS05 | PW-007 | N/A | 24.01.2021 | QQS-SDF-0009 | ACC | 100X450 | 0 | 0 | 24.01.2021 | QQS-SDF-0009 | |||
2 | HS523217-01-PIP-ISO-00211-0022 | 12-P-0100211-BA51-NI | SP-03 | 12 | WS07 | PW-036 | N/A | 24.01.2021 | QQS-SDF-0010 | REJ | 100X450 | 0 | 0 | QQS-SDF-0010 | ||||
3 | HS523217-01-PIP-ISO-10819-0015 | 16-UW-0110819-AA52-NI | SP-01 | 16 | WS01 | PW-030 | N/A | 26.01.2021 | QQS-SDF-0012 | ACC | 100X550 | 0 | 0 | QQS-SDF-0017 | ||||
4 | HS523217-01-PIP-ISO-10405-0010 | 16-FG-0110405-AA51-NI | SP-02 | 16 | WS03 | PW-035 | PW-036 | N/A | N/A | N/A | N/A | N/A | 0 | |||||
5 | HS523217-01-PIP-ISO-00220-0016 | 10-P-0100220-BA21-NI | SP-02 | 10 | WS03 | PW-049 | N/A | 25.01.2021 | QQS-SDF-0018 | ACC | 100X400 | 0 | 0 | |||||
6 | HS523217-01-PIP-ISO-10405-0010 | 16-FG-0110405-AA51-NI | SP-01 | 16 | WS01 | PW-001 | N/A | 03.02.2021 | QQS-SDF-0013 | ACC | 100X550 | 0 | 0 | |||||
7 | HS523217-01-PIP-ISO-10819-0021 | 16-UW-0110819-AA52-NI | SP-01 | 16 | WS01 | PW-001 | N/A | 25.01.2021 | QQS-SDF-0014 | REJ | 100X550 | 15-20 | 0 | 25.01.2021 | ????? | |||
8 | HS523217-01-PIP-ISO-00211-0024 | 12-P-0100211-BA51-NI | SP-02 | 12 | WS06 | PW-007 | N/A | 25.01.2021 | QQS-SDF-0016 | ACC | 100X450 | 0 | 0 | ???? | ||||
9 | HS523217-01-PIP-ISO-00211-0022 | 12-P-0100211-BA51-NI | SP-04 | 12 | WS09 | PW-005 | N/A | 25.01.2021 | QQS-SDF-0015 | ACC | 100X450 | 0 | 0 | How I can separet ? | ???? | |||
10 | HS523217-01-PIP-ISO-20003-0003 | 12-P-0120003-BA51-E | SP-03 | 12 | WS05 | PW-013 | N/A | 24.01.2021 | QQS-SDF-0017 | REJ | 100X450 | 5-10 | 0 | |||||
Sheet2 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
J1:J10 | Cell Value | ="ACC" | text | NO |
J1:J10 | Cell Value | ="REJ" | text | NO |