How to adjust values in columns after removing the rows?

[tarunr9]

Hi
I want to remove the row where column C and column D both are #N/A( highlighted rows) and the column B should be adjusted accordingly like all the Adapter values in column B add upto 100%( approximately- varries between 99-100) , but if we remove the lines , the adjustment to all the remaining adapter values should be made such that all the adapter values still add upto 100%.
This can be done in 2 or 3 steps. Does not have to be one single code.
Thank you everyone in Advance.

spam.xlsx
	A	B	C	D
1	Adapter - Binding/Sticking	0.047%	#N/A	42.55865
2	Adapter - Induced Failure	0.047%	#N/A	42.55865
3	Adapter - Spring Failure	0.047%	#N/A	42.55865
4	Adapter - Defective	0.155%	#N/A	2.759178
5	Adapter - Malfunctioned	0.077%	#N/A	2.759178
6	Adapter - Compressor Failure	0.543%	#N/A	7.318915
7	Adapter - Electrical Failure	0.660%	#N/A	2160.103
8	Adapter - Alignment Improper	0.036%	#N/A	2160.103
9	Adapter - Scored	0.029%	#N/A	2160.103
10	Adapter - Faulty Card	0.017%	#N/A	2160.103
11	Adapter - Improper Response To Electrical Input	0.017%	#N/A	2160.103
12	Adapter - Contact Failure	0.005%	#N/A	2160.103
13	Adapter - Open	0.005%	#N/A	2160.103
14	Adapter - Over Temperature	0.005%	#N/A	2160.103
15	Adapter - Erratic Operation	1.087%	#N/A	#N/A
16	Adapter - Burned	1.087%	#N/A	3.251715
17	Adapter - Fuse Blown	0.121%	#N/A	4.68129
18	Adapter - Motor Failure	0.121%	#N/A	4.68129
19	Adapter - Grounded Element	1.087%	#N/A	2.685849
20	Adapter - Cross Threaded	1.087%	#N/A	3.862933
21	Adapter - Audio Faulty	0.073%	#N/A	5.56931
22	Adapter - Clogged	0.543%	#N/A	3.939589
23	Adapter - No Defect Found	0.049%	#N/A	#N/A
24	Adapter - Improper Operation	0.182%	#N/A	8.798532
25	Adapter - Failed To Reset	0.272%	#N/A	#N/A
26	Adapter - Snapped	0.272%	#N/A	12.14699
27	Adapter - Metal Shavings	0.217%	#N/A	8.212941
28	Adapter - Seized	0.217%	#N/A	8.212941
29	Adapter - Ripped	0.182%	#N/A	5.93283
30	Adapter - Intermittent	0.084%	#N/A	12.41073
31	Adapter - Short	0.543%	#N/A	20.4219
32	Adapter - Leakage	8.700%	0	297.6423
33	Adapter - Seal Failure	0.443%	0	18.0848
34	Adapter - Broken	12.371%	0	98.1085
35	Adapter - Overheated	1.193%	0	10.45898
36	Adapter - Cracked	6.875%	0	29.54297
37	Adapter - Defective Component	0.409%	0	7.844235
38	Adapter - Deteriorated Protective Coatings	1.134%	0	1087.896
39	Adapter - Mechanical Failure	0.805%	0	6.601005
40	Adapter - Stripped	1.134%	0	97.45548
41	Adapter - Worn	11.475%	0	19.34265
42	Adapter - Loose	7.249%	0	151.432
43	Adapter - Bent	1.341%	0	20.05558
44	Adapter - Missing	5.820%	0	11.95107
45	Adapter - Corrosion	4.692%	0	13.46135
46	Adapter - Improper Output	3.903%	0	31.59637
47	Adapter - Failed To Operate	4.104%	0	16.62671
48	Adapter - Inoperative	1.351%	0	17.23569
49	Adapter - Deteriorated	0.725%	0	88.62579
50	Adapter - Degraded Operation	0.314%	0	7.616585
51	Adapter - Rattle	1.196%	0	8.223965
52	Adapter - Failed Calibration	0.078%	0	2.890912
53	Adapter - Out of Specification	2.457%	0	543.5489
54	Adapter - Oil Leakage	0.725%	0	2.391552
55	Adapter - Fails During Operation	0.954%	0	543.5489
56	Adapter - Vibration	1.630%	0	27.25013
57	Adapter - Unknown	0.246%	0	1086.023
58	Adapter - Back Lash	0.290%	0	30.65126
59	Adapter - Binding	0.290%	0	2.753284
60	Adapter - Blown	0.347%	0	1.075231
61	Adapter - Chaffed	0.567%	0	5.198027
62	Adapter - Contaminated	0.652%	0	1101.331
63	Adapter Assembly - Broken	0.350%	#N/A	#N/A
64	Adapter Assembly - Binding/Sticking	0.022%	#N/A	#N/A
65	Adapter Assembly - Deteriorated Protective Coatings	0.022%	#N/A	#N/A
66	Adapter Assembly - Alignment Improper	0.008%	#N/A	#N/A
67	Adapter Assembly - Improper Response To Electrical Inpu	0.005%	#N/A	#N/A
68	Adapter Assembly - Out of Adjustment	0.005%	#N/A	#N/A
69	Adapter Assembly - Cut	0.002%	#N/A	#N/A
70	Adapter Assembly - Missing	0.002%	#N/A	#N/A
71	Adapter Assembly - Out of Specification	0.002%	#N/A	#N/A
72	Adapter Assembly - Jammed	1.087%	#N/A	3.49771
73	Adapter Assembly - Cracked	0.757%	0	15.68847
74	Adapter Assembly - Worn	0.573%	0	15.68847
75	Adapter Assembly - Loose	2.600%	0	15.68847
76	Adapter Connection - Broken	1.087%	#N/A	3.234771
77	Adapter Pack - Corrosion	0.362%	#N/A	4.847099
78	Adapter Pack - Deteriorated	0.362%	#N/A	4.847099
79	Adapter Pack - Jammed	0.362%	#N/A	4.847099
80	Adaptive Gain Module - Inoperative	61.100%	#N/A	10.57001
81	Adaptive Gain Module - Degraded Operation	16.700%	#N/A	10.57001
82	Adaptive Gain Module - Failed To Operate	11.100%	#N/A	10.57001
83	Adaptive Gain Module - Damaged	5.600%	#N/A	10.57001
84	Adaptive Gain Module - Defective	5.600%	#N/A	10.57001
85	Adjuster - Corrosion	25.000%	#N/A	10.82645
86	Adjuster - Failed To Operate	15.375%	#N/A	2.139137
87	Adjuster - Inoperative	7.700%	#N/A	2.139137
88	Adjuster - Unable To Adjust To Limits	1.925%	#N/A	2.139137
89	Adjuster - Cracked	25.000%	#N/A	10.82645
90	Adjuster Assembly - Corrosion	12.500%	#N/A	#N/A
91	Adjuster Assembly - Cracked	12.500%	#N/A	#N/A
92	Adjusting Yoke - Corrosion	58.300%	#N/A	247.6667
93	Adjusting Yoke - Warped	16.700%	#N/A	247.6667
94	Adjusting Yoke - Contaminated	8.300%	#N/A	247.6667
95	Adjusting Yoke - Cracked	8.300%	#N/A	247.6667
96	Adjusting Yoke - Missing	8.300%	#N/A	247.6667
97	Adjustment Assembly - Broken	28.600%	#N/A	122.1796
98	Adjustment Assembly - Chaffed	28.600%	#N/A	122.1796
99	Adjustment Assembly - Damaged	14.300%	#N/A	122.1796
100	Adjustment Assembly - Malfunctioned	14.300%	#N/A	122.1796
101	Adjustment Assembly - Unable To Adjust To Limits	14.300%	#N/A	122.1796
102	Agitator - Degraded Operation	200.000%	#N/A	121.6503
103	Agitator - Degraded Operation	200.000%	#N/A	121.6503
104	Aileron - Loose	16.650%	#N/A	#N/A
105	Aileron - Cracked	10.200%	#N/A	#N/A
106	Aileron - Worn	6.500%	#N/A	#N/A
107	Aileron - Broken	3.700%	#N/A	#N/A
108	Aileron - Delamination	3.700%	#N/A	#N/A
109	Aileron - Bent	2.800%	#N/A	#N/A
110	Aileron - Water Damage	2.800%	#N/A	#N/A
111	Aileron - Deteriorated Protective Coatings	0.950%	#N/A	#N/A
112	Aileron - Overheated	0.950%	#N/A	#N/A
113	Aileron - Scored	0.950%	#N/A	#N/A
114	Aileron - Voids	0.950%	#N/A	#N/A
115	Aileron Assembly - Worn	21.750%	#N/A	2.063998
116	Aileron Assembly - Scored	10.850%	#N/A	2.063998
117	Aileron Assembly - Bent	6.500%	#N/A	2.063998
118	Aileron Assembly - Cracked	6.500%	#N/A	2.063998
119	Aileron Assembly - Broken	4.350%	#N/A	2.063998
120	Aiming Post - Induced Failure	50.000%	#N/A	#N/A
121	Aiming Post - Mechanical Failure	18.200%	#N/A	#N/A
122	Aiming Post - Worn	9.100%	#N/A	#N/A
123	Aiming Post - Workmanship	18.200%	#N/A	#N/A
124	Aiming Post - Unknown	4.500%	#N/A	#N/A
Sheet1

 

[maabadi]

A. What about the values more than 100%?
1. Delete Them ( Row)
2. Decrease them to 100%
3. Minus their value by 100% e.g. if Value is 165% Decrease it to 65%

B. I see when Column D is #N/A then Column C is #N/A Always. Is this correct for all of your data?

C. please show result that you want also

 

[tarunr9]

A. What about the values more than 100%?
1. Delete Them ( Row)
2. Decrease them to 100%
3. Minus their value by 100% e.g. if Value is 165% Decrease it to 65%

B. I see when Column D is #N/A then Column C is #N/A Always. Is this correct for all of your data?

Thank you for the reply but data sheet contains more than 20000 rows. Is there any formula I could use?
No , not everywhere, in someplaces, column C has values where Column D is #N/A.

 

[maabadi]

1. First add One Header row for your Data. Select first row & Insert. then Give Title to Columns.
2. Select All data with CTRL+A
3. At the Home Tab, Select Sort and Filter Dropdown Menu, Then Select Filter
4. Click on Arrow apears at first row at column C
5. at the menu apears at the bottom section, first unmark select all, then Only Select #N/A Option.
6. Repeat Step 5 for Column D
7. Hold ALT key and Press ; Key
8. At the Home Tab, Select arrow below Delete then Select Delete Sheet Row.

at the second Step also you can filter data based column B ( same 4 step at the above but column B), Then for step 5 , Go to Number filter and select Greater than option for filter data greater than 100% and then Edit them