• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List Accounts Receivable for Period

deciog

Active Member
vletm, Good Morning.

Thanks for the quick, interesting answer, but I need it with formulas.

Hugs

Decio
 

deciog

Active Member
vletm

I do not master pivot table, so the interesting

I liked it but I don't know how to do it in the original spreadsheet

Decio
 

John Jairo V

Well-Known Member
Hi, @deciog !

You could try this formula:

=IFERROR(INDEX(Planilha1!$A$3:$C$34,AGGREGATE(15,6,ROW(Planilha1!$A$3:$A$34)-ROW(Planilha1!$A$2)/(MMULT((TEXT(CHOOSE({1,2,3},Planilha1!$D$3:$D$34,Planilha1!$G$3:$G$34,Planilha1!$J$3:$J$34),"[$-416]mmmmyyy")=$C$2&$C$3)*(CHOOSE({1,2,3},Planilha1!$F$3:$F$34,Planilha1!$I$3:$I$34,Planilha1!$L$3:$L$34)="Aberto"),{1;1;1})>0),ROWS(B$6:B6)),CHOOSE(COLUMNS($B6:B6),2,1,3)),"")

Check file. Blessings!
 

Attachments

deciog

Active Member
John Jairo V, Good Morning.

Great solution, I loved it.

I made some adjustments to the original spreadsheet and it worked very well

Thank you very much, I have a lot to learn

Big hug

Decio Gassi
 

deciog

Active Member
Gentlemen

Sorry people, when I put the formula in the original spreadsheet, a big problem appeared.

In the Tab Sheet1 I put in red the dates that for the same name have maturities every 15 days
being able to have up to 3 maturities in the same line, I painted in red the new example of the problem for the name Pedro and Tereza.

I don't know how to solve

thanks in advance

Decio Gassi
 

Attachments

John Jairo V

Well-Known Member
Hi, again!

What is the problem? I got this answer with your data:

61933

what is the answer you expected? upload by hand the results if this is wrong, and explain clearly the reasons in words what is the logic of the answer. Blessings!
 

John Jairo V

Well-Known Member
Hi, again...

Try this formulas:

[B6] : =IFERROR(INDEX(Planilha1!$B$3:$B$34,AGGREGATE(15,6,ROW(Planilha1!$B$3:$B$34)-ROW(Planilha1!$B$2)/(TEXT(Planilha1!$D$3:$J$34,"[$-416]mmmmyyy")=$C$2&$C$3)/(Planilha1!$F$3:$L$34="Aberto"),ROWS(B$6:B6))),"") --> Drag it down.

[C6] : =IF($B6="","",INDIRECT("Planilha1!"&TEXT(AGGREGATE(15,6,(ROW(Planilha1!D$3:J$34)/1%+COLUMN(Planilha1!D$3:J$34))/(TEXT(Planilha1!$D$3:$J$34,"[$-416]mmmmyyy")=$C$2&$C$3)/(Planilha1!$F$3:$L$34="Aberto"),ROWS(B$6:B6)),LEFT(ADDRESS(,1,4,0))&"0C00"),)) --> Drag it down and right.

Check file. Blessings!
 

Attachments

Top