Which Function(s) should I use?

[Marbol]

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

 

[Mark]

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.

 

[Ken Johnson]

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/183174256/401da887/Preference_Lists.html

Ken Johnson

 

[Ashish Mathur]

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]

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

 

[Ken Johnson]

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

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

 

[Marbol]

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,

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

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

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
.

 

[Ken Johnson]

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.

You're welcome Marbol.
Ken Johnson