Formula to display data if it meets multiple criteria

[Keniesha]

I have a workbook with several worksheets, one that has the data, and the
other that displays the results (report). I have 4 ranges of data - 0-19.9,
20-29.9, 30-39.9, 40-49.9)

My data sheet shows actual data (ex. 15.9 or 37.2) What kind of formula can
i use to get the actual data to show up under the range that it falls under?
So if someone has a 16.3, it would show up under the appropriate column on
the report page.

I hope this doesn't sound confusing.....I appreciate any help!

 

[Gary''s Student]

This is just an example that you can adapt to your needs. It assumes that
the data is in column A in Sheet1. The data will be transferred to Sheet2 in
columns A thru D.

Sub keniesha()
Dim v As Variant
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
n1 = s1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n1
v = s1.Cells(i, "A").Value
Select Case v
Case Is < 20
colum = 1
Case Is < 30
colum = 2
Case Is < 40
colum = 3
Case Else
colum = 4
End Select
roww = s2.Cells(Rows.Count, colum).End(xlUp).Row + 1
s2.Cells(roww, colum) = v
Next
End Sub

So if the data is:

7
3
41
17
17
30
26
17
36
37
4
14
46
17
45
5
13
7
26
41
45
35
45
33
44
21
1
39
19
11
37
44
46
29
23
30
20
25
41
5
25
3
19
29
13
28
48
50
39
19

the result will be:

7 26 30 41
3 26 36 46
17 21 37 45
17 29 35 41
17 23 33 45
4 20 39 45
14 25 37 44
17 25 30 44
5 29 39 46
13 28 41
7 48
1 50
19
11
5
3
19
13
19

 

[Keniesha]

Thank you so much for your response, I may not have explained myself
accurately enough. My results sheet headings are set up as such:

BMI <=18.5-24.9 25-29.9 30+


Weight <=173 174-250 251+

There are other data results that I would like to show up on this page as
well, but we'll just use what I typed above. Each participant has their own
set of results that need to be displayed based on the data entered on the
"data" worksheet.

If I enter that "Miss Jones" has a BMI of 26.7, I would like for this number
to show up under the appropriate heading on the results sheet. I was able to
use the sumproduct formula on another worksheet because I was just trying to
COUNT, but I can't get it to work for this sheet (maybe because the data is a
little bit more complex?

 

[Gary''s Student]

Using COUNT will tell you how many values fall into a catagory, it will not
transfer the actual data items.

If we make a small modification to the macro to use only three columns:

Sub bmi()
Dim v As Variant
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
n1 = s1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n1
v = s1.Cells(i, "A").Value
Select Case v
Case Is < 25
colum = 1
Case Is < 30
colum = 2
Case Else
colum = 3
End Select
roww = s2.Cells(Rows.Count, colum).End(xlUp).Row + 1
s2.Cells(roww, colum) = v
Next
End Sub

and we have BMI data in Sheet1 that looks like (Jones is the first entry):

26.7
32.0
29.4
43.8
21.2
36.7
41.9
21.1
19.0
20.6
35.9
19.3
23.5
31.4
44.9
43.3
31.5
32.6
36.2
44.0
21.9
21.3
36.6
45.5
19.3
21.6
32.4
19.8
37.6
45.0
35.4
23.0
27.4
44.2
35.6
18.2
43.2
39.8
24.5
34.6
40.8
33.7
28.0
29.0
43.8
43.4
19.2
29.4
33.7
22.5

then in the results sheet:

21.2 26.7 32.0
21.1 29.4 43.8
19.0 27.4 36.7
20.6 28.0 41.9
19.3 29.0 35.9
23.5 29.4 31.4
21.9 44.9
21.3 43.3
19.3 31.5
21.6 32.6
19.8 36.2
23.0 44.0
18.2 36.6
24.5 45.5
19.2 32.4
22.5 37.6
45.0
35.4
44.2
35.6
43.2
39.8
34.6
40.8
33.7
43.8
43.4
33.7

Note that the Jones value appears in the second column.