|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 27th, 2009, 08:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Function(s) should I use?
Hi All,
I want to generate 5 lists using the preference column from raw data which is in the following format: Preference Name Club 1 A Smith NY 2 G Murphy WN 3 S Rogers MA 4 B Wall CN 5 C Hill DW 1 D Thomas ON 2 W Shatner CA 3 E Jones MS 4 L Long SC 5 J Downe NC Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- Marbol 
|
|
#2
December 28th, 2009, 06:36 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which Function(s) should I use?
On Dec 27, 2:29*pm, Marbol wrote:
Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference *Name * * * * *Club 1 * * * * * * * *A Smith * * * NY 2 * * * * * * * *G Murphy * *WN 3 * * * * * * * *S Rogers * * MA 4 * * * * * * * *B Wall * * * * CN 5 * * * * * * * *C Hill * * * * * DW 1 * * * * * * * *D Thomas * *ON 2 * * * * * * * *W Shatner * CA 3 * * * * * * * *E Jones * * * *MS 4 * * * * * * * *L Long * * * * SC 5 * * * * * * * *J Downe * * *NC *Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- * Marbol are you familiar with the autofilter feature? Im not sure if that will do exactly what you are asking, but it would give you a way to see only the data in rows that meet certain criteria in your search. Try the help listing for autofilter. It may do the trick.
|
|
#3
December 28th, 2009, 12:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which Function(s) should I use?
On Dec 27, 2:29*pm, Marbol wrote:
Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference *Name * * * * *Club 1 * * * * * * * *A Smith * * * NY 2 * * * * * * * *G Murphy * *WN 3 * * * * * * * *S Rogers * * MA 4 * * * * * * * *B Wall * * * * CN 5 * * * * * * * *C Hill * * * * * DW 1 * * * * * * * *D Thomas * *ON 2 * * * * * * * *W Shatner * CA 3 * * * * * * * *E Jones * * * *MS 4 * * * * * * * *L Long * * * * SC 5 * * * * * * * *J Downe * * *NC *Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- * Marbol If the data in the Preference column is named Prefs, the data in the Name column is named Names and the data in the Club column is named Clubs, then... =IF(ROW($A1)COUNTIF(Prefs,(COLUMN(A$1)+1)/2),"",INDEX(Names,SMALL(IF (Prefs=(COLUMN(A$1)+1)/2,ROW(Prefs)-MIN(ROW(Prefs))+1,""),ROW($A1)))) array entered into a cell (Ctrl+Shift+Enter key combination) and... =IF(ROW($A1)COUNTIF(Prefs,(COLUMN(A$1)+1)/2),"",INDEX(Clubs,SMALL(IF (Prefs=(COLUMN(A$1)+1)/2,ROW(Prefs)-MIN(ROW(Prefs))+1,""),ROW($A1)))) array entered into the cell immediately to its right can be filled to the right for a total of 10 columns, then filled down to accommodate all of the data. Like this http://www.4shared.com/file/18317425...nce_Lists.html Ken Johnson
|
|
#4
December 29th, 2009, 03:59 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which Function(s) should I use?
Hi,
You could try this. Create a pivot table with preference in the report filter (page field area), name in the row area, club in the column area and club (again) in the data area. Now click on the report filter cell and then click on Show Pages. This will create 5 sheets with one preference number per sheet -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Marbol" wrote in message ... Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference Name Club 1 A Smith NY 2 G Murphy WN 3 S Rogers MA 4 B Wall CN 5 C Hill DW 1 D Thomas ON 2 W Shatner CA 3 E Jones MS 4 L Long SC 5 J Downe NC Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- Marbol
|
|
#5
December 29th, 2009, 07:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which Function(s) should I use?
Ken,
Thanks for your help it was exactly what I was looking for. However, if I insert additional data and drag down the formulae it doesn't populate the columns with the addtional data. Any thoughts on how to fix this? I will have a final list of approx 400 - 500 names. Thanks -- Marbol "Marbol" wrote: Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference Name Club 1 A Smith NY 2 G Murphy WN 3 S Rogers MA 4 B Wall CN 5 C Hill DW 1 D Thomas ON 2 W Shatner CA 3 E Jones MS 4 L Long SC 5 J Downe NC Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- Marbol
|
|
#6
December 30th, 2009, 12:09 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which Function(s) should I use?
On Dec 29, 1:09*pm, Marbol wrote:
Ken, Thanks for your help it was exactly what I was looking for. However, if I insert additional data and drag down the formulae it doesn't populate the columns with the addtional data. Any thoughts on how to fix this? I will have a final list of approx 400 - 500 names. Thanks -- Marbol "Marbol" wrote: Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference *Name * * * * *Club 1 * * * * * * * *A Smith * * * NY 2 * * * * * * * *G Murphy * *WN 3 * * * * * * * *S Rogers * * MA 4 * * * * * * * *B Wall * * * * CN 5 * * * * * * * *C Hill * * * * * DW 1 * * * * * * * *D Thomas * *ON 2 * * * * * * * *W Shatner * CA 3 * * * * * * * *E Jones * * * *MS 4 * * * * * * * *L Long * * * * SC 5 * * * * * * * *J Downe * * *NC *Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- * Marbol Hi Marbol, The first thing that will cause the formulas to produce an incomplete result is... The array formulas in columns D, F, H, J & L refer to two named ranges. These are Prefs and Names. The array formulas in columns E, G, I, K & M refer also refer to two named ranges. These are Prefs and Clubs. On the sheet I uploaded Prefs, Names and Clubs were defined by the following... Prefs:=Sheet1!$A$2:$A$11 Names:=Sheet1!$B$2:$B$11 Clubs:=Sheet1!$C$2:$C$11 While Prefs, Names and Clubs are defined as above, the formulas in columns D to M will only work on those 10 rows. To get the array formulas to work on 400 to 500 rows the named ranges (Prefs, Names and Clubs) need to be redefined to include that many rows. The simplest way to do that is to change the 11 in each of the defining formulas to some suitably large number, say 600. This needs to be done in the "Refers to:" box at the bottom of the "Define Name" dialog. This dialog is shown by going Insert|Name|Define... Another way of making the named ranges the correct sizes for the data is to make them Dynamic Named Ranges so that as data is added or subtracted the formulas defining them automatically adjust to fit the current rows of data. The following formulas entered into the "Refers to:" box at the bottom of the "Define Name" dialog will respectively make Prefs, Names and Clubs Dynamic Named Ranges... =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A),1) =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B),1) =OFFSET(Sheet1!$C$2,,,COUNTA(Sheet1!$C:$C),1) If you use Dynamic Named Ranges you don't have to worry about accidentally adding too much data to the sheet and you can be confident that your formulas will give complete results. One thing to be aware of though is that there must be no gaps in the Prefs, Names and Clubs columns. The OFFSET formulas used to define the ranges cannot properly account for intervening empty rows. If intervening empty rows are unavoidable then different formulas would need to be used to define the named ranges. The second thing that will cause the formulas to produce an incomplete result is... The array formulas in columns D to M need to be copied down the sheet far enough so that they can accommodate the the largest possible preference list. With 400 to 500 rows of data, the largest preference group could require 400 to 500 rows, but this is unlikely since it is unlikely that everyone will have the same preference value. Neverless, if you copy the formulas down to be level with the bottom of the Prefs, Names and Clubs data you can be sure the results are complete. Another approach is to use a formula to compare the number of rows in the largest of the preference lists with the frequency of the most popular preference. If the number of rows in the largest of the preference lists is less than the frequency of the most popular preference then the array formulas have not been filled down far enough and action should be taken. A formula that counts the number of rows in the largest of the preference lists is... =MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F$2:$F $65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J$2:$J $65535"")),SUMPRODUCT(--($L$2:$L$65535"")) and a formula that returns the frequency of the most popular preference is... =MAX(COUNTIF(Prefs,Prefs)) which is an array. You could either use these formulas in a cell so that a warning is returned when the array formulas are not filled down far enough... =IF(MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F$2:$F $65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J$2:$J $65535"")),SUMPRODUCT(--($L$2:$L$65535"")))MAX(COUNTIF (Prefs,Prefs)),"Fill Down Further","") or use them to conditionally format say the heading cells to change colour... Formula Is:... =MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F $2:$F$65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J $2:$J$65535"")),SUMPRODUCT(--($L$2:$L$65535""))MAX(COUNTIF (Prefs,Prefs)) Ken Johnson
|
|
#7
December 31st, 2009, 03:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which Function(s) should I use?
That's brilliant Ken, thanks a million.
I didn't realise that you had defined names in for the source data. I have amended the range and it works perfectly now. Many thanks, your help is much appreciated. -- Marbol "Ken Johnson" wrote: On Dec 29, 1:09 pm, Marbol wrote: Ken, Thanks for your help it was exactly what I was looking for. However, if I insert additional data and drag down the formulae it doesn't populate the columns with the addtional data. Any thoughts on how to fix this? I will have a final list of approx 400 - 500 names. Thanks -- Marbol "Marbol" wrote: Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference Name Club 1 A Smith NY 2 G Murphy WN 3 S Rogers MA 4 B Wall CN 5 C Hill DW 1 D Thomas ON 2 W Shatner CA 3 E Jones MS 4 L Long SC 5 J Downe NC Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- Marbol Hi Marbol, The first thing that will cause the formulas to produce an incomplete result is... The array formulas in columns D, F, H, J & L refer to two named ranges. These are Prefs and Names. The array formulas in columns E, G, I, K & M refer also refer to two named ranges. These are Prefs and Clubs. On the sheet I uploaded Prefs, Names and Clubs were defined by the following... Prefs:=Sheet1!$A$2:$A$11 Names:=Sheet1!$B$2:$B$11 Clubs:=Sheet1!$C$2:$C$11 While Prefs, Names and Clubs are defined as above, the formulas in columns D to M will only work on those 10 rows. To get the array formulas to work on 400 to 500 rows the named ranges (Prefs, Names and Clubs) need to be redefined to include that many rows. The simplest way to do that is to change the 11 in each of the defining formulas to some suitably large number, say 600. This needs to be done in the "Refers to:" box at the bottom of the "Define Name" dialog. This dialog is shown by going Insert|Name|Define... Another way of making the named ranges the correct sizes for the data is to make them Dynamic Named Ranges so that as data is added or subtracted the formulas defining them automatically adjust to fit the current rows of data. The following formulas entered into the "Refers to:" box at the bottom of the "Define Name" dialog will respectively make Prefs, Names and Clubs Dynamic Named Ranges... =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A),1) =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B),1) =OFFSET(Sheet1!$C$2,,,COUNTA(Sheet1!$C:$C),1) If you use Dynamic Named Ranges you don't have to worry about accidentally adding too much data to the sheet and you can be confident that your formulas will give complete results. One thing to be aware of though is that there must be no gaps in the Prefs, Names and Clubs columns. The OFFSET formulas used to define the ranges cannot properly account for intervening empty rows. If intervening empty rows are unavoidable then different formulas would need to be used to define the named ranges. The second thing that will cause the formulas to produce an incomplete result is... The array formulas in columns D to M need to be copied down the sheet far enough so that they can accommodate the the largest possible preference list. With 400 to 500 rows of data, the largest preference group could require 400 to 500 rows, but this is unlikely since it is unlikely that everyone will have the same preference value. Neverless, if you copy the formulas down to be level with the bottom of the Prefs, Names and Clubs data you can be sure the results are complete. Another approach is to use a formula to compare the number of rows in the largest of the preference lists with the frequency of the most popular preference. If the number of rows in the largest of the preference lists is less than the frequency of the most popular preference then the array formulas have not been filled down far enough and action should be taken. A formula that counts the number of rows in the largest of the preference lists is... =MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F$2:$F $65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J$2:$J $65535"")),SUMPRODUCT(--($L$2:$L$65535"")) and a formula that returns the frequency of the most popular preference is... =MAX(COUNTIF(Prefs,Prefs)) which is an array. You could either use these formulas in a cell so that a warning is returned when the array formulas are not filled down far enough... =IF(MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F$2:$F $65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J$2:$J $65535"")),SUMPRODUCT(--($L$2:$L$65535"")))MAX(COUNTIF (Prefs,Prefs)),"Fill Down Further","") or use them to conditionally format say the heading cells to change colour... Formula Is:... =MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F $2:$F$65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J $2:$J$65535"")),SUMPRODUCT(--($L$2:$L$65535""))MAX(COUNTIF (Prefs,Prefs)) Ken Johnson .
|
|
#8
January 2nd, 2010, 03:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which Function(s) should I use?
On Dec 31 2009, 9:27*am, Marbol
wrote: That's brilliant Ken, thanks a million. I didn't realise that you had defined names in for the source data. I have amended the range and it works perfectly now. Many thanks, your help is much appreciated. -- Marbol "Ken Johnson" wrote: On Dec 29, 1:09 pm, Marbol wrote: Ken, Thanks for your help it was exactly what I was looking for. However, if I insert additional data and drag down the formulae it doesn't populate the columns with the addtional data. Any thoughts on how to fix this? I will have a final list of approx 400 - 500 names. Thanks -- Marbol "Marbol" wrote: Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference *Name * * * * *Club 1 * * * * * * * *A Smith * * * NY 2 * * * * * * * *G Murphy * *WN 3 * * * * * * * *S Rogers * * MA 4 * * * * * * * *B Wall * * * * CN 5 * * * * * * * *C Hill * * * * * DW 1 * * * * * * * *D Thomas * *ON 2 * * * * * * * *W Shatner * CA 3 * * * * * * * *E Jones * * * *MS 4 * * * * * * * *L Long * * * * SC 5 * * * * * * * *J Downe * * *NC *Can you please advise which functions I should use to collate the data, also allowing me to change the preference number without giving me a #N/A error message or blank cell. Many thanks -- * Marbol Hi Marbol, The first thing that will cause the formulas to produce an incomplete result is... The array formulas in columns D, F, H, J & L refer to two named ranges. These are Prefs and Names. The array formulas in columns E, G, I, K & M refer also refer to two named ranges. These are Prefs and Clubs. On the sheet I uploaded Prefs, Names and Clubs were defined by the following... Prefs:=Sheet1!$A$2:$A$11 Names:=Sheet1!$B$2:$B$11 Clubs:=Sheet1!$C$2:$C$11 While Prefs, Names and Clubs are defined as above, the formulas in columns D to M will only work on those 10 rows. To get the array formulas to work on 400 to 500 rows the named ranges (Prefs, Names and Clubs) need to be redefined to include that many rows. The simplest way to do that is to change the 11 in each of the defining formulas to some suitably large number, say 600. This needs to be done in the "Refers to:" box at the bottom of the "Define Name" dialog. This dialog is shown by going Insert|Name|Define... Another way of making the named ranges the correct sizes for the data is to make them Dynamic Named Ranges so that as data is added or subtracted the formulas defining them automatically adjust to fit the current rows of data. The following formulas entered into the "Refers to:" box at the bottom of the "Define Name" dialog will respectively make Prefs, Names and Clubs Dynamic Named Ranges... =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A),1) =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B),1) =OFFSET(Sheet1!$C$2,,,COUNTA(Sheet1!$C:$C),1) If you use Dynamic Named Ranges you don't have to worry about accidentally adding too much data to the sheet and you can be confident that your formulas will give complete results. One thing to be aware of though is that there must be no gaps in the Prefs, Names and Clubs columns. The OFFSET formulas used to define the ranges cannot properly account for intervening empty rows. If intervening empty rows are unavoidable then different formulas would need to be used to define the named ranges. The second thing that will cause the formulas to produce an incomplete result is... The array formulas in columns D to M need to be copied down the sheet far enough so that they can accommodate the the largest possible preference list. With 400 to 500 rows of data, the largest preference group could require 400 to 500 rows, but this is unlikely since it is unlikely that everyone will have the same preference value. Neverless, if you copy the formulas down to be level with the bottom of the Prefs, Names and Clubs data you can be sure the results are complete. Another approach is to use a formula to compare the number of rows in the largest of the preference lists with the frequency of the most popular preference. If the number of rows in the largest of the preference lists is less than the frequency of the most popular preference then the array formulas have not been filled down far enough and action should be taken. A formula that counts the number of rows in the largest of the preference lists is... =MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F$2:$F $65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J$2:$J $65535"")),SUMPRODUCT(--($L$2:$L$65535"")) and a formula that returns the frequency of the most popular preference is... =MAX(COUNTIF(Prefs,Prefs)) which is an array. You could either use these formulas in a cell so that a warning is returned when the array formulas are not filled down far enough... =IF(MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F$2:$F $65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J$2:$J $65535"")),SUMPRODUCT(--($L$2:$L$65535"")))MAX(COUNTIF (Prefs,Prefs)),"Fill Down Further","") or use them to conditionally format say the heading cells to change colour... Formula Is:... =MAX(SUMPRODUCT(--($D$2:$D$65535"")),SUMPRODUCT(--($F $2:$F$65535"")),SUMPRODUCT(--($H$2:$H$65535"")),SUMPRODUCT(--($J $2:$J$65535"")),SUMPRODUCT(--($L$2:$L$65535""))MAX(COUNTIF (Prefs,Prefs)) Ken Johnson . You're welcome Marbol. Ken Johnson
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
