Find Text, Replace Value Adjacent to Found Text, Replace with Formula

[NeilATaylor]

Hi
I have a sheet of data where the same account needs to be adjusted, the monotony of the same adjustment over and over again is doing my head in!!!!
There are 50 or more companies on this trial balance, which doesn't balance, because of an account that refuses to update (from our finance system - unfixable at this moment).
I have to change the account (8150 - Intercompany Account) myself.

Here is what the sheet looks like:

TRIAL BALANCE 010208.xls
	B	C	D	E	F	G
1099	Company 1
1100		7290 - Orig Posting Bank Cd	0.00	0.20
1101		7316 - SL owed by PPPP	2,014,524.60	0.00
1102		7800 - Stock & WIP	0.00	0.11
1103		8150 - DON'T USE: AUTOMATIC Intercompany A/C	0.00	1,690,862.89
1104		8176 - Manual Intercompany Account (S with G)	1,645.83	0.00
1105		8706 - Provision for Merged Accounts	0.00	0.02
1106		8800 - Accruals	0.05	0.00
1107		9203 - LOANS MASTER LOAD ACCOUNT	0.30	0.00
1108		9702 - Cumulative Net Surplus/Deficit	0.00	187,392.96
1109		9703 - Prior Year Retained Earnings	8,016.00	0.00
1110		9707 - Revenue Reserves	0.00	16,565.13
1111		9755 - Corporation Tax Payable	0.00	0.23
1112		Total 59:	2,024,186.78	1,894,821.54		-129,365.24
1113
1114	Company 2
1115		4038 - Stationery	169.21	0.00
1116		4043 - Sundry Management	56,250.00	0.00
1117		4614 - Allocations New Business Development	213,583.67	0.00
1118		5600 - Abortive Fees	600.00	0.00
1119		6304 - Prop Under Cons Cost	7,005.76	0.00
1120		8150 - DON'T USE: AUTOMATIC Intercompany A/C	0.00	9,380,253.73
1121		8176 - Manual Intercompany Account (S with G)	0.00	587.75
1122		8658 - 1st Tranche Marketing & Legal	587.50	0.00
1123		8675 - Shortlife Scheme Retention	0.00	1,349.14
1124		8750 - Input VAT	105.00	0.00
1125		9702 - Cumulative Net Surplus/Deficit	207,411.44	0.00
1126		9703 - Prior Year Retained Earnings	0.00	209,488.06
1127		Total 60:	485,712.58	9,591,678.68		9,105,966.10
All

My fix for this is to manually type (in cell E1103 "Interco") +1690862.89+129365.24, which gives the result needed to get the thing to balance, with G1112 equalling zero.
I would really appreciate if someone could get me started on some code for this!
It would require replacing all values in 8150, for each company, with a result that makes the formula in column G (i.e. G1112 and G1127) equal zero.

Thanks in advance
Neil

 

[Dan Rittgers]

Your download/upload recognizes " : " as a column delimiter, rather than treating it as text.

Look in Cell F1103

 

[NeilATaylor]

Thanks for your reply Dan; I was just wondering how this could be fixed? Is it the original report from the Finance System that needs to be changed?

I would be grateful if someone could produce a cope that went along the lines of "find account 8150, adjust by difference between sum column D & E" just for my own learning.

Thanks
Neil

 

[Dan Rittgers]

Regards your immediate data, if the data is in cell F1103 you can probably use =trim(F1103).

Disqualifier: I am not a systems guy, but I have asked many questions in my career. The following is based upon my understanding.

Issue
Your upload recognizes the : as a column delimiter. You probably have two (3 if you extract from an (ODBC-Access or BO data warehouse) and each of these provides the opportunity to corrupt the data.

Solution
Talk to IT and ask if you can test a protocol; request that the data be downloaded as text/tab/delimited. Second before uploading into Excel perform a find-and-replace. Replace the : with a ^, < or > or some other character that will not cause the issue. When the data gets to Excel you do a find-and-replace to replace that character with :

 

[NeilATaylor]

Cheers Dan, that seems to make sense. I'm running it by IT, we are going to test it - will let you know if your theory is correct.

 

[Dan Rittgers]

Niel

You are using Trail Balance info. This recommendation is an complete protocol for Detail Transactions and AP transactions.

You data appearing in Colunm B, may be stored in column A as text. Thus the Account Number and Description may be conjoined. The recommended protocol can include parsing the data into Col A countaing Account and Col B containg Description. Which might be good for Lookups.

Good Luck.