List Accounts Receivable for Period

[deciog]

Gentlemen, Good Morning

I need the help of you again, explanation within the spreadsheet

Thanks in advance

Hugs

Decio Gassi

 

[vletm]

deciog
Open titles ... yes!
... I should guess what would those are.
... You wrote something about year and month ... but You would like to get dates ...
Are those 'sheet2' ... correct results?
If makes Pivot-table to Planilha1... would it show Your hoped results?

 

[deciog]

vletm, Good Morning.

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

Hugs

Decio

 

[vletm]

deciog
hmm ... You need formulas ... not results ... interesting?
Good luck.

 

[deciog]

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

 

[vletm]

deciog
1) Pivot-table: https://chandoo.org/wp/excel-pivot-tables-tutorial/
2) Your sample should be as near as You original file.
3) Anyway, You should give answers ...

 

[deciog]

vletm

Thanks, I'll study, but I need the solution with formulas

Hugs

Decio

 

[John Jairo V]

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!

 

[deciog]

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]

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

 

[John Jairo V]

Hi, again!

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



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!

 

[deciog]

Apologies

Follows typed

Decio Gassi

 

[John Jairo V]

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!

 

[deciog]

John Jairo V

Spectacular, saved my life

Big hug

Blessings!

Decio Gassi