Count Text in ColumnB, if ColumnA is unique
Closed
Jay
-
28 Sep 2009 à 10:30
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 28 Sep 2009 à 22:10
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 28 Sep 2009 à 22:10
Related:
- Count Text in ColumnB, if ColumnA is unique
- Excel count occurrences - Guide
- Count names in excel - Guide
- How to hide subscriber count - Guide
- Excel create unique id for each row ✓ - Excel Forum
- Count years - Office Software Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
28 Sep 2009 à 22:10
28 Sep 2009 à 22:10
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 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
28 Sep 2009 à 22:03
28 Sep 2009 à 22:03
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
