+ Reply to Thread
Results 1 to 4 of 4

Compare 2 different columns based on 1 result and match all results in column 2

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Compare 2 different columns based on 1 result and match all results in column 2

    I have 2 different independent columns. Column A is claim number and column B is bill amount. There are 22000 rows. What I am trying to do is match all claim numbers where there are bill amounts of 0. After I get that I want to only show the claim numbers that have duplicates and have a bill amount of 0. I need to keep duplicate claim numbers only if one of the matching claim numbers has a bill value of 0.

    Is there a formula to do this?

    Claim # Bill Amnt
    5315945 0.00
    6248561 120.00
    .... ....

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Compare 2 different columns based on 1 result and match all results in column 2

    KaleyF, welcome to the forum.

    Have you 3 separate requirements, as described above? Your post reads (to me, at least) as if keeping "duplicate claim numbers only if one of the matching claim numbers has a bill value of 0" is the end game, but you will have no results for that if you're already showing only "claim numbers that have duplicates and have a bill amount of 0". You might confirm before I put any work into this, please.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Compare 2 different columns based on 1 result and match all results in column 2

    The spreadsheet I have has multiple claim numbers which may or may not have duplicate claim numbers on separate lines and a bill amount of 0.00.

    I need the claim numbers with bill amounts of 0.00 and any other subsequent bill amounts with that same claim number grouped together.
    If a claim number has a bill amount of 0.00, then I will need the other lines that may have this same claim number grouped with the 0.00 bill amount.

    An example of how I would like it to look is:

    Claim# Bill Amnt
    0123456 0.00
    0123456 150.00
    0123456 1100.00
    9876543 0.00
    9876543 750.00
    9876543 800.00
    ... ...

    Sorry for the confusion. Hope this helps.

    Thank you!

  4. #4
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Compare 2 different columns based on 1 result and match all results in column 2

    Ok, the attached uses formulas and the data filter to return ONLY claim numbers where there is a bill amount of 0 AND there are other bill amounts associated with the same claim number.

    In C2, and copied down:

    Please Login or Register  to view this content.
    identifies claim numbers that occur more than once, and have a 0 value associated with them. (So if there is a claim number which has a 0 bill amount, and the claim number does not occur again, that claim number is disregarded.) By identifying this cohort of claim numbers, we can use, in D2 and copied down:

    Please Login or Register  to view this content.
    to identify ALL the claim numbers in exist in Column A that are associated with a 0 value bill. If you now use the filter on Column D to remove "blanks", you are left with what you are looking for.

    Hope this helps.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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