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

Modthis

New Member
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
179.7 KB · Views: 9

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+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

Modthis

New Member
Hi & welcome to MrExcel.
+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
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

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

• Modthis

Marc L

Well-known Member
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.​

• Modthis

RoryA

MrExcel MVP, Moderator

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 and Fluff

Modthis

New Member
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.

Modthis

New Member
Scratch that last response, I ran out of TRUEs. I just need to add an error box reflecting that.

Peter_SSs

MrExcel MVP, Moderator
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
End If

Anyway, glad you got it sorted. Replies
1
Views
284
Replies
2
Views
106
Replies
2
Views
104
Replies
2
Views
210
Replies
3
Views
218 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

1,151,768
Messages
5,766,370
Members
425,350
Latest member
procha 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.    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

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