Need Help for if this manual task can be automated

[suresh7860]

Hi Experts

Good Evening!

I am doing a task of updating the data manually for each criteria in Column V (Exh Pos / REVAL+CIS+FX / REVAL-SPOT) by filtering Exh Pos then if the sum of the amount in column H (AUD/CNY/ INR/USD/EUR/GBP......... Etc will be 25-30 different currencies) is positive then it should update column N with P35800 & Column P with IP35800 or if negative then it should updated with A35800 & Column P with IA35800to but if it zero then no change.

For example below if i filter column v with Exh Pos and filter column AUD its coming sum is +25 then i should go and update all filtered spceial call until last row with in column N with P35800 & Column P with IP35800 and filter again with next currency i.e., CNY which is zero hence i will not make any changes...

I am doing this task for thousands of lines and for 25-30 currencies and for 3 or 5 criteria's and for different regions hence its taking too much of time to do would be helpful any one can help me with:

Column D	Column G	Column H ​ <colgroup><col></colgroup><tbody> </tbody>	Column N	Column P	Column V
6001	AUD	(100.00) <colgroup><col></colgroup><tbody> </tbody>	P35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IA35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6002	CNY	1.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6003	INR	150.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6004	USD	22.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6005	EUR	60.00 <tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6006	GBP	78.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6001	AUD	125.00 <colgroup><col></colgroup><tbody> </tbody>	P35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IA35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6002	CNY	(1.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6003	INR	(255.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6004	USD	(88.00) <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6005	EUR	(55.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6006	GBP	(152.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	Exh Pos <colgroup><col width="64"></colgroup><tbody> </tbody>
6001	AUD	(100.00) <colgroup><col></colgroup><tbody> </tbody>	P35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IA35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX <colgroup><col width="64"></colgroup><tbody> </tbody>
6002	CNY	1.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX <colgroup><col width="64"></colgroup><tbody> </tbody>
6003	INR	150.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX <colgroup><col width="64"></colgroup><tbody> </tbody>
6004	USD	22.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX <colgroup><col width="64"></colgroup><tbody> </tbody>
6005	EUR	60.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX <colgroup><col width="64"></colgroup><tbody> </tbody>
6006	GBP	78.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX <colgroup><col width="64"></colgroup><tbody> </tbody>
6001	AUD	125.00 <colgroup><col></colgroup><tbody> </tbody>	P35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IA35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX
6002	CNY	(1.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX
6003	INR	(255.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX
6004	USD	(88.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX
6005	EUR	(55.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX
6006	GBP	(152.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL+CIS+FX
6001	AUD	(100.00) <colgroup><col></colgroup><tbody> </tbody>	P35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IA35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6002	CNY	1.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6003	INR	150.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6004	USD	22.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6005	EUR	60.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6006	GBP	78.00 <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6001	AUD	125.00 <colgroup><col></colgroup><tbody> </tbody>	P35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IA35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6002	CNY	(1.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6003	INR	(255.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6004	USD	(88.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6005	EUR	(55.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>
6006	GBP	(152.00) <colgroup><col></colgroup><tbody> </tbody>	A35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	IP35800 <colgroup><col width="64"></colgroup><tbody> </tbody>	REVAL - SPOT <colgroup><col width="64"></colgroup><tbody> </tbody>

<tbody>
</tbody>

REVAL+CIS+FX

<colgroup><col width="64"></colgroup><tbody>
</tbody>

Thanks for your help!

Regards
Suresh

 

[RickXL]

Hi,

I can make it look like your data which is not quite like your description.

Excel 2013
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Column D			Column G	Column H						Column N		Column P						Column V
2	6001			AUD	-100						P35800		IA35800						Exh Pos
3	6002			CNY	1						A35800		IP35800						Exh Pos
4	6003			INR	150						A35800		IP35800						Exh Pos
5	6004			USD	22						A35800		IP35800						Exh Pos
6	6005			EUR	60						P35800		IA35800						Exh Pos
7	6006			GBP	78						A35800		IP35800						Exh Pos
8	6001			AUD	125						P35800		IA35800						Exh Pos
9	6002			CNY	-1						A35800		IP35800						Exh Pos
10	6003			INR	-255						A35800		IP35800						Exh Pos
11	6004			USD	-88						A35800		IP35800						Exh Pos
12	6005			EUR	-55						P35800		IA35800						Exh Pos
13	6006			GBP	-152						A35800		IP35800						Exh Pos
14	6001			AUD	-100						P35800		IA35800						REVAL+CIS+FX
15	6002			CNY	1						A35800		IP35800						REVAL+CIS+FX
16	6003			INR	150						A35800		IP35800						REVAL+CIS+FX
17	6004			USD	22						A35800		IP35800						REVAL+CIS+FX
18	6005			EUR	60						P35800		IA35800						REVAL+CIS+FX
19	6006			GBP	78						A35800		IP35800						REVAL+CIS+FX
20	6001			AUD	125						P35800		IA35800						REVAL+CIS+FX
21	6002			CNY	-1						A35800		IP35800						REVAL+CIS+FX
22	6003			INR	-255						A35800		IP35800						REVAL+CIS+FX
23	6004			USD	-88						A35800		IP35800						REVAL+CIS+FX
24	6005			EUR	-55						P35800		IA35800						REVAL+CIS+FX
25	6006			GBP	-152						A35800		IP35800						REVAL+CIS+FX
26	6001			AUD	-100						P35800		IA35800						REVAL - SPOT
27	6002			CNY	1						A35800		IP35800						REVAL - SPOT
28	6003			INR	150						A35800		IP35800						REVAL - SPOT
29	6004			USD	22						A35800		IP35800						REVAL - SPOT
30	6005			EUR	60						P35800		IA35800						REVAL - SPOT
31	6006			GBP	78						A35800		IP35800						REVAL - SPOT
32	6001			AUD	125						P35800		IA35800						REVAL - SPOT
33	6002			CNY	-1						A35800		IP35800						REVAL - SPOT
34	6003			INR	-255						A35800		IP35800						REVAL - SPOT
35	6004			USD	-88						A35800		IP35800						REVAL - SPOT
36	6005			EUR	-55						P35800		IA35800						REVAL - SPOT
37	6006			GBP	-152						A35800		IP35800						REVAL - SPOT
Sheet1

Cell Formulas
Range	Formula
N2	=SUMIFS($H$2:$H$37,$V$2:$V$37,$V2,$G$2:$G$37,$G2)
P2	=SUMIFS($H$2:$H$37,$V$2:$V$37,$V2,$G$2:$G$37,$G2)

The above formulas need to be pasted down.
Note: they end at row 37. Please change that to be whatever your final data row is.

I have created two Custom Formats for Columns N and P. The Custom Format for Column N is:
"P35800";"A35800";"A35800"
For column P it is:
"IA35800";"IP35800";"IP35800"

You need to right-click on a column, select Format Cells then choose Custom.
In the Edit Box labelled Type paste in one of the above strings. OK then add the next Custom Format.

The SUMIFS() formulas calculate the sum then the formats convert the positive and negative answers into the required strings.

Regards,

 

[suresh7860]

Thanks a lot @RickXl

It working good for negative sum perfectly but when it is zero then it is treating it as a positive and converting it as P/IP in respective columns. Is there a way to let zero balance data as it is in column N & O accordingly.

I changed the second formula as it should be shown in this way... If Positive, Column (N) P35800 then COlumn (O) IP35800 and if negative then Column (N) A35800 then column (O) IA35800 but when it is zero no need to change the data

Regards
Suresh7860

 

[suresh7860]

Sorry its working correctly I didn't catch the difference between the two custom formats which I have understood and changed it as per your format now its working fine for zero too and keeping as it is when it is zero "P35800";"A35800";"A35800" (N) & "IP35800";"IA35800";"IP35800" (O). Thanks a lot and have a great day !!

 

[RickXL]

Good news and thanks for the update.

Regards,