Extracting Text From a Column

[skuddyb]

Hi Guys,


I basically have a column of data made up of 0's and names of places.

In the column adjacent and to the right of it I wish to return all of the words in the column but not the 0's. If possible I want to be able to return only one of each individual word and then in the cell adjacent to this (2 columns over from the original data) tally them up

Original Data	Places	Number of.
Glasgow	Glasgow	3
0	London	2
London	Birmingham	2
London
Birmingham
0
Glasgow
Birmingham
Glasgow

<tbody>
</tbody>

This is what I want it to look like in the end, although i have thousands of bits of data in column 1.

if anyone could help out with this i'd be grateful!

 

[Gary's Student]

Try this short macro:

Sub Consolidatee()
   Dim i As Long, j As Long, N As Long, c As Collection
   Dim v As String, wf As WorksheetFunction
   Set wf = Application.WorksheetFunction
   Set c = New Collection
   N = Cells(Rows.Count, "A").End(xlUp).Row
   j = 2
   On Error Resume Next
   
   For i = 2 To N
      v = Cells(i, 1).Text
      If v <> "0" Then
         c.Add v, CStr(v)
         If Err.Number = 0 Then
            Cells(j, 2).Value = v
            Cells(j, 3).Value = wf.CountIf(Range("A1:A" & N), v)
            j = j + 1
         Else
            Err.Number = 0
         End If
      End If
   Next i
End Sub

 

[skuddyb]

Hi,

I managed to use this formula to get it to return individual values in the adjacent column:

=INDEX($BB$2:$BB$10000, MATCH(0, COUNTIF($BC$1:BC1, $BB$2:$BB$10000), 0))

Now in the next column "BD" I wish it to return the number of times the text appeared in column "BB", what do i need for this?

 

[oldbrewer]

Original Data	Places	Number of.
Glasgow	Glasgow	3
0	London	2
London	Birmingham	2
London
Birmingham
0						Count of Original Data
Glasgow						Original Data	Total
Birmingham						Birmingham	2
Glasgow						Glasgow	3
						London	2
						Grand Total	7
					a simple pivot table applied to column A

<colgroup><col><col><col><col span="3"><col><col><col span="2"></colgroup><tbody>
</tbody>