Hello,

I need help in making the following task a 1 or 2 step process. Currently, it's very time consuming working with 1500 lines. Below is a sample of my current worksheet.

Screenshot 1 - This is my Data

Screenshot 2: I remove any duplicates to get unique "Account" and "Code" and then complete a SUMIFS formula to get debit and credit dollars from my data tab.

Have the following formula in the Debit Column:

=SUMIFS('Monthly Data'!H:H,'Monthly Data'!H:H,">=0",'Monthly Data'!D:D,Entry!B2,'Monthly Data'!I:I,Entry!E2)

Have the following formula in the Credit Column:

=(SUMIFS('Monthly Data'!H:H,'Monthly Data'!H:H,"<=0",'Monthly Data'!D:D,Entry!B2,'Monthly Data'!I:I,Entry!E2))

I add the Debit Column and Credit Column to the get the net total in column I, Because the system doesn't allow debits and credits on the same line. And, this will be my entry for the month.

Screenshot 3 - Final Entry to submit to the ledger - I manually add to each cell their appropriate debit and credit.

Here is my sample file

sample file1.xlsx

Any help would be appreciated.

thank you.

I need help in making the following task a 1 or 2 step process. Currently, it's very time consuming working with 1500 lines. Below is a sample of my current worksheet.

Screenshot 1 - This is my Data

Screenshot 2: I remove any duplicates to get unique "Account" and "Code" and then complete a SUMIFS formula to get debit and credit dollars from my data tab.

Have the following formula in the Debit Column:

=SUMIFS('Monthly Data'!H:H,'Monthly Data'!H:H,">=0",'Monthly Data'!D:D,Entry!B2,'Monthly Data'!I:I,Entry!E2)

Have the following formula in the Credit Column:

=(SUMIFS('Monthly Data'!H:H,'Monthly Data'!H:H,"<=0",'Monthly Data'!D:D,Entry!B2,'Monthly Data'!I:I,Entry!E2))

I add the Debit Column and Credit Column to the get the net total in column I, Because the system doesn't allow debits and credits on the same line. And, this will be my entry for the month.

Screenshot 3 - Final Entry to submit to the ledger - I manually add to each cell their appropriate debit and credit.

Here is my sample file

sample file1.xlsx

Any help would be appreciated.

thank you.

Last edited: