Frequency tracker

[Mally]

Hi. I want to display the number of times a number is picked by representing
them in cells on top of each other with no spaces. I tried doing it in a
pivot table/chart but it didn't work.

I have a worksheet with numbers 1-8 (for WEEKS) in column cells A1 - A8
I then have the numbers 1-10 in row cells B9-K9 for the numbers actually
picked.

e.g.
WEEK 1. If in week one numbers 1, 2, 5, 7, 9 and 10 are picked then row
cells B1, C1, F1, H1, J1 and K1 contain the number 1 for WEEK 1

WEEK 2. In week two numbers 1, 2, 5, 7, 8 and 10 are picked. Cells B2, C2,
F2, H2 and K2 will all have the number 2 in for WEEK 2. But 8 is a new
number so I want the number 2 for WEEK 2 to be displayed in cell I1.

Simply, I want the numbers displayed on top of each other in cells with no
spaces depending on the weeks they are picked.

I've got the formaula (below) which displays the numbers but not without the
spaces. The cell references are different.

=IF(OR($E20=B$10,$F20=B$10,$G20=B$10,$H20=B$10,$I20=B$10),$A20,"")

Any ideas anyone?

 

[Max]

One idea to set it up which might appeal to you ..

Illustrated in this sample:
http://www.savefile.com/files/1610056
Display Multiple Selections wo Blank Columns.xls

Inputs are assumed within B1:K8 to denote selections from B9:K9
Inputs don't have to be numbers

In B11: =IF(B1="","",COLUMNS($A:A))
B11 copied across/filled down to K18 to cover input range in B1:K18. This
sets up the criteria range (can be hidden away)

Then to auto-display the results
In B20:
=IF(COLUMNS($A:A)>COUNT($B11:$K11),"",INDEX($B$9:$K$9,SMALL($B11:$K11,COLUMNS($A:A))))
Copy B20 across/fill down to K27 to return the desired results, all neatly
bunched to the left

 

[Mally]

Hi Max. Thanks for the reply and the emaxples.

Just one thing on the idea is that i don't want them formatted to the left.

e.g

If
Week 1 = 1, 2, 3, 4, 8 ,10
Week 2 = 2, 4, 5, 7, 8, 9
Week 3 = 4, 6, 7, 8, 9, 10

Then the results would be (as illustrated in the table below)

week
8
7
6
5
4
3 3 3
2 2 2 3 2 3
1 1 1 1 1 2 3 2 3 1 1
1 2 3 4 5 6 7 8 9 10 num

So the table above shows that numbers 4 and 9 were the most frequent at
being picked, 3 times each in 3 weeks.

When a number is picked in week 1 it will get 1 displayed in the table.
When a number is picked in week 2 it will get 2 displayed in the table.
When a number is picked in week 3 it will get 3 displayed in the table etc.

Thanks

 

[Mally]

Sorry, The table should read

If
Week 1 = 1, 2, 3, 4, 8 ,10
Week 2 = 2, 4, 5, 7, 8, 9
Week 3 = 4, 6, 7, 8, 9, 10

week
8
7
6
5
4
3 3 3 3
2 2 2 3 2 2 3
1 1 1 1 1 2 3 2 1 2 1
1 2 3 4 5 6 7 8 9 10 num

Showing that 4, 8 and 9 were picked 3 times in 3 weeks.

 

[Max]

Maybe you're after something like this:
http://www.freefilehosting.net/download/3id4e
Mally_1.xls

Inputs assumed within B1:K8
to denote selections from B9:K9
B19:K19 is the same as B9:K9

In B11: =IF(B1=B$19,ROWS($1:1),"")
B11 copied across/filled down to K18 to cover input range in B1:K8

Spare a moment to hit the "Yes" button below, won't you?

---

 

[Mally]

Thanks for your help Max. That did the trick!

 

[Max]

Welcome, Mally

 

[Mally]

Thanks again Max. One last thing i've just realised I need.

If the data is as follows
Week 1 = 1, 2, 3, 4, 8 ,10
Week 2 = 2, 4, 5, 7, 8, 9
Week 3 = 4, 6, 7, 8, 9, 10

Can the results table be displayed as the following so that there are no
gaps at all in the numbers?

week
8
7
6
5
4
3 3 3 3
2 2 2 3 2 2 3
1 1 1 1 1 2 3 2 1 2 1
1 2 3 4 5 6 7 8 9 10 num

 

[Max]

.. One last thing i've just realised I need.
Pl put in as a fresh new post in future ..

My farewell present for you here:
http://www.freefilehosting.net/download/3ie8a
Mally_2.xls

To get Results 1:
In B11: =IF(B1=B$19,ROWS($1:1),"")
B11 copied across/filled down to K18 to cover input range in B1:K18
(that's what we had earlier)

To get Results 2 (collapsed):
In B22, copied across/fill down to K29:
=IF(ROWS($1:1)>COUNT(B$11:B$18),"",INDEX(B$11:B$18,SMALL(B$11:B$18,ROWS($1:1))))

To get Results 3 (flipped):
In B33, copied across/fill down to K40:
=INDEX(A$22:A$29,9-ROWS($1:1))

 

[Mally]

Whoooo Hoooo! Thanks Max for your help. That's exactly what I needed.

Thank you

Mally

 

[Max]

Welcome, cheers.