Hi,
I have attached a snapshot of current file which is created from Sage, it contains over 16,000 rows...
Our nominal codes as persheet "Raw Data are in the format of:
Account Number : Account Cost Centre: Account Department
Account number is only valid for Blance sheet items i.e 0000 - 9999, and the P&L numbers contain all three of the above for example 4000-100-900, however as you can see from sheet they are located in Column A,C & E.
Period number (Column G) is 0-12 based on the month in question and Balance TM (Column H) is the value im after, i have created a pivott based on this information but it annoyingly (in my suitation) groups the Account numbers together and wont repeat the number, so i cant see how pivott table can assist.
I am looking to create monthly accounts based on the Account Number : Account Cost Centre: Account Department bening matched and then referenced to period number the corresponding entry in Row H is produced.
For example i will in my result sheet have 4000-100-900 in Cell A1 in Cell B1 will be a formula that will look at A1 and break it down into 4000 & 100 & 900 matching it where it appears vertically in Raw Data Sheet in columns A C & E (all three plus period nymber must match) and then give the number in Blance TM column.
Cell A2000 = 4000-405-800
Cell B2000 = 265
Can somebody advise on how to tackle this without using macros?
Cheers,
Paddyboy
Bookmarks