+ Reply to Thread
Results 1 to 7 of 7

Thousands of lines with debits and some credits that offset

  1. #1
    Registered User
    Join Date
    11-16-2023
    Location
    adelaide australia
    MS-Off Ver
    2023
    Posts
    4

    Thousands of lines with debits and some credits that offset

    Please help, i have thousands of numbers, not in any particular order and I am needing to offset what can offset, how can i Isolate the numbers that offset? Please help

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Thousands of lines with debits and some credits that offset

    Wayyyy too little information. Please read the yellow banner (top) about sample sheets and act on its guidelines.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh

  3. #3
    Registered User
    Join Date
    11-16-2023
    Location
    adelaide australia
    MS-Off Ver
    2023
    Posts
    4

    Re: Thousands of lines with debits and some credits that offset

    I have column of numbers of around 11k rows, a portion of the given numbers offset and net off to zero, they are in no order. For example,
    102.43
    103.40
    77.27
    29.82
    124.36
    29.54
    39.09
    27.14
    6.15
    89.54
    114.09
    45.45
    48.76
    12.76
    20.09
    22.18
    4.09
    -102.43
    -103.4
    -77.27
    -29.82
    -124.36
    -29.54
    -39.09
    -27.14
    -6.15
    -89.54

    The total I need to be left is 267.42, is it possible to isolate the positive and negative number that net off to zero? e.g. 103.40 & -103.40.

    I would be so very grateful for any help

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Thousands of lines with debits and some credits that offset

    Not an Excel file as requested, but it is clear now!

    =SUMIF(A:A,">0")+SUMIF(A:A,"<0")

  5. #5
    Registered User
    Join Date
    11-16-2023
    Location
    adelaide australia
    MS-Off Ver
    2023
    Posts
    4

    Re: Thousands of lines with debits and some credits that offset

    Thank you so very much, can the offset amounts be isolated in the rows, is this possible? e.g the orange amounts that offset to zero? there is no order to the data

    102.43
    103.4
    77.27
    29.82
    124.36
    29.54
    39.09
    27.14
    6.15
    89.54

    114.09
    45.45
    48.76
    12.76
    20.09
    22.18
    4.09
    -102.43
    -103.4
    -77.27
    -29.82
    -124.36
    -29.54
    -39.09
    -27.14
    -6.15
    -89.54

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Thousands of lines with debits and some credits that offset

    No, not quite... since they are "not in any particular order". It took a while to come up with something that covered:

    two +ve numbers at the same value and only 1 -ve number
    two +ve numbers at teh same value and 2 -ve numbers.

    This (monstrous) Conditional Formatting formula does that.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See file. Unshaded cells have no match.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-16-2023
    Location
    adelaide australia
    MS-Off Ver
    2023
    Posts
    4

    Re: Thousands of lines with debits and some credits that offset

    Thank you so very much, grateful appreciation for your help, very thankful to learn and see the work of such brilliant excel minds.

    Thank you again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Match Debits and Credits
    By TaxLady in forum Excel General
    Replies: 5
    Last Post: 02-17-2021, 08:07 PM
  2. [SOLVED] Offset debits and credits - p/o
    By leebird in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-05-2015, 09:22 PM
  3. Debits and credits
    By Willow350 in forum Excel General
    Replies: 2
    Last Post: 12-07-2014, 11:34 AM
  4. debits and credits in one column
    By childothe1980s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2013, 03:47 PM
  5. [SOLVED] Lookup Debits & Credits
    By Sniper in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2012, 10:25 AM
  6. [SOLVED] Change debits to credits
    By Yo in forum Excel General
    Replies: 1
    Last Post: 04-10-2006, 08:20 PM
  7. [SOLVED] vertical lines between credits and debits
    By Carolyn in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-28-2006, 12:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1