Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW CAN THIS BE DONE?
I need a Function to copy a series of Rows (each four Rows Together)
dependent on a criteria on the same sheet and paste it in another Sheet. EXAMPLE: 111 222 333 444 USD # 111 222 333 555 # 111 333 444 555 EUR # 111 222 333 444 # 111 333 444 555 EUR # 111 222 333 777 # 111 333 444 112 JPY # 111 222 333 000 # I would like copy each 4 rows (each 4 starting with the row containing the currency) and paste them on a different sheet dependent on the currency. So if I had 2 groups(4 rows each) in EUR(like the example above) i would like to past them in the "EUR" Sheet. And the ones containg the USD to the "USD" and the same for JPY (and other currencies as well). I hope I was able to explain my intention..and thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW CAN THIS BE DONE?
One play which automates it using non-array formulas ..
Illustrated in this sample: http://www.freefilehosting.net/download/3edgh AutoCopy Lines by Currency into its Own Sht.xls In sheet: WS1 (the "master") Assume source data as posted is in cols A to E, data in row2 down, with the key col = col E (Currency) List the currencies in K1 across: USD, EUR, JPY, etc (can be in any order) Put in J2: =E2 Put in J3: =IF(E3="",J2,E3) Copy J3 down to the last row of source data Put in K2: =IF($J2=K$1,ROW(),"") Copy K2 across as far as required, fill down 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. Then, in a new sheet named after one of the currencies, eg: USD With the same col headers pasted into A1:D1 Put in A2: =IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MA TCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV $1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN, WS1!$K$1:$IV$1,0)),0))) Copy A2 across to D2, fill down to say, D100, to cover the max expected extent for any currency. Cols A to D will return only the lines for the currency: USD from "WS1", with all lines neatly bunched at the top Now, just make a copy of the sheet: USD, rename it as the next currency: EUR, and you'd get the results for that currency. Repeat the copy rename sheet process to get the rest of the currency sheets (a one-time job) as required. Adapt to suit .. P/s: You should have used a more meaningful subject line, and never use all CAPS - it's considered impolite -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ... I need a Function to copy a series of Rows (each four Rows Together) dependent on a criteria on the same sheet and paste it in another Sheet. EXAMPLE: 111 222 333 444 USD # 111 222 333 555 # 111 333 444 555 EUR # 111 222 333 444 # 111 333 444 555 EUR # 111 222 333 777 # 111 333 444 112 JPY # 111 222 333 000 # I would like copy each 4 rows (each 4 starting with the row containing the currency) and paste them on a different sheet dependent on the currency. So if I had 2 groups(4 rows each) in EUR(like the example above) i would like to past them in the "EUR" Sheet. And the ones containg the USD to the "USD" and the same for JPY (and other currencies as well). I hope I was able to explain my intention..and thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW CAN THIS BE DONE?
On Mar 31, 1:26*pm, "Max" wrote:
One play which automates it using non-array formulas .. Illustrated in this sample:http://www.freefilehosting.net/download/3edgh AutoCopy Lines by Currency into its Own Sht.xls In sheet: WS1 (the "master") Assume source data as posted is in cols A to E, data in row2 down, with the key col = col E (Currency) List the currencies in K1 across: USD, EUR, JPY, etc (can be in any order) Put in J2: =E2 Put in J3: =IF(E3="",J2,E3) Copy J3 down to the last row of source data Put in K2: =IF($J2=K$1,ROW(),"") Copy K2 across as far as required, fill down 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. Then, in a new sheet named after one of the currencies, eg: USD With the same col headers pasted into A1:D1 Put in A2: =IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:*A1))),"",INDEX(WS1!A:A,M ATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$I V*$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WS N,WS1!$K$1:$IV$1,0)),0))) Copy A2 across to D2, fill down to say, D100, to cover the max expected extent for any currency. Cols A to D will return only the lines for the currency: USD from "WS1", with all lines neatly bunched at the top Now, just make a copy of the sheet: USD, rename it as the next currency: EUR, and you'd get the results for that currency. Repeat the copy rename sheet process to get the rest of the currency sheets (a one-time job) as required. *Adapt to suit .. P/s: You should have used a more meaningful subject line, and never use all CAPS - it's considered impolite -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik wrote in message ... I need a Function to copy a series of Rows (each four Rows Together) dependent on a criteria on the same sheet and paste it in another Sheet. EXAMPLE: 111 *222 *333 *444 * USD # 111 *222 *333 *555 # 111 *333 *444 *555 * EUR # 111 *222 *333 *444 # 111 *333 *444 *555 * EUR # 111 *222 *333 *777 # 111 *333 *444 *112 * JPY # 111 *222 *333 *000 # I would like copy each 4 rows (each 4 starting with the row containing the currency) and paste them on a different sheet dependent on the currency. So if I had 2 groups(4 rows each) in EUR(like the example above) i would like to past them in the "EUR" Sheet. And the ones containg the USD to the "USD" and the same for JPY (and other currencies as well). I hope I was able to explain my intention..and thank you in advance.- Hide quoted text - - Show quoted text - I really apreciate your reply, it was almost perfect (check below to see why). As for the CAPS thing, well believe it or not it wasn't intentional(had the caps on by mistake)..and truly get your point (i know how lame such things tend to be) :) By the way this is around 99% of what I really need.. Still there is a little something you missed and it can be seen in the following: My request: EXAMPLE: 111 222 333 444 USD # 111 222 333 555 # 111 333 444 555 EUR # 111 222 333 444 # 111 333 444 555 EUR # 111 222 333 777 # 111 333 444 112 JPY # 111 222 333 000 # Your results: EXAMPLE: 111 222 333 444 USD # 0 0 0 111 222 333 555 # 0 0 0 111 333 444 555 EUR # 0 0 0 111 222 333 444 # 0 0 0 111 333 444 555 EUR # 0 0 0 111 222 333 777 # 0 0 0 111 333 444 112 JPY # 0 0 0 111 222 333 000 # 0 0 0 Eventhough this was really great and really inelligable(I really appreciate your algorithm) yet the zeros halt the whole process. I apreciate you coming back on this one and helping me with this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW CAN THIS BE DONE?
.. yet the zeros halt the whole process ..
The zeros returned are due to blank source cells. That's just the way Excel calculates blank cells in formulas, eg: =Sheet2!A1 will return a zero if Sheet2's A1 is blank 1. A simple way out, if it's more just for a neat look in the sheet is to switch off zeros display via clicking Tools Options View tab Uncheck "zero values" ok 2. If (1) is not feasible, then replace the formula in A2 with this: =IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:A1))),"",IF(INDEX(WS1!A:A ,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1: $IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(W SN,WS1!$K$1:$IV$1,0)),0))=0,"",INDEX(WS1!A:A,MATCH (SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1, 0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1 !$K$1:$IV$1,0)),0)))) Recopy across/fill down, etc as before The above is essentially just adding another IF trap for zero returns by INDEX(...) to return blanks: "" instead -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW CAN THIS BE DONE?
On Apr 3, 4:25*pm, "Max" wrote:
.. yet the zeros halt the whole process .. The zeros returned are due to blank source cells. That's just the way Excel calculates blank cells in formulas, eg: =Sheet2!A1 will return a zero if Sheet2's A1 is blank 1. A simple way out, if it's more just for a neat look in the sheet is to switch off zeros display via clicking Tools Options View tab Uncheck "zero values" ok 2. If (1) is not feasible, then replace the formula in A2 with this: =IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:*A1))),"",IF(INDEX(WS1!A: A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1 :*$IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH (WSN,WS1!$K$1:$IV$1,0)),0))*=0,"",INDEX(WS1!A:A,MA TCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV $1,*0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN ,WS1!$K$1:$IV$1,0)),0)))) Recopy across/fill down, etc as before The above is essentially just adding another IF trap for zero returns by INDEX(...) to return blanks: "" instead -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- * Thanks again for the quick and excellet reply..This is exactly what I needed... PS: (About the zero's) I wasn't trying to be picky and wasn't for a neat look. I am doing this in order to be able to copy it into a system which doesn't allow text modifying...thus I need the output to be 100% exact and without any zeros.. by the way both solutions apply in this case...Thanks again :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW CAN THIS BE DONE?
Welcome, good to hear that
Thanks for posting back -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- <xbiggyl... wrote Thanks again for the quick and excellet reply..This is exactly what I needed... PS: (About the zero's) I wasn't trying to be picky and wasn't for a neat look. I am doing this in order to be able to copy it into a system which doesn't allow text modifying...thus I need the output to be 100% exact and without any zeros.. by the way both solutions apply in this case...Thanks again :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|