# List Accounts Receivable for Period

#### deciog

##### Active Member
Gentlemen, Good Morning

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

Hugs

Decio Gassi

#### Attachments

• 16.1 KB Views: 13

#### vletm

##### Excel Ninja
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?

#### Attachments

• 22.1 KB Views: 7

#### deciog

##### Active Member
vletm, Good Morning.

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

Hugs

Decio

#### vletm

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

#### 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

#### deciog

##### Active Member
vletm

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

Hugs

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

• 17.4 KB Views: 7

#### deciog

##### Active Member
John Jairo V, Good Morning.

Great solution, I loved it.

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

Decio Gassi

#### Attachments

• 17.2 KB Views: 3

#### John Jairo V

##### Well-Known Member
Hi, again!

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!

Apologies

Follows typed

Decio Gassi

#### Attachments

• 15.6 KB Views: 1

#### 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

• 17.6 KB Views: 7

#### deciog

##### Active Member
John Jairo V

Spectacular, saved my life

Big hug

Blessings!

Decio Gassi