Bringing together whole rafts of info...

Hi guys - hope you can help me here. I'm moderately proficient at Excel but this has stumped me so far...

I've just been posted to a new team in work who deal with sections of the accounts, namely method of receipts of payments. The main spreadsheet is organised as follows:

- two seperate strands of the business each have their own sheet
- the two sheets are designed in the same format
- across the top we have column headers relating to time periods (more to follow), and then the different streams of business. The time periods consist of seperate columns for financial year, month, and quarter
- for each month of the year, a 'template' is copied and pasted into the spreadsheet from another worksheet, containing all the entry items, eg Direct Debit, BACS etc. These are pasted in the first blank row available. Then the data from invoices etc is entered.
- there is a unit charge and total cost at the end of the row

Now this copy and paste has evolved over the 3 years this sheet has been in operation. It was originally 6 rows big, at one point was as large as 12, is currently 9. The variations come from changes in the way payments are handled.

Anyway, this system works and so they've all resolved to keep it as the inputters are heavily resistant to change

They've asked me to develop a sort of reporting spreadsheet that will allow them to track YTD performance against budget, previous years etc. Previously, the way they did this was to use filters to get down to the info they needed, and then create a new spreadsheet from that - there are hundreds of reports on the shared drive created in this way.

What I've set up so far is a series of tabs each relating to a financial year (Apr-Mar). Now I want to extract all this info from the 2 worksheets. I've set up a model template for each year, which contains all the items that exist, for every month.

Now obviously I could manually go through and extract this info, but I want to build a system robust enough to handle what happens in the future also, i.e. future months' entries. The problem is, I don't know where these entries will be as the size of the pasted element changes.

So far, I've been playing around with array entering the formula using SUM, IF and AND. I had some success in 'single row' entry, but once I try to get it working for an array, I hit problems.

Let me try to explain what I have tried to do.

Column A contains the names of the individual transactions. Columns B-D contain the data Month, Qtr and Financial Year. E-J contain the seperate accounts. Rows 11-82 contain all the transaction types (12 sets of identical data).

In my new sheet, in E11, I tried the following:

=Sum(If ( And ( A11:A82 = a11:a82, B11:B82 = b11:b82, C11:C82 = c11:c82, D11:D82 = d11:d82), E11:E82,0))

This is array entered into e11:e82. (For ease of demonstration, I've used capital letters to refer to cells in the original data entry sheet, and small letters for my own consolidation sheet)

I thought this would have the following effect - it would check whether the values in the data entry sheet match those in the consolidation sheet for each row in columns A:D. If it did, then it would post to that row in column E, whatever is in that row in the original sheet. So for example, if the row in the original sheet was for BACS entries, from April, of Quarter 1, of FY 05-06, then it would copy the value across. If not, it would enter 0.

However this comes up with an error (non-specific error).

So could anybody please suggest an alternative method of doing this.

I realise my explanation might be hard to follow so happy to provide the sheet if required...

Thanks in advance! :-)

Post the workbook, and manually enter a couple of correct results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"The Cardinal" <The.Cardinal.2bg3se_1153737304.5872@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bg3se_1153737304.5872@excelforum-nospam.com...
>
> Hi guys - hope you can help me here. I'm moderately proficient at Excel
> but this has stumped me so far...
>
> I've just been posted to a new team in work who deal with sections of
> the accounts, namely method of receipts of payments. The main
> spreadsheet is organised as follows:
>
> - two seperate strands of the business each have their own sheet
> - the two sheets are designed in the same format
> - across the top we have column headers relating to time periods (more
> to follow), and then the different streams of business. The time
> periods consist of seperate columns for financial year, month, and
> quarter
> - for each month of the year, a 'template' is copied and pasted into
> the spreadsheet from another worksheet, containing all the entry items,
> eg Direct Debit, BACS etc. These are pasted in the first blank row
> available. Then the data from invoices etc is entered.
> - there is a unit charge and total cost at the end of the row
>
> Now this copy and paste has evolved over the 3 years this sheet has
> been in operation. It was originally 6 rows big, at one point was as
> large as 12, is currently 9. The variations come from changes in the
> way payments are handled.
>
> Anyway, this system works and so they've all resolved to keep it as the
> inputters are heavily resistant to change
>
> They've asked me to develop a sort of reporting spreadsheet that will
> allow them to track YTD performance against budget, previous years etc.
> Previously, the way they did this was to use filters to get down to the
> info they needed, and then create a new spreadsheet from that - there
> are hundreds of reports on the shared drive created in this way.
>
> What I've set up so far is a series of tabs each relating to a
> financial year (Apr-Mar). Now I want to extract all this info from the
> 2 worksheets. I've set up a model template for each year, which
> contains all the items that exist, for every month.
>
> Now obviously I could manually go through and extract this info, but I
> want to build a system robust enough to handle what happens in the
> future also, i.e. future months' entries. The problem is, I don't know
> where these entries will be as the size of the pasted element changes.
>
>
> So far, I've been playing around with array entering the formula using
> SUM, IF and AND. I had some success in 'single row' entry, but once I
> try to get it working for an array, I hit problems.
>
> Let me try to explain what I have tried to do.
>
> Column A contains the names of the individual transactions. Columns B-D
> contain the data Month, Qtr and Financial Year. E-J contain the seperate
> accounts. Rows 11-82 contain all the transaction types (12 sets of
> identical data).
>
> In my new sheet, in E11, I tried the following:
>
> =Sum(If ( And ( A11:A82 = a11:a82, B11:B82 = b11:b82, C11:C82 =
> c11:c82, D11:D82 = d11:d82), E11:E82,0))
>
> This is array entered into e11:e82. (For ease of demonstration, I've
> used capital letters to refer to cells in the original data entry
> sheet, and small letters for my own consolidation sheet)
>
> I thought this would have the following effect - it would check whether
> the values in the data entry sheet match those in the consolidation
> sheet for each row in columns A:D. If it did, then it would post to
> that row in column E, whatever is in that row in the original sheet. So
> for example, if the row in the original sheet was for BACS entries, from
> April, of Quarter 1, of FY 05-06, then it would copy the value across.
> If not, it would enter 0.
>
> However this comes up with an error (non-specific error).
>
> So could anybody please suggest an alternative method of doing this.
>
> I realise my explanation might be hard to follow so happy to provide
> the sheet if required...
>
> Thanks in advance! :-)
>
>
> --
> The Cardinal
> ------------------------------------------------------------------------
> The Cardinal's Profile:
http://www.excelforum.com/member.php...o&userid=36690
> View this thread: http://www.excelforum.com/showthread...hreadid=564225
>

Bob, Thanks for looking at this for me...

Ok I tried to upload one of the sheets but it's too big (187KB). Can I email it to you instead?

Anyway, just in case the upload did work, firstly the Macros aren't necessary for this sheet so no problem in disabling them. On sheets Stream 1 and Stream 2 is the actual live data for the last few years. The remaining sheets (FY2005-06 and FY 2006-07) are the sheets I've created to bring this data together. From there, I'm going to export it all to another management sheet, but that's another issue! :-)

Ok, for FY 2005-06, row 7, I've copied and pasted the info from Stream 1 and Stream 2, BACS Input (Credit), for April 05, Qtr1 FY2005-06 as you can see. You'll notice that for stream 1, there are 6 rows, and stream 2, only 2 for this month. Stream 1 becomes 7 later on, and stream 2 becomes 3 later on, which is part of my problem. You'll see that each month for FY2005-06 has room for the 7 entries, even though for most of the year, only 5 are needed. (This is simpler than the other sheet I mentioned where the number of required rows for each month varies from 6-12). I don't mind 0 values/blanks because this sheet is only being used to generate data for another sheet.

So hopefully this should make it clearer what my problem is? When I put FY2007-08 in, I'll simply have no idea which rows that info will occupy, so I'll need to match Transaction Type, Month, Quarter and Financial Year from the potential 6000 rows that exist there.

All help gratefully received...

MR

No, it didn't make it. Can you zip it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"The Cardinal" <The.Cardinal.2bhq4d_1153812903.2826@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bhq4d_1153812903.2826@excelforum-nospam.com...
>
> Bob, Thanks for looking at this for me...
>
> Ok I tried to upload one of the sheets but it's too big (187KB). Can I
> email it to you instead?
>
> Anyway, just in case the upload did work, firstly the Macros aren't
> necessary for this sheet so no problem in disabling them. On sheets
> Stream 1 and Stream 2 is the actual live data for the last few years.
> The remaining sheets (FY2005-06 and FY 2006-07) are the sheets I've
> created to bring this data together. From there, I'm going to export it
> all to another management sheet, but that's another issue! :-)
>
> Ok, for FY 2005-06, row 7, I've copied and pasted the info from Stream
> 1 and Stream 2, BACS Input (Credit), for April 05, Qtr1 FY2005-06 as
> you can see. You'll notice that for stream 1, there are 6 rows, and
> stream 2, only 2 for this month. Stream 1 becomes 7 later on, and
> stream 2 becomes 3 later on, which is part of my problem. You'll see
> that each month for FY2005-06 has room for the 7 entries, even though
> for most of the year, only 5 are needed. (This is simpler than the
> other sheet I mentioned where the number of required rows for each
> month varies from 6-12). I don't mind 0 values/blanks because this
> sheet is only being used to generate data for another sheet.
>
> So hopefully this should make it clearer what my problem is? When I put
> FY2007-08 in, I'll simply have no idea which rows that info will occupy,
> so I'll need to match Transaction Type, Month, Quarter and Financial
> Year from the potential 6000 rows that exist there.
>
> All help gratefully received...
>
> MR
>
>
> --
> The Cardinal
> ------------------------------------------------------------------------
> The Cardinal's Profile:
http://www.excelforum.com/member.php...o&userid=36690
> View this thread: http://www.excelforum.com/showthread...hreadid=564225
>

Good idea - attached (assuming I worked WinZip correctly!)

Looking at the data, you seem to have misaligned the data (part of your
problem that you want to solve I guess), as you have aligned the BACS Output
(Debit) for Stream 1 against BACS Input (Credit).

What I am not yet clear on is whether categories per month is fixed or
variable. If variable, I guess that the easiest way will be VBA, formulae
would be horrendous. Can the FY2005-06 data be rebuilt each month? If so,
how would you want to trigger the rebuild?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"The Cardinal" <The.Cardinal.2bhs7c_1153815603.2534@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bhs7c_1153815603.2534@excelforum-nospam.com...
>
> Good idea - attached (assuming I worked WinZip correctly!)
>
>
> +-------------------------------------------------------------------+
> |Filename: BoE.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5088 |
> +-------------------------------------------------------------------+
>
> --
> The Cardinal
> ------------------------------------------------------------------------
> The Cardinal's Profile:
http://www.excelforum.com/member.php...o&userid=36690
> View this thread: http://www.excelforum.com/showthread...hreadid=564225
>

What I meant is to ditch the current year spreadsheet and build it anew each
month. If the year summary holds all categories that could be possible,
formulae could work, otherwise rebuilding is probably better.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"The Cardinal" <The.Cardinal.2bjjmo_1153897803.0663@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bjjmo_1153897803.0663@excelforum-nospam.com...
>
> Bill,
> Thanks for looking again.
>
> The alignment problem you mention is actually just a copy and paste
> error from me due to the freeze panes thing I had on. I've now copied
> the right data and attach again! Sorry! But, it does highlight my point
> that it would be impossible to map where any particular month's BACS
> Input would be, which is why I turned to this forum
>
> I'm not entirely sure what you mean by the rebuild thing. If you're
> thinking the same as me (that this could be done so much easier another
> way) then you're absolutely right... however resistance to change here
> is very strong, and they'd just as much prefer to be doing it with pen
> and paper than this new fangled PC thing!
>
> So anyway, what did you mean by the rebuild?
>
>
> --
> The Cardinal
> ------------------------------------------------------------------------
> The Cardinal's Profile:
http://www.excelforum.com/member.php...o&userid=36690
> View this thread: http://www.excelforum.com/showthread...hreadid=564225
>

It's possible for this sheet with these 2 streams to have every possible category in the year summary. In fact I've toyed with the idea for every month of introducing a 'Miscellaneous' field in the event that new categories do come up (very unlikely).

The 7 you see now per month are the only 7 that are foreseeably possible (which would become 8 if you allow for the miscellaneous option mentioned above..)

Is that good news?

Either way, I'm happy to rebuild the yearly summaries if that will help - so long as the 2 input sheets remain as close to the original as possible, I have pretty much carte blanche to do whatever else I see fit.

I have posted a proposed solution at http://cjoint.com/?hAlQ5ptf4a

I have only done the 2006-06 year, you should be able to apply easily to
another year if it is what you want.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"The Cardinal" <The.Cardinal.2bjo11_1153903503.7416@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bjo11_1153903503.7416@excelforum-nospam.com...
>
> It's possible for this sheet with these 2 streams to have every possible
> category in the year summary. In fact I've toyed with the idea for every
> month of introducing a 'Miscellaneous' field in the event that new
> categories do come up (very unlikely).
>
> The 7 you see now per month are the only 7 that are foreseeably
> possible (which would become 8 if you allow for the miscellaneous
> option mentioned above..)
>
> Is that good news?
>
> Either way, I'm happy to rebuild the yearly summaries if that will help
> - so long as the 2 input sheets remain as close to the original as
> possible, I have pretty much carte blanche to do whatever else I see
> fit.
>
>
> --
> The Cardinal
> ------------------------------------------------------------------------
> The Cardinal's Profile:
http://www.excelforum.com/member.php...o&userid=36690
> View this thread: http://www.excelforum.com/showthread...hreadid=564225
>

Bob that looks absolutely perfect. Could I ask a question or two though?

- I've never seen that function ISNA before - what does it do?
- If you remember the tariff costings bit, because stream 2 has only 2 entries, and stream 1 has the 7, the tariff costs for those in stream 1 but not in 2 don't appear because I think the formula is only checking stream 2. If I change the references in the tariff costs column to Stream 1 instead of 2, will it pick up the tariff costs for all the items? (I only ask because I don't want to mess around with the formula for fear of breaking it!)
-As a novice with array formulae, how do I edit little bits of it? Do I need to select the whole array, and then edit, and ctrl-shift enter at the end again?
- Just out of curiosity, why are some cells 0, and others blank?

I really appreciate your help!
Mike

"The Cardinal" <The.Cardinal.2bjryv_1153908620.4204@excelforum-nospam.com>
wrote in message
news:The.Cardinal.2bjryv_1153908620.4204@excelforum-nospam.com...
>
> Bob that looks absolutely perfect. Could I ask a question or two
> though?
>
> - I've never seen that function ISNA before - what does it do?

ISNA is used to check whether functions like MATCH find a match. If they do
they return a number, which I pass to INDEX to get the value. If they don't
they return #N/A, which is nasty in this case, so I use ISNA to test for no
match, and if no match I output "", else I output the result. I could also
have used ISNUMBER, same effect.

> - If you remember the tariff costings bit, because stream 2 has only 2
> entries, and stream 1 has the 7, the tariff costs for those in stream 1
> but not in 2 don't appear because I think the formula is only checking
> stream 2. If I change the references in the tariff costs column to
> Stream 1 instead of 2, will it pick up the tariff costs for all the
> items? (I only ask because I don't want to mess around with the formula
> for fear of breaking it!)

Excellent point, I should have thought of that.

The solution is to test for a match with one, and then the other

=IF(ISNA(MATCH(1,(Stream1!$A$4:$A$1000='FY2005-06'!$A8)*(Stream1!$B$4:$B$100
0='FY2005-06'!$B8),0)),
IF(ISNA(MATCH(1,(Stream2!$A$4:$A$1000='FY2005-06'!$A8)*(Stream2!$B$4:$B$1000
='FY2005-06'!$B8),0)),"",
INDEX(Stream2!J$4:J$1000,MATCH(1,(Stream2!$A$4:$A$1000='FY2005-06'!$A8)*(Str
eam2!$B$4:$B$1000='FY2005-06'!$B8),0))),
INDEX(Stream1!L$4:L1001,MATCH(1,(Stream1!$A$4:$A$1000='FY2005-06'!$A8)*(Stre
am1!$B$4:$B$1000='FY2005-06'!$B8),0)))

which again is an array formula.

> -As a novice with array formulae, how do I edit little bits of it? Do I
> need to select the whole array, and then edit, and ctrl-shift enter at
> the end again?

You can go into edit mode in the cell, F2, and then just place the cursor
over the bit to edit, and change it. You must do Ctrl-Shift-Enter at then
for array formulae though.

> - Just out of curiosity, why are some cells 0, and others blank?

The blank cells are where I code it to put blabk, as per the #N/A discussion
above.

The zeroes are caused by referring to cells that have no data, either by a
direct reference, or by summing empty cells. AGain, you could test for 0 and
output "", but the big problem with this stuff is knowing when it contained
0 and when it contains "", they are different.

> I really appreciate your help!
> Mike

And now I know the Cardinal's name <g>. I assume that from BoE Charges, that
means you are UK based?

Thanks again Bob - I've put the formula in for the tariff columns and it's working perfectly.
Yes I'm in England and responsible for paying lots of money to the BoE!
And as for the name, well a bit of mystique never hurt anybody!

Thanks again for the help, and no doubt I'll call on you again!