Search : in
By :

Count Text in ColumnB, if ColumnA is unique

Last answer on Sep 29, 2009 3:10:42 am BST Jay, on Sep 28, 2009 3:30:10 pm BST 
 Report this message to moderators

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

Configuration: Windows XP
Firefox 3.5.3

Best answers for « Count Text in ColumnB, if ColumnA is unique » in :
Text String count within a Cell ShowText String count within a Cell Issue Solution Note Issue I have number of records (see below) and want to know if I can use a function to count the number of records that contain a specific text string within each cell entry? e.g....
How to count number of words in MS Words sheet! ShowHow to count number of words in MS Words sheet! It is quite simple to count the number of words in a Microsoft word sheet. This tip might be very useful for example, if you wish to make a presentation on Microsoft word for an approximate...
Excel - Using IF statement to add 1 to total ShowExcel - Using IF statement to add 1 to total Issue Solution Note Issue I am trying to get a formula to add + 1 to a total cell based on the word typed. Example : If I type the word Duty in cells B3:B18 then add + 1 to cell B35. If...
Download AM-Deadlink ShowAM-DeadLink detects dead links and duplicates in browser bookmarks and text files. If a bookmark has become unavailable you can verify and delete it permanently. Additionally you can download FavIcons for all your Favorites and Bookmarks. AM...
Download Recover Deleted text Messages Show
Download Text to MP3 ShowText to MP3 allows to convert text files into WAV or MP3 files. It can solve your daily problem about electronic texts. With this software published by United Research Labs, you can listen to your documents as MP3 files in 11 different languages....
Spreadsheet - Text Operators ShowConcatenation Operator Spreadsheets generally use an oeprator, called a concatenation operator , that allows two text values to be added together. The concatenation operator, written as &, is used as follows: In the above example, the cell...
Ask your question Reply

1

venkat1926, on Sep 29, 2009 3:03:52 am BST

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

   
Reply to venkat1926

2

 venkat1926, on Sep 29, 2009 3:10:42 am BST

Caution:

Keep your data file safely somewhere. if there is some mess up the data is available for you

   
Reply to venkat1926
Ask your question Reply
They need your help