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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Original Data[/TD]
[TD]Places[/TD]
[TD]Number of.[/TD]
[/TR]
[TR]
[TD]Glasgow[/TD]
[TD]Glasgow[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]London[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]Birmingham[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Glasgow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Glasgow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="width: 819"]
<colgroup><col><col><col><col span="3"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Original Data[/TD]
[TD]Places[/TD]
[TD]Number of.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Glasgow[/TD]
[TD]Glasgow[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]London[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]Birmingham[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of Original Data[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Glasgow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Original Data[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Birmingham[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Glasgow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Glasgow[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]London[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]a simple pivot table applied to column A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]