|
Frequency tracker
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,$I2 0=B$10),$A20,"") Any ideas anyone? |
Frequency tracker
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote: 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,$I2 0=B$10),$A20,"") Any ideas anyone? |
Frequency tracker
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 "Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote: 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,$I2 0=B$10),$A20,"") Any ideas anyone? |
Frequency tracker
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. "Mally" wrote: 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 "Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote: 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,$I2 0=B$10),$A20,"") Any ideas anyone? |
Frequency tracker
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? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Frequency tracker
Thanks for your help Max. That did the trick!
"Max" wrote: 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? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Frequency tracker
Welcome, Mally
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote in message ... Thanks for your help Max. That did the trick! |
Frequency tracker
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" wrote: Welcome, Mally -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote in message ... Thanks for your help Max. That did the trick! |
Frequency tracker
.. One last thing i've just realised I need.
Pl put in as a fresh new post in future .. My farewell present for you he 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)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote in message ... 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 |
Frequency tracker
Whoooo Hoooo! Thanks Max for your help. That's exactly what I needed.
Thank you Mally "Max" wrote: .. One last thing i've just realised I need. Pl put in as a fresh new post in future .. My farewell present for you he 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)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote in message ... 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 |
Frequency tracker
Welcome, cheers.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mally" wrote in message ... Whoooo Hoooo! Thanks Max for your help. That's exactly what I needed. Thank you Mally |
| All times are GMT +1. The time now is 11:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com