Excel Sorting help

I hope someone can help me with this as simply as possible. I would
prefer to stay away from VB (don't know how to use it very well), but I
will do what I need.

I have a excel sheet (Sht1) that lists out the assets owned by a
family. Column A is the Asset Name (IRA 1, IRA 2). And Column C
indicates which family member owns the Asset (John, Jane)

I need to keep this list intact and unsorted on Sht1 - 'Sht1' is an
input sheet that will vary in length. However, I want to be able to
relist all of John's assets on 'Sht2' and all of Jane's assets on
'Sht3'.

Sht1:

___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| IRA 2 AFD
4| Roth 1 JQD
5| Roth 2 JQD
6| Brok 1 JQD
7| Brok 2 AFD

**I want this sheet to be a 'cell reference' of inputs for two other
sheets.

I want 'Sht2' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| Roth 1 JQD
4| Roth 2 JQD
5| Brok 1 JQD



and 'Sht3' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 2 AFD
3| Brok 2 AFD

Here's the major twist, the number of owners will always be two (JQD or
ASD), but the number of Assets and the quantity owned by each owner can
vary. Is there a function that will place them on the separate pages?

I'd try to keep my data on one worksheet. Then use Data|Filter|autofilter to
display/print what I want.

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

I hope someone can help me with this as simply as possible. I would
prefer to stay away from VB (don't know how to use it very well), but I
will do what I need.

I have a excel sheet (Sht1) that lists out the assets owned by a
family. Column A is the Asset Name (IRA 1, IRA 2). And Column C
indicates which family member owns the Asset (John, Jane)

I need to keep this list intact and unsorted on Sht1 - 'Sht1' is an
input sheet that will vary in length. However, I want to be able to
relist all of John's assets on 'Sht2' and all of Jane's assets on
'Sht3'.

Sht1:

___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| IRA 2 AFD
4| Roth 1 JQD
5| Roth 2 JQD
6| Brok 1 JQD
7| Brok 2 AFD

**I want this sheet to be a 'cell reference' of inputs for two other
sheets.

I want 'Sht2' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| Roth 1 JQD
4| Roth 2 JQD
5| Brok 1 JQD

and 'Sht3' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 2 AFD
3| Brok 2 AFD

Here's the major twist, the number of owners will always be two (JQD or
ASD), but the number of Assets and the quantity owned by each owner can
vary. Is there a function that will place them on the separate pages?

--

Dave Peterson

Thanks.
I can infer you are telling me there is no easy way to do it.
Unfortunately, printing and displaying (IOW, using autofilter) are not
my main concern. The info on Sht2 & Sht3 go on to complete 15 other
sheets. Furthermore, the cells comprise both partial and complete
functions/formulas on the other sheets that must display at the same
time. As a matter of fact, some of the functions require that the two
owners assets be added together (both partially and wholy).

It is imperative I be able to isolate the data into Sht2 & Sht3, not
just a single Sheet that can display different batches of info.

Thanks though. I added the websites to my "bank of knowledge"

.. It is imperative I be able to isolate the data into Sht2 & Sht3, not
just a single Sheet that can display different batches of info.

Here's a non-array formulas play which can deliver the desired results
automatically into separate sheets by asset owner as the source input sheet
"Sht1" is continually updated ..

A sample construct is available at:
AutoCopy data to separate shts by key col value.xls
http://www.savefile.com/files/37091

Assume the master list is input in sheet named: Sht1
in cols A to B, headers in row1, data from row2 down
Col B = Asset Owner (eg: JQD, AFD, etc) - this is the key col

Using empty cols to the right of the data, say cols K onwards
List the unique asset owners in K1 across, in any order, eg: JQD, AFD, etc.
Ensure these are consistent with the values under "Asset Owner"

Put in K2: =IF($B2="","",IF($B2=K$1,ROW(),""))
Copy K2 across and fill down to cover the max expected extent of source data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named: JQD
With the same col headers in A1:B1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Sht1!$J:$J,,MATCH(WSN,Sht 1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(Sht1!A:A ,MATCH(SMALL(OFFSET(Sht1!$J:$J,,MATCH(WSN,Sht1!$K$ 1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Sht1!$J:$J,,MATC H(WSN,Sht1!$K$1:$IV$1,0)),0)))

Copy A2 across to B2, fill down by the smallest extent sufficient to cover
all the cases expected for any one asset owner, say down to D10.

Cols A & B will return only the lines for the asset owner: JQD from Sht1
with all lines neatly bunched at the top - exactly the results desired. Now,
just make a copy of the sheet: JQD, rename it as: AFD, and you'd get the
results for AFD. Repeat as required for the rest of the asset owners (a
one-time job).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Orig post:
wrote:
I hope someone can help me with this as simply as possible. I would
prefer to stay away from VB (don't know how to use it very well), but I
will do what I need.

I have a excel sheet (Sht1) that lists out the assets owned by a
family. Column A is the Asset Name (IRA 1, IRA 2). And Column C
indicates which family member owns the Asset (John, Jane)

I need to keep this list intact and unsorted on Sht1 - 'Sht1' is an
input sheet that will vary in length. However, I want to be able to
relist all of John's assets on 'Sht2' and all of Jane's assets on
'Sht3'.

Sht1:

___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| IRA 2 AFD
4| Roth 1 JQD
5| Roth 2 JQD
6| Brok 1 JQD
7| Brok 2 AFD

**I want this sheet to be a 'cell reference' of inputs for two other
sheets.

I want 'Sht2' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| Roth 1 JQD
4| Roth 2 JQD
5| Brok 1 JQD

and 'Sht3' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 2 AFD
3| Brok 2 AFD

Here's the major twist, the number of owners will always be two (JQD or
ASD), but the number of Assets and the quantity owned by each owner can
vary. Is there a function that will place them on the separate pages?

Thanks

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Thanks

Hi Max

Very nice solution!
I will tuck that one away for future use if you don't mind.

--
Regards

Roger Govier


"Max" wrote in message
...
.. It is imperative I be able to isolate the data into Sht2 & Sht3,
not
just a single Sheet that can display different batches of info.

Here's a non-array formulas play which can deliver the desired results
automatically into separate sheets by asset owner as the source input
sheet
"Sht1" is continually updated ..

A sample construct is available at:
AutoCopy data to separate shts by key col value.xls
http://www.savefile.com/files/37091

Assume the master list is input in sheet named: Sht1
in cols A to B, headers in row1, data from row2 down
Col B = Asset Owner (eg: JQD, AFD, etc) - this is the key col

Using empty cols to the right of the data, say cols K onwards
List the unique asset owners in K1 across, in any order, eg: JQD, AFD,
etc.
Ensure these are consistent with the values under "Asset Owner"

Put in K2: =IF($B2="","",IF($B2=K$1,ROW(),""))
Copy K2 across and fill down to cover the max expected extent of
source data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname
in
formulas. It will auto-extract the sheetname implicitly. Technique
came from
a post by Harlan.

In a new sheet named: JQD
With the same col headers in A1:B1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Sht1!$J:$J,,MATCH(WSN,Sht 1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(Sht1!A:A ,MATCH(SMALL(OFFSET(Sht1!$J:$J,,MATCH(WSN,Sht1!$K$ 1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Sht1!$J:$J,,MATC H(WSN,Sht1!$K$1:$IV$1,0)),0)))

Copy A2 across to B2, fill down by the smallest extent sufficient to
cover
all the cases expected for any one asset owner, say down to D10.

Cols A & B will return only the lines for the asset owner: JQD from
Sht1
with all lines neatly bunched at the top - exactly the results
desired. Now,
just make a copy of the sheet: JQD, rename it as: AFD, and you'd get
the
results for AFD. Repeat as required for the rest of the asset owners
(a
one-time job).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Orig post:
wrote:
I hope someone can help me with this as simply as possible. I would
prefer to stay away from VB (don't know how to use it very well), but
I
will do what I need.

I have a excel sheet (Sht1) that lists out the assets owned by a
family. Column A is the Asset Name (IRA 1, IRA 2). And Column C
indicates which family member owns the Asset (John, Jane)

I need to keep this list intact and unsorted on Sht1 - 'Sht1' is an
input sheet that will vary in length. However, I want to be able to
relist all of John's assets on 'Sht2' and all of Jane's assets on
'Sht3'.

Sht1:

___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| IRA 2 AFD
4| Roth 1 JQD
5| Roth 2 JQD
6| Brok 1 JQD
7| Brok 2 AFD

**I want this sheet to be a 'cell reference' of inputs for two other
sheets.

I want 'Sht2' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 1 JQD
3| Roth 1 JQD
4| Roth 2 JQD
5| Brok 1 JQD

and 'Sht3' to look like:
___A___ ___B___
1| Asset Name Asset Owner
2| IRA 2 AFD
3| Brok 2 AFD

Here's the major twist, the number of owners will always be two (JQD
or
ASD), but the number of Assets and the quantity owned by each owner
can
vary. Is there a function that will place them on the separate pages?

Most welcome, and thanks for the compliments, Roger!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roger Govier" wrote:
Hi Max

Very nice solution!
I will tuck that one away for future use if you don't mind.

--
Regards

Roger Govier

Okay. Little more help please.

I was able to setup Sht1 and get it to do everything as you instructed.


However, I cannot get the JQD page to produce formula results. I cut
and pasted all formulas to eliminate typos and the Sht1 formulas work
excellently.

I have never dealt with the "InsertNameDefine" action so I doubt that
I executed that part of your instructions properly. Do I need to
highlight the whole sheet, just part of it, none of it, etc. etc.

Sorry for my ignorance, but could you please expound on this step.
Also, for curiousity sake, what does this step do?

Thanks

wrote
.. I have never dealt with the "InsertNameDefine" action so I doubt that
I executed that part of your instructions properly. Do I need to
highlight the whole sheet, just part of it, none of it, etc. etc.

It doesn't matter, just click on any cell in any sheet then do the steps
given earlier, viz:

Click Insert Name Define (this brings up the "Define Name" dialog box)
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

Sorry for my ignorance, but could you please expound on this step.
Also, for curiousity sake, what does this step do?

As explained earlier .. The above defines WSN as a name we can use to refer
to the sheetname in formulas. It will auto-extract the sheetname implicitly.
Technique came from a post by Harlan.

WSN is actually a formula which simply returns the sheetname. After setting
it up as above, you can easily test this by putting in any cell in any
sheet: =WSN. When you press ENTER to confirm the formula, it will return the
sheetname in that cell.

Try it again ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---