1

EDIT: SOLUTION BELOW

An example will make it easier to understand. The idea is very simple, at least so it seems.

With the application of dynamic lookup values I want to match values of one column to values of another and list the individual results. If due to the dynamic range of the lookup value space is limited I can 'sum' the output in one cell in case of several matches.

It is also possible to add the desired amount of rows instead to accommodate for any possible additional matches, but I haven't been able to figure out a formula to accomplish that yet.

All columns, data and hence lookup values are dynamic.

Problem areas Highlighted:

Problem areas Highlighted

The following formulas I have right now (drag down to cells below):

Column E

{=IF(F7=0,"",IFERROR(INDEX($B:$B,MATCH($F7,$C:$C,0),),0))}

Column F

{=IFERROR(INDEX($C$1:$C$26,SMALL(IF($C$1:$C$26>0,ROW($C$1:$C$26)),ROW(2:2))),0)}

Column G

{=IFERROR(IF(E7<>E6,IF(E7<>E8,SUMIF($B$7:$B$26,E7,$D$7:$D$26),IF(COUNTIFS($B$7:$B$26,E7,$D$7:$D$26,">0")>2,SUMIF($B$7:$B$26,E7,$D$7:$D$26),INDEX($D$7:$D$26,MATCH(0,IF($E7=$B$7:$B$26,COUNTIF($J$8:$J9,$D$7:$D$26),""),0)))),IF(COUNTIFS($B$7:$B$26,AQ7,$D$7:$D$26,">0")>2,SUMIF($B$7:$B$26,AQ7,$D$7:$D$26),INDEX($D$7:$D$26,MATCH(0,IF($AQ7=$B$7:$B$26,COUNTIF($AU$5:$AU7,$D$7:$D$26),""),0)))),"")}

EDIT: FOUND ACCEPTABLE SOLUTION

So I managed to accomplish what I wanted in a fairly easy way with additional 'helper' columns. I decided to accept that the budget column shall only show the SUM of all budget entries on a given day. Then in order to add one empty row to make space for a date on which there was a budget entry, but on which no purchase has been made ('receipt'), without disturbing the flowing dynamic range of both the date and receipt column, I used the following setup with reference to the example screenshot:

Condition: A receipt entry never shares the same row as a budget entry

1) Helper column #1 replaces each budget entry in column D with a unique value that can be used for lookup.

2) Helper column #2 each cell displays its row number.

3) Helper column #3 copies the exact matching dates to all receipt values of column C on their respective row numbers.

4) Helper column #4 does the same as above with the difference that all blanks are replaced by the dates on which budget entries have been made.

5) Helper column #5 combines helper column #3 and #4 by only adding unique budget dates in the blank spaces that are not present in column #3

6) DESIRED OUTPUT for the Date column (column I) is formed by removing all blank spaces from helper column #5

7) DESIRED OUTPUT for the Receipt column (column J) is formed by following the same procedure with helper columns in order to add the desired extra row. This is achieved by adding another helper column #6 replacing the date that is added on a row with a blank cell in helper column #4 with a zero value. This way a distinction is made between the added "zero value" cell and the other blank cells. By removing all blank cells in the desired output column J the zero value will create the extra desired row without disturbing the receipt entries and their corresponding dates.

8) DESIRED OUTPUT for the Budget column (column K), now, simply shows the sum of all budget entries on a specific date by adding this value on the first row of each new instance of a corresponding date in column I.

See all formulae below:

All formulae below are put on the first line of each respective column that can be copied down. For this example I use column AA for helper column #1, AB for #2, and so on:

Helper column #1 - AA

=if(d7>0,AB7,"")

Helper column #2 - AB

=ROW()

Helper column #3 - AC

=IF(OR(ISBLANK(C7),C7=0),"",INDEX($B:$B,MATCH($C7,$C:$C,0),))

Helper column #4 - AD

=IF(ISBLANK(AA7),"",INDEX($B:$B,MATCH($AA7,$AA:$AA,0),))

Helper column #5 - AE

=IF(AC7="",IF(COUNTIF($AC:$AC,AA7)=0,AA7,""),AC7)

Helper column #6 - AF

=IF(C7="",IF(COUNTIF($AC:$AC,AA7)=0,0,""),C7)

DESIRED OUTPUT COLUMN I

{=IFERROR(INDEX($AE$1:$AE$100,SMALL(IF($AE$1:$AE$100<>"",ROW($AE$1:$AE$100)),ROW(1:1))),0)}

DESIRED OUTPUT COLUMN J

{=IFERROR(INDEX($AF$1:$AF$100,SMALL(IF($AF$1:$AF$100<>"",ROW($AF$1:$AF$100)),ROW(1:1))),0)}

DESIRED OUTPUT COLUMN K

=IFERROR(IF(I9<>I8,SUMIF($B$7:$B$100,I9,$D$7:$D$100),""),"")

Not sure if this, by adding 6 (!) additional helper columns, is the most efficient way, but it does the job. It would be great to have an array formula combine all of this, but I don't know if that would be possible. Any working suggestions for better alternatives are welcome.

  • Seems you want to Extract Date from Column B along with Receipt numbers from Column C and the Budget but ALL BUDGET values don't have Receipts in Column D?? In Desire Output area JAN 2 has no Receipt but accessing the Budget, in fact total 550, instead of 500 !! – Rajesh S Feb 18 '18 at 7:40
  • The reason it says 550 instead of 500 is because I can't fit both 500 and 50 (both budget entries on Jan 2) in one cell. So I decided to simply sum the two up by lack of other solution, which would be to add a row with Jan 2 (or any other date for which there are no 'sufficient' receipts given in relation to the amount of budget entries) to accommodate for the extra budget entry on the same day only one receipt was made. – PepeerLiber Feb 18 '18 at 8:21
  • Basically that would solve the problem, if I could just add an additional row automatically, just in case there are more budget entries than receipt entries, in the 'desired output' field, I assume by somehow adjusting the array formula? The second 'acceptable' solution would be to place the values of (in this case) D17 and D18 in respectively cells K11 and K12, since there is just enough space (2 receipt entries that day and 2 budget entries). This however leaves me with the problem of adding 1 row with the date and budget entry of B25:D25 into I15:K15, for there is no receipt entry that day – PepeerLiber Feb 18 '18 at 8:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.