Help counting instances of text, and returning associated cell values...?

L

ltvrdik

New Member
Joined
Jun 15, 2015
Messages
2
I am trying to figure out how I can take the hosts, and list all volumes associated with each one. For example, in my spreadsheet, I have a column of volumes, and to the right, in a row of separate cells, it lists the hosts associated to this volume. I would like to be able to be able to somehow list each host, and what volumes it has associated with it, and I'm at a total loss here. I've been looking at the COUNTIF function, but it doesn't seem to do what I want. I know that I could do a Ctrl-F and find all. I would have to do this for each and every instance of hosts, and it would take forever, since this is quite a large file. I have a snip of my file below for reference.
Any help is greatly appreciated, since I think I'll be bald if I can't figure this out soon ;)

Volume
Hosts
voll0
192.168.xx.4192.168.xx.4
WZ_prod_01192.168.xx.x1192.168.xx.xx192.168.15.101192.168.x5.xx192.168.xx.xxx
vfiler11_15_vol0TRUE
cp_mic_prdTRUE
vfiler_17_vol0TRUE
vfiler_18_vol0TRUE
ftp_dmz192.xx1.20.20192.168.20.21192.168.20.20192.168.20.21
is_ovm192.xxx.33.30192.xx8.33.xx
vfiler11_33_vol0TRUE
is_ovm_monitoring
192.1xx.xx.0/24
192.xxx.6x.0/24
vfiler11_64_vol0TRUE
monitoring_prod_66
192.xxx.xx.0/24
dmz_6x192.xxx.xx.0/24
prod_dmz192.xxx.66.xx5192.1xx.66.xx192.168.66.116192.xxx.xx228192.xxx.xx.227

<colgroup><col style="mso-width-source:userset;mso-width-alt:7936;width:163pt" width="217"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="mso-width-source:userset;mso-width-alt:3840;width:79pt" width="105"> <col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> <col style="mso-width-source:userset;mso-width-alt:3547; width:73pt" span="4" width="97"> </colgroup><tbody>
</tbody>
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Welcome ltvrdik:

In my example I have the following layout:


Excel 2010
ABCDEF
1VolumeHosts
2voll0192.168.xx.4192.168.xx.4
3WZ_prod_01192.168.xx.x1192.168.xx.xx192.168.15.101192.168.x5.xx192.168.xx.xxx
4vfiler11_15_vol0TRUE
5cp_mic_prdTRUE
6vfiler_17_vol0TRUE
7vfiler_18_vol0TRUE
8ftp_dmz192.xx1.20.20192.168.20.21192.168.20.20192.168.20.21
9is_ovm192.xxx.33.30192.xx8.33.xx
10vfiler11_33_vol0TRUE
11is_ovm_monitoring192.1xx.xx.0/24192.xxx.6x.0/24
12vfiler11_64_vol0TRUE
13monitoring_prod_66192.xxx.xx.0/24
14dmz_6x192.xxx.xx.0/24
15prod_dmz192.xxx.66.xx5192.1xx.66.xx192.168.66.116192.xxx.xx228192.xxx.xx.227
Sheet2



Excel 2010
AB
2192.168.xx.4voll0
3192.168.xx.x1WZ_prod_01
4192.168.xx.xxWZ_prod_01
5192.168.15.101WZ_prod_01
6192.168.x5.xxWZ_prod_01
7192.168.xx.xxxWZ_prod_01
8TRUEvfiler11_15_vol0
9192.xx1.20.20ftp_dmz
10192.168.20.21ftp_dmz
11192.168.20.20ftp_dmz
12192.xxx.33.30is_ovm
13192.xx8.33.xxis_ovm
14192.1xx.xx.0/24is_ovm_monitoring
15192.xxx.6x.0/24is_ovm_monitoring
16192.xxx.xx.0/24monitoring_prod_66
17192.xxx.66.xx5prod_dmz
18192.1xx.66.xxprod_dmz
19192.168.66.116prod_dmz
20192.xxx.xx228prod_dmz
21192.xxx.xx.227prod_dmz
Sheet1
Cell Formulas
RangeFormula
B2=INDEX(Sheet2!$A$2:$A$15,FINDVALUE(A2,Sheet2!$B$2:$F$15)-1)


In Module 1 I have:
Code: 
Sub OriginalExtractUniqueEntries()   Dim ProdSh As Worksheet
   Dim ResSh As Worksheet
   Const ProductRange = "B2:F15" ' change as appropriate
   Const ResultsCol = "A" ' change as appropriate
   Dim productWS As Worksheet
   Dim uniqueList() As String
   Dim productsList As Range
   Dim anyProduct
   Dim LC As Integer
   
   ReDim uniqueList(1 To 1)
   Set ProdSh = Worksheets("Sheet2")
   Set ResSh = Worksheets("Sheet1")
   Set productsList = ProdSh.Range(ProductRange)
   Application.ScreenUpdating = False
   For Each anyProduct In productsList
     If Not IsEmpty(anyProduct) Then
       If Trim(anyProduct) <> "" Then
         For LC = LBound(uniqueList) To UBound(uniqueList)
           If Trim(anyProduct) = uniqueList(LC) Then
             Exit For ' found match, exit
           End If
         Next
         If LC > UBound(uniqueList) Then
           'new item, add it
           uniqueList(UBound(uniqueList)) = Trim(anyProduct)
           'make room for another
           ReDim Preserve uniqueList(1 To UBound(uniqueList) + 1)
         End If
       End If
     End If
   Next ' end anyProduct loop
   If UBound(uniqueList) > 1 Then
     'remove empty element
     ReDim Preserve uniqueList(1 To UBound(uniqueList) - 1)
   End If
   'clear out any previous entries in results column
   If ResSh.Range(ResultsCol & Rows.Count).End(xlUp).Row > 1 Then
     ResSh.Range(ResultsCol & 2 & ":" & _
      ResSh.Range(ResultsCol & Rows.Count).Address).ClearContents
   End If
   'list the unique items found
   For LC = LBound(uniqueList) To UBound(uniqueList)
     ResSh.Range(ResultsCol & Rows.Count).End(xlUp).Offset(1, 0) = _
      uniqueList(LC)
   Next
   'housekeeping cleanup
   Set productsList = Nothing
   Set productWS = Nothing
 End Sub

In a second module I have:
Code: 
Function FindValue(Search_Value As Variant, Lookup_Range As Range) As String
 FindValue = "#N/A" 'If value not found
 For Each c In Lookup_Range
 If c.Value = Search_Value Then
 FindValue = c.Row
 Exit For
 End If
 Next
End Function

Let me know if you have any questions.

Luke
 
Upvote 0
Thank you! This is so cool! However, I have a question. In my range of hosts (B2:F15), I actually have some hosts that are repeated, because they mount more than one volume. This is not catching the instances beyond the first. Do you know how I can adjust this? I don't really know VB. :(

Thanks again,


LloydFinancials said:
Welcome ltvrdik:

In my example I have the following layout:

Excel 2010
ABCDEF
1VolumeHosts
2voll0192.168.xx.4192.168.xx.4
3WZ_prod_01192.168.xx.x1192.168.xx.xx192.168.15.101192.168.x5.xx192.168.xx.xxx
4vfiler11_15_vol0TRUE
5cp_mic_prdTRUE
6vfiler_17_vol0TRUE
7vfiler_18_vol0TRUE
8ftp_dmz192.xx1.20.20192.168.20.21192.168.20.20192.168.20.21
9is_ovm192.xxx.33.30192.xx8.33.xx
10vfiler11_33_vol0TRUE
11is_ovm_monitoring192.1xx.xx.0/24192.xxx.6x.0/24
12vfiler11_64_vol0TRUE
13monitoring_prod_66192.xxx.xx.0/24
14dmz_6x192.xxx.xx.0/24
15prod_dmz192.xxx.66.xx5192.1xx.66.xx192.168.66.116192.xxx.xx228192.xxx.xx.227

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​



Excel 2010
AB
2192.168.xx.4voll0
3192.168.xx.x1WZ_prod_01
4192.168.xx.xxWZ_prod_01
5192.168.15.101WZ_prod_01
6192.168.x5.xxWZ_prod_01
7192.168.xx.xxxWZ_prod_01
8TRUEvfiler11_15_vol0
9192.xx1.20.20ftp_dmz
10192.168.20.21ftp_dmz
11192.168.20.20ftp_dmz
12192.xxx.33.30is_ovm
13192.xx8.33.xxis_ovm
14192.1xx.xx.0/24is_ovm_monitoring
15192.xxx.6x.0/24is_ovm_monitoring
16192.xxx.xx.0/24monitoring_prod_66
17192.xxx.66.xx5prod_dmz
18192.1xx.66.xxprod_dmz
19192.168.66.116prod_dmz
20192.xxx.xx228prod_dmz
21192.xxx.xx.227prod_dmz

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B2=INDEX(Sheet2!$A$2:$A$15,FINDVALUE(A2,Sheet2!$B$2:$F$15)-1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



In Module 1 I have:
Code: 
Sub OriginalExtractUniqueEntries()   Dim ProdSh As Worksheet
   Dim ResSh As Worksheet
   Const ProductRange = "B2:F15" ' change as appropriate
   Const ResultsCol = "A" ' change as appropriate
   Dim productWS As Worksheet
   Dim uniqueList() As String
   Dim productsList As Range
   Dim anyProduct
   Dim LC As Integer
   
   ReDim uniqueList(1 To 1)
   Set ProdSh = Worksheets("Sheet2")
   Set ResSh = Worksheets("Sheet1")
   Set productsList = ProdSh.Range(ProductRange)
   Application.ScreenUpdating = False
   For Each anyProduct In productsList
     If Not IsEmpty(anyProduct) Then
       If Trim(anyProduct) <> "" Then
         For LC = LBound(uniqueList) To UBound(uniqueList)
           If Trim(anyProduct) = uniqueList(LC) Then
             Exit For ' found match, exit
           End If
         Next
         If LC > UBound(uniqueList) Then
           'new item, add it
           uniqueList(UBound(uniqueList)) = Trim(anyProduct)
           'make room for another
           ReDim Preserve uniqueList(1 To UBound(uniqueList) + 1)
         End If
       End If
     End If
   Next ' end anyProduct loop
   If UBound(uniqueList) > 1 Then
     'remove empty element
     ReDim Preserve uniqueList(1 To UBound(uniqueList) - 1)
   End If
   'clear out any previous entries in results column
   If ResSh.Range(ResultsCol & Rows.Count).End(xlUp).Row > 1 Then
     ResSh.Range(ResultsCol & 2 & ":" & _
      ResSh.Range(ResultsCol & Rows.Count).Address).ClearContents
   End If
   'list the unique items found
   For LC = LBound(uniqueList) To UBound(uniqueList)
     ResSh.Range(ResultsCol & Rows.Count).End(xlUp).Offset(1, 0) = _
      uniqueList(LC)
   Next
   'housekeeping cleanup
   Set productsList = Nothing
   Set productWS = Nothing
 End Sub

In a second module I have:
Code: 
Function FindValue(Search_Value As Variant, Lookup_Range As Range) As String
 FindValue = "#N/A" 'If value not found
 For Each c In Lookup_Range
 If c.Value = Search_Value Then
 FindValue = c.Row
 Exit For
 End If
 Next
End Function

Let me know if you have any questions.

Luke
Click to expand...
 
Upvote 0
Great question, Thank you!

I updated and added new formulas next to the macro built list. See if this works.


Excel 2010
ABCDEF
2192.168.xx.4voll0vfiler_17_vol0vfiler11_33_vol0monitoring_prod_66 
3192.168.xx.x1WZ_prod_01
4192.168.xx.xxWZ_prod_01
5192.168.15.101WZ_prod_01
6192.168.x5.xxWZ_prod_01
7192.168.xx.xxxWZ_prod_01
8TRUEvfiler11_15_vol0cp_mic_prdvfiler_18_vol0vfiler11_33_vol0vfiler11_64_vol0
9192.xx1.20.20ftp_dmz
10192.168.20.21ftp_dmz
11192.168.20.20ftp_dmz
12192.xxx.33.30is_ovm
13192.xx8.33.xxis_ovm
14192.1xx.xx.0/24is_ovm_monitoring
15192.xxx.6x.0/24is_ovm_monitoring
16192.xxx.xx.0/24monitoring_prod_66dmz_6x
17192.xxx.66.xx5prod_dmz
18192.1xx.66.xxprod_dmz
19192.168.66.116prod_dmz
20192.xxx.xx228prod_dmz
21192.xxx.xx.227prod_dmz
Sheet1
Cell Formulas
RangeFormula
B2=IF(A2="","",INDEX(Sheet2!$A$2:$A$15,FINDVALUE($A2,Sheet2!$B$2:$F$15)-1))
C2=IF($A2="","",IF(IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(B2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")=$B2,"",IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(B2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")))
D2=IF($A2="","",IF(IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(C2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")=$B2,"",IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(C2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")))
E2=IF($A2="","",IF(IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(D2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")=$B2,"",IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(D2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")))
F2=IF($A2="","",IF(IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(E2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")=$B2,"",IFERROR(INDEX(Sheet2!$A$1:$A$15,FINDVALUE($A2,INDIRECT("Sheet2!$B$"&MATCH(E2,Sheet2!$A$1:$A$15,0)+1&":$F$15"))),"")))


Luke
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA to launch userform by selecting a cell
2
Replies
10
Views
2K
Logit
Logit
D
  • Question
need macro help - text extraction from cell with line breaks
Replies
3
Views
600
Fluff
Fluff
M
  • Question
Two-way lookup with INDEX and MATCH return closest match
Replies
3
Views
529
Marcelo Branco
M
E
  • Question
Count and text other row
Replies
5
Views
497
mrshl9898
M
S
  • Question
place text in row for all matching instances in range
Replies
2
Views
485
saralouise
S

Forum statistics

Threads
1,203,727
Messages
6,056,983
Members
444,901
Latest member
Teal

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top