Brain Error ( not for everyone )

[Tofik]

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

 

[Tofik]

 

[Michael M]

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 %....

how do you reach this result currently ??

2) which welder has rejection rate..........

as above

4) Average value for the entire duration of the project. %............

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 ??

 

[Tofik]

MREXCEL EXAMPLE.xlsb
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	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
2	PW-001	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
3	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
4	PW-005	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
5	PW-007	N/A	N/A	N/A	N/A	N/A		N/A	0				25.01.2021	etc….	etc….	etc….	etc….
6	PW-030	N/A	N/A	N/A	N/A	N/A		N/A	0				26.01.2021	etc….	etc….	etc….	etc….
7	PW-034	N/A	N/A	N/A	N/A	N/A		N/A	0				27.01.2021	etc….	etc….	etc….	etc….
8	PW-013	N/A	N/A	N/A	N/A	N/A		N/A	0				27.01.2021	etc….	etc….	etc….	etc….
9	PW-026	N/A	N/A	N/A	N/A	N/A		N/A	0				28.01.2021	etc….	etc….	etc….	etc….
10	PW-035	N/A	22.01.2021	QQS-SDF-0005	ACC	100X400		0	0				29.01.2021	etc….	etc….	etc….	etc….
11	PW-035	N/A	N/A	N/A	N/A	N/A		N/A	0				Total reports number :	8
12	PW-036	N/A	N/A	N/A	N/A	N/A		N/A	0
13	PW-013	N/A	22.01.2021	QQS-SDF-0006	ACC	100X400		0	0
14	PW-005	N/A	N/A	N/A	N/A	N/A		N/A	0
15	PW-001	N/A	N/A	N/A	N/A	N/A		N/A	0		example		Rejection rate NEW TABLE ( WHICH I IMAGINE AND SHOULD WORK AUTOMATIC ) I JUST DESCRIBE SOME PRINCIPLES HOW IT SHOULD LOOK LIKE
16	PW-001	N/A	N/A	N/A	N/A	N/A		N/A	0		for week 2 in january		Report date or weekly cycle	report number	welder 1	welder 2	rt result	Repaired Joints
17	PW-030	N/A	28.01.2021	QQS-SDF-0003	ACC	100X450		0	0				23.01.2021	QQS-SDF-0021	WS03	PW-001	acc	this inormation came from 3d party
18	PW-009	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
19	PW-034	N/A	N/A	N/A	N/A	N/A		N/A	0				25.01.2021	etc….	etc….	etc….	etc….
20	PW-007	N/A	22.01.2021	QQS-SDF-0004	ACC	100X450		0	0				26.01.2021	etc….	etc….	etc….	etc….
21	PW-026	N/A	N/A	N/A	N/A	N/A		N/A	0				27.01.2021	etc….	etc….	etc….	etc….
22	PW-029	N/A	N/A	N/A	N/A	N/A		N/A	0				27.01.2021	etc….	etc….	etc….	etc….
23	PW-036	N/A	N/A	N/A	N/A	N/A		N/A	0				28.01.2021	etc….	etc….	etc….	etc….
24	PW-035	N/A	N/A	N/A	N/A	N/A		N/A	0				29.01.2021	etc….	etc….	etc….	etc….
25	PW-031	N/A	22.01.2021	QQS-SDF-0008	ACC	100X450		0	0				Total reports number :	8
26	PW-033	N/A	N/A	N/A	N/A	N/A		N/A	0
27	PW-026	N/A	N/A	N/A	N/A	N/A		N/A	0
28	PW-026	N/A	N/A	N/A	N/A	N/A		N/A	0		example		Rejection rate NEW TABLE ( WHICH I IMAGINE AND SHOULD WORK AUTOMATIC ) I JUST DESCRIBE SOME PRINCIPLES HOW IT SHOULD LOOK LIKE
29	PW-026	N/A	N/A	N/A	N/A	N/A		N/A	0		for week 3 in january		Report date or weekly cycle	report number	welder 1	welder 2	rt result	Repaired Joints
30	PW-030	N/A	N/A	N/A	N/A	N/A		N/A	0				23.01.2021	QQS-SDF-0021	WS03	PW-001	acc	this inormation came from 3d party
31	PW-001	N/A	23.01.2021	QQS-SDF-0021	ACC	100X450		0	0				24.01.2021	QQS-SDF-0010	WS07	PW-036	rej	and I write this number manually
32	PW-034	N/A	23.01.2021	QQS-SDF-0007	REJ	100X450		0-35	0				25.01.2021	etc….	etc….	etc….	etc….
33	PW-013	N/A	N/A	N/A	N/A	N/A		N/A	0				26.01.2021	etc….	etc….	etc….	etc….
34	PW-001	N/A	N/A	N/A	N/A	N/A		N/A	0				27.01.2021	etc….	etc….	etc….	etc….
35	PW-009	N/A	N/A	N/A	N/A	N/A		N/A	0				27.01.2021	etc….	etc….	etc….	etc….
36	PW-003	N/A	N/A	N/A	N/A	N/A		N/A	0				28.01.2021	etc….	etc….	etc….	etc….
37	PW-005	N/A	N/A	N/A	N/A	N/A		N/A	0				29.01.2021	etc….	etc….	etc….	etc….
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J123	Cell Value	="ACC"	text	NO
J2:J123	Cell Value	="REJ"	text	NO

 

[Tofik]

up

 

[diddi]

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?

 

[Tofik]

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