Formula w/ Arrays

**gambinus2** · 05-07-2009, 11:33 AM

I download data and I need it to flow over to a summary tab based on a 4 digit number being contained in a cell.

The first part I need help with is that the date I download (see Download tab column A) is in a General format. Is there an easy way to change this so I can match it up to what is in my column A of my Disb08 tab?

I have the formula down for my REC08 tab, I just need help with the Disb08 formulas.

I need to flow from the Download tab to the Disb08 tab based on the date, that column F of the Download tab = "Outgoing Money Transfer" and that the 4 digit number in in row 2 of the Disb08 tab is matched against Column G in the Download tab. When all those match, then the value from column J of the download tab would flow over.

Please let me know if you need anything clarified.

thank you for your help.

**DonkeyOte** · 05-07-2009, 12:19 PM

I confess I don't really understand as to how you're extracting the A/C ID from Download narrative in Col G - you seem to be taking the rightmost 4 characters from the string but this seems to be an address ?

In terms of taking the Date strings (text) in Column A and converting them to Date Values (numeric) you could use a formula in an adjacent column, eg:

K2: =DATE(RIGHT(A2,4),MONTH(1&LEFT(A2,3)),SUBSTITUTE(MID(A2,5,2),",",""))
copied down

I noticed you subsequently use SUMPRODUCTS on your other sheets, lots of SUMPRODUCTS will lead to poor performance... personally I would be inclined to adapt the above (K) to create a concatenation key such that you can revert to a standard SUMIF formula, eg:

K2:
=DATE(RIGHT(A2,4),MONTH(1&LEFT(A2,3)),SUBSTITUTE(MID(A2,5,2),",",""))&":"&F2&":"&RIGHT(G2,4)
copied down

The above is using the 4 rightmost characters of the string in G as per your initial Sumproduct but I'm not sure this is correct...

Regardless - to elaborate on the non-Sumproduct approach, using the concatenation column created (K) one can revert to the much faster SUMIF approach:

Rec08
D4: =SUMIF(Download!$K:$K,$A4&":ZBA Credit:"&D$2,Download!$J:$J)
copy across matrix

Disb08
D4: =SUMIF(Download!$K:$K,$A4&":Outgoing Money Transfer:"&D$2,Download!$J:$J)
copy across matrix
(as you can see this is the same as Rec08 just with different narrative check)

**gambinus2** · 05-07-2009, 01:59 PM

I am unable to get your SumIf formulas to work.

SUMIF(Download!$B$2:$B$700,$A5&":Outgoing Money Transfer:"&D$2,Download!$J$2:$J$700)

How does the formula know where to search for Outgoing Money Transfer and the last 4 digit number I am matching?

**DonkeyOte** · 05-07-2009, 02:06 PM

The formula you have used does not relate to that offered... as previously detailed at length you need to utilise a Concatenation column in order to use a SUMIF (single condition) approach.