• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Count between worksheets using subset of data (list)

Hello,
I have 10 lists ('lists' worksheet) each containing 111 numbers. They were selected from the 226 available (worksheet 'data').  

I would like to know the count (number of cells) where the value is not  '-9999' for each of the four columns in 'data' for each of the 10 lists in 'lists'.

An example of the output is in sheet 'results'

Can this be done with a formula or macro?

Thank you kindly,
JE
je-experts-lists-count.xlsx
0
justearth
Asked:
justearth
  • 3
2 Solutions
 
jppintoCommented:
Your question doesn't make sense! How do you relate the column A (tp) of sheet "data" with the values on sheet "lists"? For instance, you have on cell A2 of sheet "data" a tp like 1.1. If a check on sheet "lists", you have 1.1 values on all of the columns of tp's!?!

Can you please explain so that we can help you?
0
 
justearthAuthor Commented:
I hope I can explain.

Sheet 'data':
Column a = tp (the original list)

Sheet "lists"
tp.1, tp.2, etc are subsets of tp from sheet 'data'
All of the lists (tp.x) share many of the same values (each list is unique).

so, for example, tp.1 and tp each 1.1 and the all values for 1.1 are not -9999 so it would be included in the count.
....
tp.1 and tp each have 19.1 and its value is -9999 for the ln_slope92 it is not included in the count. The values for ln_mean92, ln_slope01, and ln_mean01 at 19.1 are not -9999 so they sho be included in the count.
.....

I hope I have provided some clarity,
Thank you,
JE
0
 
justearthAuthor Commented:
I hope I can explain.

Sheet 'data':
Column a = tp (the original list)

Sheet "lists"
tp.1, tp.2, etc are subsets of tp from sheet 'data'
All of the lists (tp.x) share many of the same values (each list is unique).

so, for example, tp.1 and tp each 1.1 and the all values for 1.1 are not -9999 so it would be included in the count.
....
tp.1 and tp each have 19.1 and its value is -9999 for the ln_slope92 it is not included in the count. The values for ln_mean92, ln_slope01, and ln_mean01 at 19.1 are not -9999 so they should be included in the count.
.....

I hope I have provided some clarity,
Thank you,
JE
0
 
aikimarkCommented:
This should do the special counts.  

Note: The CSng() function was required because the data worksheet numeric data was formatted as text.
Option Explicit

Public Sub SpecialCounts()
  Dim rngTP As Range
  Dim rngLists As Range
  Dim rngData As Range
  Dim rngList As Range
  Dim rngValues As Range
  Dim varResults(1 To 10, 0 To 4) As Variant
  Dim lngLoop As Long
  
  Dim lngCounts(1 To 4) As Long
  Dim dicTP As Object
  Set dicTP = CreateObject("scripting.dictionary")
  Set rngLists = Worksheets("lists").Range(Worksheets("lists").Range("a1"), Worksheets("lists").Range("a1").End(xlToRight))
  For Each rngList In rngLists
    dicTP.RemoveAll
    Erase lngCounts
    
    For Each rngTP In Worksheets("lists").Range(rngList.Offset(1), rngList.End(xlDown))
      dicTP.Add rngTP.Value, 1
    Next
    
    Set rngData = Worksheets("data").Range(Worksheets("data").Range("a1").Offset(1), Worksheets("data").Range("a1").End(xlDown))
    For Each rngTP In rngData
      If dicTP.exists(CSng(rngTP.Value)) Then
        For Each rngValues In rngTP.Range(rngTP.Offset(0, 1), rngTP.End(xlToRight))
          If rngValues.Value <> -9999 Then
            lngCounts(rngValues.Column - 1) = lngCounts(rngValues.Column - 1) + 1
          End If
        Next
      End If
    Next
    'Debug.Print rngList, lngCounts(1), lngCounts(2), lngCounts(3), lngCounts(4)
    varResults(Val(Mid(rngList, 3)), 0) = rngList
    For lngLoop = 1 To 4
      varResults(Val(Mid(rngList, 3)), lngLoop) = lngCounts(lngLoop)
    Next
  'Stop
  Next
  Worksheets("results").Range(Worksheets("results").Range("A2"), Worksheets("results").Range("A2").Offset(9, 4)) = varResults
  'Stop
End Sub

Open in new window

0
 
justearthAuthor Commented:
Thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now