Count Text in ColumnB, if ColumnA is unique
Closed
Jay
-
Sep 28, 2009 at 10:30 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 28, 2009 at 10:10 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 28, 2009 at 10:10 PM
Related:
- Count Text in ColumnB, if ColumnA is unique
- Count names in excel - Guide
- Count occurrences in excel - Guide
- How to hide subscriber count - Guide
- Count in french excel - Guide
- Stop counting days in excel when status is changed ✓ - Excel Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 28, 2009 at 10:10 PM
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
Keep your data file safely somewhere. if there is some mess up the data is available for you
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 28, 2009 at 10:03 PM
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
the result is column G and H will be
h2
ABCD 4
EFGH 5
if this is ok confirm
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
