Formula to populate data based on date

Greetings Everyone!

Request for some assistance with a massive task I’ve been pushed against. I’ve enclosed a spreadsheet depicting the layout in which my data is available. I need some assistance to do the following using the Base Date in Column B.

Here’s the explanation:

Populate the data in columns C to H by row

Step 1: Logic for Column C:

Compare the Base Date in Column B with the Date in the 3rd cell (Labeled D) under each of the categories (Colum I onwards)

Add all the values in the 4th column (labeled A) under each category and populate in Column C cell provided the date in the 3rd Column of each category is less than or equal to the base date

Step 2: Populate data for the category whose Date in the 3rd column of each category is the closest to the base date, but does not exceed the base date. In example 1:

Base Date = 2/24/2010

Closest Date = 4/20/2007. As a result, the data in D to G columns have been populated from Category 5

Last step: Column H is the sum from column A of all categories for which the category dates exceed the Base Date

Have enclosed a sample file with the expected output in column C to H. I would be obliged if you could help me with a formula or something that can do this magic for over 2000 rows of data that I have.

Please let me know if anything is unclear or need further explanation.

Best,
Spi

Try array-entering this formula in D3 and filling down to G4.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Originally Posted by FlameRetired

Try array-entering this formula in D3 and filling down to G4.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Thanks so much. This works perfect from my preliminary tests. Could you please also assist me with populating the data in column C and H repectively please...


Thanks again

Originally Posted by spiwere

................... Could you please also assist me with populating the data in column C and H repectively please...

I'm considering several strategies. For one of them I would need to know the largest number you would anticipate in the "A" sections.

Originally Posted by FlameRetired

Try array-entering this formula in D3 and filling down to G4.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

On my real data. I'm getting all #NA is there something I'm doing incorrectley? Please advise.

Please ignore this one. I figured out the reason. It was to do with my header. Sorry.

But could you please help me with updating data in column C and H


Kindest regards,

Spi

Originally Posted by FlameRetired

I'm considering several strategies. For one of them I would need to know the largest number you would anticipate in the "A" sections.

The largest number in section A would be a tricky one this is the revenue amount and could be in the tune of billions. Does this help?

Ouch! Does your present data reflect this? I.e. is 14.2 an expression of 14.2 billion?

Originally Posted by FlameRetired

Ouch! Does your present data reflect this? I.e. is 14.2 an expression of 14.2 billion?

Not necessarily. In the example it is 14.2 million, but yes there could be a case where it is in billions too.

Also, while I applied the formula in D to G columns, it works well, the only tweak that I foresee that is required is like this:

2 categories let's say 4 and 5 have the same date in the 3rd column. In this case the formula is updating the details for category 4 only. Would it be possible to update it such that the details in D to G get updated in a manner where the data for both categories in each cell is updated (D to G) seprated by commas. Please see the attached for example.

Thank you!

In the meantime this seems to work so far for totals in column C regardless of amounts or duplicates. Array-entered again.

Formula:

Please Login or Register  to view this content.

On concatenating as the latest upload shows ... the only way I know to do that is with VBA ... a User Defined Function. Unfortunately I do not know how to apply it selectively to the formula I posted in post #2. I will try to get back to this if I can.

I'm presently still trying to solve for column H.

Not necessarily. In the example it is 14.2 million, but yes there could be a case where it is in billions too.

So will these millions or billions always be expressed in these smaller actual numbers? The reason I ask is that dates are numbers, too, and numbers like 42313 are the underlying value of 11/5/2015. The formatting is cosmetic, but the size range of the actual values in "A" section could foil my plans. So far I've been able to filter those date values out because they are "safely" large ... but .....? Asked another way will any of those numbers in section "A" be expressed the range of say 35000 to 50000?

Originally Posted by FlameRetired

I

I'm presently still trying to solve for column H. So will these millions or billions always be expressed in these smaller actual numbers?

Yes, the intent is to show them in smaller actual numbers.

Thanks....

Super. In the meantime how does that summing formula for column C work on your live data?

Originally Posted by FlameRetired

Super. In the meantime how does that summing formula for column C work on your live data?

Perfectly is too small a word to define it

Good. Try this in column H. Array-entered.

Formula:

Please Login or Register  to view this content.

I chose an arbitrarily small enough number (30000 equal to 2/18/1982) to filter out dates in this array and large enough not to filter out any amounts.

.........2 categories let's say 4 and 5 have the same date in the 3rd column. In this case the formula is updating the details for category 4 only. Would it be possible to update it such that the details in D to G get updated in a manner where the data for both categories in each cell is updated (D to G) seprated by commas. Please see the attached for example.

So far all my efforts are going nowhere. If I am able to come up with anything it will probably take me awhile to figure this part out.

Edit I'm stumped. I'm going to see if I can get us some help on this one.

Originally Posted by FlameRetired

So far all my efforts are going nowhere. If I am able to come up with anything it will probably take me awhile to figure this part out.

Edit I'm stumped. I'm going to see if I can get us some help on this one.

Dear FlameRetired,

Please don't worry about the ask to concatenate data. I figured it out manually as there were not very many cases.

By the way, did someone ever tell you that you are a magician? Your solution is magical. Thanks a zillion times for saving tones and tones of my time. I'm great-full.

Thanks again closing this as solved

Best regards,
Spi

You're welcome. Thanks for the feedback and the rep.

Originally Posted by FlameRetired

You're welcome. Thanks for the feedback and the rep.

Pleasure is all mine. Thanks for your help!