Counting text strings in columns (Excel 2000)

  • Thread starter Thread starter gavin
  • Start date Start date
G

gavin

I have a worksheet with seven columns of text strings. I want to find out
which of the text strings appears in more than one column and in how many
columns. Is there a formula which will do this for me?

Thanks,


Gavin
 
Gavin

You seem to be short of offers on this one!!
The only way I can suggest is to copy the seven columns into one column (on
a separate sheet so you don't screw anything up). From there you can use a
pivot table or subtotals to find what you require.

Andy.
 
I like that idea.

In fact, if I were doing it I'd add an adjacent column indicating what column
the name came from. Then when I built the pivottable, I'd use that as the
column field and the count of names as the data field.

I did a small test. I filled a range of 7 columns with
="asdf"&randbetween(1,55). I put headers in row 1 (so the data was in rows
2:xxx).

Then I copied|pasted, added the adjacent column info to get a list that looked
like:

Name Col
asdf12 A
asdf51 A
asdf32 A
asdf9 A
asdf18 A
asdf12 A
asdf11 A
asdf11 A
asdf50 A
asdf32 A
asdf45 A
asdf9 A
asdf29 A
asdf43 A
.....

then I did the pivottable stuff and got a report that looked like:

Count of Name Col
Name A B C D E F G Grand Total
asdf1 1 1 2
asdf10 1 1 2
asdf11 3 1 1 1 3 9
asdf12 2 1 2 5
asdf14 1 1 1 3
asdf15 1 1 2 4
asdf16 1 1 1 1 1 5
asdf17 1 1 1 3
asdf18 1 1 1 1 4
asdf19 1 1 1 3
asdf2 2 2


I could see that asdf11 was used 3 times in A, once in D, E & F and 3 times in G
(9 total uses).

A macro (xl2k or higher) that would do this might look like:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim pvtWks As Worksheet
Dim iCol As Long
Dim myRng As Range
Dim destCell As Range
Dim colLetter As String

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

newWks.Range("a1").Resize(1, 2).Value = Array("Name", "Col")

With curWks
For iCol = 1 To 7
'I had headers in row 1, so I started on row 2.
Set myRng = .Range(.Cells(2, iCol), .Cells(2, iCol).End(xlDown))
With newWks
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myRng.Copy _
Destination:=destCell

colLetter = Columns(iCol).Address(False, False)
colLetter = Left(colLetter, InStr(1, colLetter, ":") - 1)
destCell.Offset(0, 1).Resize(myRng.Rows.Count, 1).Value _
= colLetter

Next iCol
End With

'do the pivottable
With newWks
Set myRng = .Range("A1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRng.Address(external:=True)).CreatePivotTable _
TableDestination:=""

Set pvtWks = ActiveSheet

With pvtWks
.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
.PivotTables(1).AddFields RowFields:="Name", ColumnFields:="Col"
.PivotTables(1).PivotFields("Name").Orientation = xlDataField

.UsedRange.Columns.AutoFit
End With

End Sub

If you're using xl97, the dump the 'do the pivottable code and do it manually.
(If you have to repeat this, then record a macro when you do it once and plop it
in here.)
 
Back
Top