HOW CAN THIS BE DONE?

[xbiggyl]

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.

 

[Max]

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 A11

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,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)))
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

 

[xbiggyl]

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 A11

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,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)))
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

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.

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:


Your results:

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.

 

[Max]

.. 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,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

---

 

[xbiggyl]

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,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
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

 

[Max]

Welcome, good to hear that
Thanks for posting back

---
<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