Two Variable Matching

[Mark V.]

Hello all. I'm trying to transfer cash flow data from one spreadsheet onto another sheet. My purpose in doing this is to display my data in a more standard, more accessible format.

Currently, my data looks like this:

-----A-------------------------D---------------------------E
Community #1________Date of Cash Flow Usage_______Cash Flow Amount
Community #1________Date of Cash Flow Usage_______Cash Flow Amount
Community #1________Date of Cash Flow Usage_______Cash Flow Amount

Community #2_______Date of Cash Flow Usage_______Cash Flow Amount
Community #2_______Date of Cash Flow Usage_______Cash Flow Amount
Community #2_______Date of Cash Flow Usage_______Cash Flow Amount

Community #3_______Date of Cash Flow Usage_______Cash Flow Amount
Community #3_______Date of Cash Flow Usage_______Cash Flow Amount
Community #3_______Date of Cash Flow Usage_______Cash Flow Amount

//There is no uniform number of cash flows/community
//Dates are currently listed ascending community by community (oldest usage-newest usage)
//Data runs down 5700 rows
//Not all dates have their cash flows listed (most do)
//Yes, there are spaces between every separate community

How I would like the data to look

___________________________________________9/2/89______9/3/89______9/4/89 ----------- 6/6/13
Community #1_______________________________all cash flows from Community #1 over time
Community #2_______________________________all cash flows from Community #2 over time
Community #3_______________________________all cash flows from Community #3 over time

Essentially, I would like to stretch a single function from the 9/2/89 to 6/6/13 (8678 Columns) down through every community (275 Rows).
From my very limited knowledge of Excel, my best guess right now is that an INDEX( ,MATCH(),MATCH()) function should be used, but I can't for the life of me make it work. If anyone can help me with this request, I'd be a very happy person.

[Pete_UK]

I think you could use a SUMIF formula for this, but it is difficult to tell from your (largely text) description. Any chance you could attach a sample workbook? The FAQ describes how to.

Pete

[Mark V.]

Ok, I attached the necessary portion of the data. Don't worry, I scrambled the important bits.

[Pete_UK]

Do you really need to have your dates going back to 1989, and then listed for every day?

You can put this formula in I3:

=SUMIFS('Old Datasheet'!$C:$C,'Old Datasheet'!$A:$A,$A3,'Old Datasheet'!$B:$B,I$2)

then copy across and down as required.

Hope this helps.

Pete

[Mark V.]

Thanks for the quick response Pete,

I may just not know how to copy and paste a formula, but right now =SUMIFS('Old Datasheet'!$C:$C,'Old Datasheet'!$A:$A,$A3,'Old Datasheet'!$B:$B,I$2) is only returning 0s accross the board. (I applied it over the first 4 years)

The purpose of listing the dates is to produce the correct XIRR which requires that the cash flows be matched with the proper dates. Truthfully, I don't like having every date listed accross all columns. I set it up that way to enable the use of the index, match, match function I mentioned earlier.

[Mark V.]

In regards to the index double match formula, my thinking came from this video: Excel Magic Trick 781: Three Way Lookup: INDEX and MATCH

http://www.youtube.com/watch?v=zjfpA2kJhm8

I'm having trouble translating it into my project because mine is only a two way lookup.

[Pete_UK]

In row 13 of your New_datasheet you have the community code 2001.1, which ties in with rows 1999 to 2023 in the Old_datasheet. Most of the dates are in 2001, and that year's dates start in column FCN of the New sheet. So, to check that the formula is working, just copy it from cell I3 and paste it into a group of cells from FCN13 across row 13. The first non-zero value occurs in cell FDG13, which corresponds to 20th January. The next value is in May. Check back to the Old sheet and you can see those entries. So, with such a big span of dates, your table is going to be largely empty.

Hope this helps.

Pete

[Pete_UK]

I've not looked at the video, but you could use an INDEX/MATCH combination to retrieve the values instead of SUMIFS. The problem is that if you have two or more transactions on one day for a particular account, INDEX/MATCH/MATCH will only return the first of those, whereas SUMIFS will add them all together.

I haven't a clue what you are trying to do, but such a massive table is going to slow your workbook down considerably. Try to describe what it is that you want to achieve ultimately.

Hope this helps.

Pete

[Mark V.]

Sorry, I got carried away and tried to apply the formula to all 2 million cells and my comp crashed- imagine that Anyway, I'm going to give it another, more controlled try.

As for the dates, I plan to remove or at least hide the empty dates as soon as the table has played its role.

[Mark V.]

Also, is there a better way to apply formulas than just clicking and dragging?

[Pete_UK]

Instead of listing daily dates, couldn't you do them on a weekly or monthly basis? That will cut down the size of the table considerably.

Pete

[Mark V.]

Well, the purpose of doing the dates daily is so that I can solve for the XIRR later. I do plan to remove or at least hide the empty dates as soon as the table has played its role. I'm sure there is a more efficient way... I'm just not sure what will still produce the correct results.

[Pete_UK]

I don't know how I can help you further. I've given you a formula to transfer the data into your massive table - does that mean that the thread is solved? Please mark it as such if you think so (click on Thread Tools above your first post).

Pete

[Mark V.]

Hmm, so I applied your function to all of row 13 (community 2001.1) and checked the values. Right now, the cash flows and the dates on row 13 correlate to community 1162400.1000 instead of 2001.1.

[Mark V.]

Sorry for being a bugger, I appreciate your patience and continued help.

[Mark V.]

Oh, I am an idiot. This is what i get for forgeting to change the formula when i went down rows. Yes, the formula works perfectly even if I don't. Thank you so much Pete; this is perfect.

[Pete_UK]

This is the formula that should be in cell I13:

=SUMIFS('Old Datasheet'!$C:$C,'Old Datasheet'!$A:$A,$A13,'Old Datasheet'!$B:$B,I$2)

Note the emphasised part. Then you can copy it across that row.

Hope this helps.

Pete

[Mark V.]

Got it Thanks again Pete, you have no idea how happy I am not to have to do this manually.