Excel Sorting help

[kastnna]

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]

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

 

[kastnna]

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"

 

[Guest]

.. 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,Sht1!$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,,MATCH(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).

 

[kastnna]

Thanks

 

[Guest]

You're welcome !

 

[Roger Govier]

Hi Max

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

--
Regards

Roger Govier

.. 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,Sht1!$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,,MATCH(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).

---
Orig post:

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?

 

[Guest]

Most welcome, and thanks for the compliments, Roger!

 

[kastnna]

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 "Insert>Name>Define" 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

 

[Max]

.. I have never dealt with the "Insert>Name>Define" 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 ..

---

 

[kastnna]

Thanks for effort Max, I just can't make it work. I am copy/pasting the
formulas and can get everything to work right up until the "WSN" part.

I tried testing the [insert>name>define] by entering [=WSN] and I get a
#VALUE! error. Perhaps I am entering something incorrectly but I think
I am following the directions exactly.

I have found numerous posts where you have given the exact same answers
to others and they always respond that it worked great.

Maybe I have a setting wrong or something.

I'll keep working on it and any input would be appreciated.

Thanks again.

 

[Roger Govier]

Hi

In case Max is offline by now (for his time zone), then from his
original post

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

You don't put =WSN in the define part.

Max was suggesting you type =WSN into a cell in any worksheet (after you
have created the definition of WSN as above) and it will return the name
of the worksheet.

 

[kastnna]

Right. Can't get it to cooperate.

Under heading "Names in workbook:" WSN
Under heading "Refers to:" =MID(....
[Copied and Pasted the formula AND manually entered it]
then clicked OK.

Went to any random cell (for example cell C1 in Sht1) and input: =WSN

returned the "#VALUE!" error message. If you input =WSN without
defining the name the error message is #NAME?, so it obviously
recognizes something is there.

I can't figure it out.

Since I don't know anything about defining names, perhaps someone can
give me a REALLY simple naming formula that I can test as a base.

Still truckin along....

Nate

 

[kastnna]

I just tried Insert>Name>Define> using WSN as the "Name in workbook"
but just using a simple cell reference as the "refers to" and it worked
like a charm. Returns the value entered into the cell reference
wherever I input =WSN.

 

[Roger Govier]

Aaaah!!!

One small point that we have omitted to say, the Workbook has to have
been saved.
Try saving the workbook, then all should work as described.

 

[kastnna]

Okay, I am going to try a new approach. Sorry if I wasted anyone's time
before: I did learn a handful of new tricks though.

I have uploaded a stripped down version of exactly what I am working on
to:

http://savefile.com/files/44089

As you can see what I specifically need is to get one input list of
info into multiple output lists on the same page. There will always be
three potential owners (1,2, or 1&2) although the number of assets
owned by each will vary.

The extra info on the input sheet goes to other places and should not
be heeded attention.

Lastly, if anyone is feeling particularly genuis, I could use a way to
automatically number the assets on the "asset ownership" sheet. In
other words, IF owner one has three assets listed they are numbered as
such. More importantly, however, I need owner 2's first asset in the
list to pick up at numeral four and continue from there.

I am hesitant to ask someone else to "fix it for me" as that provides
no education. However, I am running out of options and all I can ask is
that my temporary Saint trust that I will learn from the formulas on
the functioning spreadsheet.

Thanks immensely,

Nate

 

[kastnna]

Roger & Max

I am deeply indebted to you. It was all in the SAVE! I got it to work.
If you have free time I would still like someone to look at the
uploaded sheets. They are a good bit different than the potential fix
we have worked out.

 

[Max]

... http://savefile.com/files/44089

Think savefile is still having intermittent problems ..

Use either of the 2 free filehosts listed below to upload your sample and
then post the link to it in response here (the link is generated when you
upload, just copy and paste it here)

http://www.flypicture.com/
http://cjoint.com/index.php

For cjoint.com (it's in French), just click the "Browse" button,
navigate to folder > select the file > Open, then click the button centred
in the page below (labelled "Creer le lien Cjoint") and it'll generate the
link. Then copy & paste the generated link as part and parcel of your
response here.

Kindly note that no attachments should be posted *directly* to the newsgroup

---

 

[Max]

As per earlier response in the other branch ..
think savefile.com is having problems (cannot download)
use either of these 2 alternatives to upload & post the link to your sample:
http://www.flypicture.com/
http://cjoint.com/index.php

 

[kastnna]

Sorry, I didn't know it was having problems. I re-uploaded here:

http://www.flypicture.com?display=updone&id=r9DylKnR

Hope you have some ideas Max.

Thanks