Results 1 to 6 of 6

Thread: if true, all are true

  1. #1

    if true, all are true



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a list of data where if one record is true, all records are true. Is there a formula or function to make that work in Excel 2013? I'd prefer not to use macros, if possible.

    Example: column "A" has date. Column "B" has category (options = 1,2,3...18). Column "C" is TRUE/FALSE based on value in category, default = FALSE. Grouped by date, if any record is category 18, then all records for the same date must be TRUE in column C. There are many records for each date. Captured data points do not follow a structured path, so category values fall randomly in the list, meaning lookups/matching/indexes don't work (that I have figured out). Category 18 may or may not show up on any given date. For clarity, the T/F question attached to each record is, "does any record for this date fall into category 18?"

    In the past I've used pivots to list only dates with the pertinent category, with a vlookup in the T/F field (lookup_value = date and table_array = the pivot). It works, though it's clunky.

  2. #2
    How is the C TRUE/FALSE determined?

  3. #3
    As stated, "does any record for this date fall into category 18?" (T/F). Maybe I can clarify. Let's say record "1" for Jan 13, 2015 is category 5 and record "2" is category 18. In this case I want to flag both records 1 and 2 as TRUE, because record 2 is true (it is in fact category 18). Does that help?

  4. #4
    Good afternoon,

    Try this:

    =IF(SUMPRODUCT(($A$1:$A$1000=A1)*($B$1:$B$1000=18))>0,"TRUE","FALSE")

    Array functions aren't exactly my forte, but hopefully this works.

    Best of luck,
    Last edited by bgoree09; 2015-01-14 at 08:36 PM.

  5. #5
    Modified it to fit the real data and it works just fine. Eats up the processor for a minute or two, but returns correct results. Thanks!

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Maybe a bit quicker processing.... if you have Excel 2007 or later...

    =COUNTIFS($A$1:$A$1000,A1,$B$1:$B$1000,18)>0


Posting Permissions

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