|
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? |
Excel Sorting help
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" |
Excel Sorting help
.. 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? |
Excel Sorting help
Thanks
|
Excel Sorting help
You're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Thanks |
Excel Sorting help
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? |
Excel Sorting help
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 |
Excel Sorting help
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 |
Excel Sorting help
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 --- |
Excel Sorting help
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 [insertnamedefine] 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. |
Excel Sorting help
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. -- Regards Roger Govier wrote in message ups.com... 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 [insertnamedefine] 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. |
Excel Sorting help
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 |
Excel Sorting help
I just tried InsertNameDefine 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. |
Excel Sorting help
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. -- Regards Roger Govier wrote in message ups.com... I just tried InsertNameDefine 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. |
Excel Sorting help
Okay, I am going to try a new approach. Sorry if I wasted anyone's time
befo 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 |
Excel Sorting help
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. |
Excel Sorting help
wrote:
... 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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
Excel Sorting help
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... 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. |
Excel Sorting help
Sorry, I didn't know it was having problems. I re-uploaded he
http://www.flypicture.com?display=updone&id=r9DylKnR Hope you have some ideas Max. Thanks |
Excel Sorting help
Here's a suggested way working along similar lines as earlier in this thread ..
See sample file at: http://cjoint.com/?jiq1Y25hON Nate_1.xls In Input Asset Ownership: List the 3 owners across in K3:M3, consistent with the values in "Owner" col, ie: 1, 2, 1&2 In K4: =IF($C4="","",IF($C4=K$3,ROW(),"")) K4 copied across to M4, filled down to M23 In Asset Ownership: In A6: =IF(B6="","",ROW(A1)) In B6: =IF(ROW(A1)COUNT('Input Asset Ownership'!$K$4:$K$23),"",INDEX('Input Asset Ownership'!A$4:A$23,MATCH(SMALL('Input Asset Ownership'!$K$4:$K$23,ROW(A1)),'Input Asset Ownership'!$K$4:$K$23,0))) In C6: =IF(ROW(A1)COUNT('Input Asset Ownership'!$K$4:$K$23),"",INDEX('Input Asset Ownership'!D$4:D$23,MATCH(SMALL('Input Asset Ownership'!$K$4:$K$23,ROW(A1)),'Input Asset Ownership'!$K$4:$K$23,0))) Copy C6 to D6 In E6: =IF(OR(C6="",D6=""),"",C6-D6) Select A6:E6, copy down to E18 (This settles it for assets owned by 1) Then in a similar vein for assets owned by 2 .. In A21: =IF(B21="","",ROW(A1)+MAX($A$6:$A$18)) The above will auto-continue the numbering from the assets owned by 1 In B21: =IF(ROW(A1)COUNT('Input Asset Ownership'!$L$4:$L$23),"",INDEX('Input Asset Ownership'!A$4:A$23,MATCH(SMALL('Input Asset Ownership'!$L$4:$L$23,ROW(A1)),'Input Asset Ownership'!$L$4:$L$23,0))) In C21: =IF(ROW(A1)COUNT('Input Asset Ownership'!$L$4:$L$23),"",INDEX('Input Asset Ownership'!D$4:D$23,MATCH(SMALL('Input Asset Ownership'!$L$4:$L$23,ROW(A1)),'Input Asset Ownership'!$L$4:$L$23,0))) Copy C21 to D21 In E21: =IF(OR(C21="",D21=""),"",C21-D21) Select A21:E21, copy down to E26 Finally, for assets owned by 1&2 .. In A29: =IF(B29="","",ROW(A1)+MAX($A$21:$A$26)) The above will auto-continue the numbering from the assets owned by 2 In B29: =IF(ROW(A1)COUNT('Input Asset Ownership'!$M$4:$M$23),"",INDEX('Input Asset Ownership'!A$4:A$23,MATCH(SMALL('Input Asset Ownership'!$M$4:$M$23,ROW(A1)),'Input Asset Ownership'!$M$4:$M$23,0))) In C29: =IF(ROW(A1)COUNT('Input Asset Ownership'!$M$4:$M$23),"",INDEX('Input Asset Ownership'!D$4:D$23,MATCH(SMALL('Input Asset Ownership'!$M$4:$M$23,ROW(A1)),'Input Asset Ownership'!$M$4:$M$23,0))) Copy C29 to D29 In E29: =IF(OR(C21="",D21=""),"",C21-D21) Select A29:E29, copy down to E35 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Nate's clarification: .... 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. |
Excel Sorting help
Pure genius.
Thanks everyone for the help especially Max. |
Excel Sorting help
You're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Pure genius. Thanks everyone for the help especially Max. |
| All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com