Return value if all instances of value contain "True"?

Modthis

New Member
Joined
Oct 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
In column A, "1234" appears 5 different times. In the adjacent column B, there are 4 instances of "True" but 1 instance of "False."

I only want to return the value from column A (once) if all instances are equal to "True.' So in the example above, "1234" would not be returned. Only the ones in green should be returned and only once not multiple times.

Is there a VBA or formula I can use to accomplish this?
 

Attachments

  • Screenshot_20211026-140602__01.jpg
    Screenshot_20211026-140602__01.jpg
    179.7 KB · Views: 9

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,943
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCD
11234TRUE
21234TRUE4321
31234TRUE4444
41234TRUE
51234FALSE
64321TRUE
74321TRUE
88888TRUE
98888FALSE
108888FALSE
114444TRUE
12555FALSE
Data
Cell Formulas
RangeFormula
D2:D3D2=UNIQUE(FILTER(A1:A12,COUNTIFS(B1:B12,TRUE,A1:A12,A1:A12)=COUNTIFS(A1:A12,A1:A12)))
Dynamic array formulas.
 

Modthis

New Member
Joined
Oct 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCD
11234TRUE
21234TRUE4321
31234TRUE4444
41234TRUE
51234FALSE
64321TRUE
74321TRUE
88888TRUE
98888FALSE
108888FALSE
114444TRUE
12555FALSE
Data
Cell Formulas
RangeFormula
D2:D3D2=UNIQUE(FILTER(A1:A12,COUNTIFS(B1:B12,TRUE,A1:A12,A1:A12)=COUNTIFS(A1:A12,A1:A12)))
Dynamic array formulas.
As far as I can tell this works. Long freezes when calculating 100k lines. Is there maybe a less CPU intensive way?
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,728
See if the following VBA code works for you. It assumes your dataset is located in the very first worksheet of your workbook.
VBA Code:
Sub Test()
    Dim brc As Range, str As String
    Application.ScreenUpdating = False
    Sheets(1).Copy Before:=Sheets(1)
    Range("B:C").Clear
    Range("A:A").RemoveDuplicates 1, xlNo
    Range("1:1").Insert xlShiftDown
    Set brc = Range("A" & Rows.Count).End(xlUp).Offset(, 1)
    str = Sheets(2).Range("A:A").Address(1, 1, xlR1C1, True)
    str = WorksheetFunction.Replace(str, Len(str), 1, "")
    Range("B2", brc).FormulaR1C1 = "=COUNTIFS(" & str & "1,RC[-1]," & str & "2,TRUE)=COUNTIFS(" & str & "1,RC[-1])"
    Range("A1", brc).AutoFilter Field:=2, Criteria1:=True
    Range("A1", brc).Resize(, 1).Copy Range("C1")
    Range("A:B").Delete
    Range("1:1").Delete
    Application.ScreenUpdating = True
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,261
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You could also give this a try.
It it is possible that there could be more than about 65,000 values in the final result then it will need a bit of a modification.

VBA Code:
Sub Unique_True_Only()
  Dim d As Object
  Dim a As Variant, Ky As Variant
  Dim i As Long

  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If Not d.exists(a(i, 1)) Then
      d(a(i, 1)) = a(i, 2)
    Else
      If d(a(i, 1)) Then d(a(i, 1)) = a(i, 2)
    End If
  Next i
  For Each Ky In d.Keys
    If Not d(Ky) Then d.Remove Ky
  Next Ky
  Range("C1").Resize(d.Count).Value = Application.Transpose(d.Keys)
End Sub
 
Solution

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,759
Office Version
  1. 2010
Platform
  1. Windows
Is there maybe a less CPU intensive way?
For sure but as it depends on your worksheet design so I won't give it a try without any workbook sample attachment with source data & expected result …​
Anyway the previous post code could be enough fast for only 100K rows.​
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You can simplify Fluff's criteria section a little to:

Excel Formula:
=UNIQUE(FILTER(A1:A12,COUNTIFS(B1:B12,"<>TRUE",A1:A12,A1:A12)=0))
 

Modthis

New Member
Joined
Oct 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
You could also give this a try.
It it is possible that there could be more than about 65,000 values in the final result then it will need a bit of a modification.

VBA Code:
Sub Unique_True_Only()
  Dim d As Object
  Dim a As Variant, Ky As Variant
  Dim i As Long

  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If Not d.exists(a(i, 1)) Then
      d(a(i, 1)) = a(i, 2)
    Else
      If d(a(i, 1)) Then d(a(i, 1)) = a(i, 2)
    End If
  Next i
  For Each Ky In d.Keys
    If Not d(Ky) Then d.Remove Ky
  Next Ky
  Range("C1").Resize(d.Count).Value = Application.Transpose(d.Keys)
End Sub
This seemed to work for the data I had in there but any attempts to update the data breaks and I'm not sure why. A and B columns are formulas (A is a simple = and B is a simple IF) that reference another sheet starting at A2 and B2 if that helps.
 

Attachments

  • image001.png
    image001.png
    19.3 KB · Views: 3

Modthis

New Member
Joined
Oct 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Scratch that last response, I ran out of TRUEs. I just need to add an error box reflecting that.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,261
Office Version
  1. 365
Platform
  1. Windows
Scratch that last response, I ran out of TRUEs. I just need to add an error box reflecting that.
Yes, I probably should have included a block something like this instead of that single line.
VBA Code:
If d.Count Then
  Range("C1").Resize(d.Count).Value = Application.Transpose(d.Keys)
Else
  MsgBox "No results"
End If

Anyway, glad you got it sorted. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,783
Messages
5,766,437
Members
425,354
Latest member
kshivanand21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top