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: 10

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution
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.​
 
Upvote 0
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))
 
Upvote 0
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: 5
Upvote 0
Scratch that last response, I ran out of TRUEs. I just need to add an error box reflecting that.
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top