User defined function of filter() to change output range

%3CLINGO-SUB%20id%3D%22lingo-sub-1504784%22%20slang%3D%22en-US%22%3EUser%20defined%20function%20of%20filter()%20to%20change%20output%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504784%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20overall%20aim%20is%20to%20create%20a%20UDF%20that%20results%20in%20the%20output%20of%20my%20filter()%20function%20to%20be%20in%20the%20same%20column%20only%20i.e.%20vertically%20only.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20sheet%26nbsp%3B%3CEM%3ESheetID02%3C%2FEM%3E%20I%20hardcode%20when%20a%20'debt'%20is%20issued.%20In%20%3CEM%3ESheet1%26nbsp%3B%3C%2FEM%3Eall%20payments%20and%20date%20of%20payments%20are%20found.%20In%26nbsp%3B%3CEM%3ESheetID02%3C%2FEM%3E%20I%20am%20currently%20using%20the%20filter()%20function.%20Thereby%2C%20I%20can%20retrieve%20the%20amount%20repaid%20and%20the%20date%20of%20repayment%20in%20the%20correct%20date%20column%20by%20filtering%20for%20payments%20between%20specific%20dates.%20As%20you%20can%20see%20below%2C%20the%20filter%20function%20works%20perfectly%20for%26nbsp%3B%3CU%3Eone%3C%2FU%3E%20payment%20made%20in%20February%20(2017%2F02).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture_1.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203387i649D607F5B4CCD42%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Capture_1.PNG%22%20alt%3D%22Capture_1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20there%20have%20been%20made%20two%20payments%20in%20March%20(2017%2F03)%20the%20output%20of%20the%20filter()%20function%20clashes%20with%20the%20next%20column%20making%20it%20appear%20as%20if%20the%20second%20payment%20in%20March%20was%20made%20in%20February.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture_2.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203391i92B76CDAEDCA946F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Capture_2.PNG%22%20alt%3D%22Capture_2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20create%20a%20user%20defined%20function%20that%20ensures%20that%20the%20function%20in%20cell%20U12%20only%20outputs%20in%20column%20U.%20Ideally%2C%20what%20is%20found%20in%20cell%20V12%20and%20V13%20in%20the%20picture%20above%20should%20instead%20appear%20in%20U14%20and%20U15.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20as%20to%20how%20this%20can%20be%20approached%20is%20much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1504784%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hi all,

 

My overall aim is to create a UDF that results in the output of my filter() function to be in the same column only i.e. vertically only.

 

In sheet SheetID02 I hardcode when a 'debt' is issued. In Sheet1 all payments and date of payments are found. In SheetID02 I am currently using the filter() function. Thereby, I can retrieve the amount repaid and the date of repayment in the correct date column by filtering for payments between specific dates. As you can see below, the filter function works perfectly for one payment made in February (2017/02).

 

Capture_1.PNG

 

However, if there have been made two payments in March (2017/03) the output of the filter() function clashes with the next column making it appear as if the second payment in March was made in February.

Capture_2.PNG

I am looking to create a user defined function that ensures that the function in cell U12 only outputs in column U. Ideally, what is found in cell V12 and V13 in the picture above should instead appear in U14 and U15.

 

Any ideas as to how this can be approached is much appreciated! If relevant, see attached file

0 Replies