Solved

Count between worksheets using subset of data (list)

Posted on 2011-05-06
405 Views
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
Question by:justearth

LVL 33

Assisted Solution

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!?!

0

Author Comment

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

Author Comment

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

LVL 44

Accepted Solution

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))
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
``````
0

Author Closing Comment

Thank you!
0

Featured Post

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …