Array Formula for matching data

Hi,
Please help on the below issue as per the enclosed file reference.

I have a data in Sheet1 from A4:L12, I need result in sheet2 from C13:N17 cells.

Logical Condition at cell C13 is
1. Sheet 2 data at C12 month range has to be matched with sheet1, E&F columns.
2. Sheet data at C13 has to be matched with G4:L4

Need the quantity with in those dates with the multiple rows without multiple calculations.

Thank u.

Result in Sheet 2 at C13 will be as a C14 result (already done in long process).

Is that a typo? Do you mean C4?

In 'Sheet1' F11 the year is 2019. All others are 2018. May we assume that is a typo as well?

Never mind. I reread your post #3.

Try this in C13:N17 of 'Sheet2'

Formula:

Please Login or Register  to view this content.

I though OP want to remove helper from row 14 and from column N sheet 1

I must admit I did not pick up on that.

And now I still don't see that helper. I thought maybe I had inadvertently pasted something over it. I re-uploaded and still don't see it.

In sheet 1, from column N, count days of each month within start and finish date

From row 14, i.e G14, which equals 4.92 =G7*N7/D7+G9*N9/D9

Both are helper tables those OP want to be rejected.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

roven.aravind
For what it's worth you can shorten up the formula in N7:Y12 with this. It returns the same results.

Formula:

Please Login or Register  to view this content.

Above formula is not working. I want alternate formula for direct calculation other than what I did.

I am confused. It is working at my end. Are you referring to the formula in post #10 or #5 ... ?

How is it not working? What results are you getting?

Can you upload a file that demonstrates this?

In the meantime try this in C13:N17 of 'Sheet2'.

Formula:

Please Login or Register  to view this content.

Array enter this in N29 fill across and down. This eliminates need for the N7:Y12 range.

Formula:

Please Login or Register  to view this content.

	M	N	O	P	Q	R	S	T	U	V	W	X	Y
26		01/Jan/18	01/Feb/18	01/Mar/18	01/Apr/18	01/May/18	01/Jun/18	01/Jul/18	01/Aug/18	01/Sep/18	01/Oct/18	01/Nov/18	01/Dec/18
27	Single Formula requested	31/Jan/18	28/Feb/18	31/Mar/18	30/Apr/18	31/May/18	30/Jun/18	31/Jul/18	31/Aug/18	30/Sep/18	31/Oct/18	30/Nov/18	31/Dec/18
28		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
29	E_CHND	5	11	3	3	4	5	4	4	2	1	1	1
30	E_MASN	25	18	4	3	12	12	11	11	9	16	17	17
31	E_SCAR	14	18	0	0	9	10	10	10	9	7	6	6
32	E_SFXR	13	11	2	2	12	27	30	30	16	8	6	6
33	E_HELP	53	123	29	28	46	48	38	38	23	8	4	4

Originally Posted by roven.aravind

If you can explain the details of this formula can be very useful to me.
I am trying to understand the same but its very difficult.
Difficult in transpose row indirect.

And if the data in E & F columns are empty it directs #DIV/0!
How can we ignore those rows if the start date and end date will be blank.

Thank u.

Glad to hear it works. Thank you for the feedback and added rep.

RE: Explaining the details. I will do my best. It will take me some time to gather and organize my thoughts ... coherently.

RE: Blank start and end dates/#DIV/0!. Force the divisor to ones where D7:D12 = 0. Replace the above with this formula. In the event of blank dates it will return 0s. I suspect the (Sheet1!$D$7:$D$12>0) in the numerator is superfluous. It doesn't do harm so I left it.

Formula:

Please Login or Register  to view this content.

Dear Sir,
Please help on the another issue in the same formula if "Quantity" column to be added.
Quantity column "G5:G10" has been divided with the matched data from the range "H5:N10" related to the dates range.
i.e. (G5:G10)/(H5:N10)
Dates comparison is same as earlier formula.

I had tried on the above issue please correct the division errors.

Formula at B15 cell:

=SUM(((Sheet2!$D$5:$D$10>0)*$G$5:$G$10/INDEX(Sheet2!$H$5:$N$10,,MATCH($A15,Sheet2!$H$2:$N$2,0))*((TRANSPOSE(ROW(INDIRECT(B$12&":"&B$13)))<=Sheet2!$F$5:$F$10)*(TRANSPOSE(ROW(INDIRECT(B$12&":"&B$13)))>=Sheet2!$E$5:$E$10)))/IF(Sheet2!$D$5:$D$10=0,1,Sheet2!$D$5:$D$10))

Thank u.

Try

in B15

=SUM(((Sheet2!$D$5:$D$10>0)*($G$5:$G$10)*(IF(INDEX(Sheet2!$H$5:$N$10,,MATCH($A15,Sheet2!$H$2:$N$2,0))>0,1/INDEX(Sheet2!$H$5:$N$10,,MATCH($A15,Sheet2!$H$2:$N$2,0)),0))*((TRANSPOSE(ROW(INDIRECT(B$12&":"&B$13)))<=
Sheet2!$F$5:$F$10)*(TRANSPOSE(ROW(INDIRECT(B$12&":"&B$13)))>=Sheet2!$E$5:$E$10)))/IF(Sheet2!$D$5:$D$10=0,1,Sheet2!$D$5:$D$10))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Originally Posted by roven.aravind

Hi Sir,
It is working and thank you for your guidance.
And I will wait for your reply on the detailing.

I'll do my best and try to keep it sufficiently brief.

If you are not already acquainted with them there are two devices that help break formulas down. These are the F9 function key and Evaluate formula feature in the FORMULA ribbon (Fx).

In the formula bar you can select portions of the formula and press the F9 function key. This will reveal the output of that segment. Bear in mind these outputs are out of context.

To see the same step by step outputs in context select the cell in question, activate Fx. Click the Evaluate button repeatedly. Excel will reveal step by step how it resolves the formula.
Unfortunately the view port is painfully small and scrolling is not precise. The remedy for that is to make a smaller (smaller arrays/ranges) version of the formula in question (shrink the problem) and then apply Evaluate formula.

Reference the upload Post #13.

Make N5 the active cell.
Select just the (N$26&":"&N$27) part of the formula and press F9. You will see a text string of a number range "43101:43131".
If you are not aware of it dates are numbers which are the number of days passed since Jan 1, 1900. (Today is 1/21/2018 or day 43,121.)
The formatting is cosmetic. The underlying values are the numbers.

This text string is passed to the ROW function which will return an array of those numbers. In this case it is returning an array of contiguous dates from 1/1/2018 to 1/31/2018.
Select just ROW(INDIRECT(N$26&":"&N$27)) in the formula bar, press F9 and see this array.

{43101;43102;43103;43104;43105;43106;43107;43108;43109;43110;43111;43112;43113;43114;43115;43116;43117;43118;43119;
43120;43121;43122;43123;43124;43125;43126;43127;43128;43129;43130;43131}

Note the ";" separators in that array. If you are not aware of it Excel "sees" those as row separators. Since SUM, SUMPRODUCT and many other functions require the size of ranges and arrays (number of rows) be the same there is a dilemma.
There are 31 rows to compare and multiply with 6 rows in all the remaining data. To make matters even more challenging the date arrays will be variable.

This is where TRANSPOSE comes into play.
Select TRANSPOSE(ROW(INDIRECT(N$26&":"&N$27)))and press F9. You will see the same array of numbers

{43101,43102,43103,43104,43105,43106,43107,43108,43109,43110,43111,43112,43113,43114,43115,43116,43117,43118,43119,
43120,43121,43122,43123,43124,43125,43126,43127,43128,43129,43130,43131}

but note the "," separators. Excel "sees" these as column separators. We can now compare each of the 31 dates above with each date in columns E:F.

The effect of multiplying the now intersecting TRUEs/FALSEs is a 6 row by 31 column array of 1s/0s.

Select ((TRANSPOSE(ROW(INDIRECT(N$26&":"&N$27))) < =
Sheet1!$F$7:$F$12)*(TRANSPOSE(ROW(INDIRECT(N$26&":"&N$27)))>=Sheet1!$E$7:$E$12)) and press F9 to see this.

When TRANSPOSE is part of an array formula it will nearly always require Ctrl + Shift + Enter. (In fact I have only seen one array formula where it didn't.)

Having said all that:
F9 and Fx are excellent tools for analyzing, trouble-shooting and self instruction. I recommend them whenever I can. Always bear in mind that F9 presents returns out of context. You will need Fx to see the whole picture.

I hope this helps.

You are welcome. Glad to help. Thank you for the feedback.