Count Text in ColumnB, if ColumnA is unique

Closed
Jay - Sep 28, 2009 at 10:30 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 28, 2009 at 10:10 PM
Hello,

I am looking for a way to count the number of times certain text appears in ColumnB, but only when ColumnA is unique.

For example

COLUMNA COLUMNB
1101 ABCD
1102 ABCD
1103 EFGH
1104 EFGH
1105 ABCD
1105 ABCD
1106 EFGH
1107 EFGH
1107 EFGH
1108 ABCD
1109 EFGH


So, based on this table, I need to find the # of time ABCD occurs in ColumnB, but IF COLUMNA is duplicated, then ColumnB is only counted once.

COUNT ABCD = 4
COUND EFGH = 5


Thank you in advance for your help. I am using EXcel 2007 on WINXP

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 28, 2009 at 10:10 PM
caution:

Keep your data file safely somewhere. if there is some mess up the data is available for you
1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 28, 2009 at 10:03 PM
this is a manual solution

introduce a heading row e.g. h1 and h2 in row 1
the data will be like this in column A and B
h1 h2
1101 ABCD
1102 ABCD
1103 EFGH
1104 EFGH
1105 ABCD
1105 ABCD
1106 EFGH
1107 EFGH
1107 EFGH
1108 ABCD
1109 EFGH

now run this macro

Sub test()
Dim r As Range, c As Range, r1 As Range
Range(Range("c2"), Cells(2, Columns.Count).End(xlToLeft)).EntireColumn.Delete
Set r = Range(Range("A1"), Range("A1").End(xlDown).End(xlToRight))
r.AdvancedFilter action:=xlFilterCopy, copytorange:=Range("d1"), unique:=True
Set r = Range(Range("E1"), Range("E1").End(xlDown))
r.AdvancedFilter action:=xlFilterCopy, copytorange:=Range("G1"), unique:=True
'msgbox r.Address
Set r1 = Range(Range("g2"), Range("g2").End(xlDown))
'msgbox r1.Address
Set r = r1.Offset(0, 1)
'msgbox r.Address
For Each c In r
c = WorksheetFunction.CountIf(r1, c)
Next c
Range("H2").Formula = "=COUNTIF($E$2:$E$10,G2)"
Range("H2").Copy
Range(Range("H2"), Range("H2").Offset(0, -1).End(xlDown).Offset(0, 1)).PasteSpecial
Application.CutCopyMode = -False

End Sub


the result is column G and H will be
h2
ABCD 4
EFGH 5

if this is ok confirm
0