Copying selected rows to another sheet

T

tacarme

hi friends. I am a newby of excel ( and idem for my english !!!)

Need some help, please.

I have a very big spreadsheet with hundreds of row of customer data.

In the first column starting from column A2 is inserted the customer
code.

After a put a customer code in cell A1. I need :
- to filter all those rows and copy the selection in a second sheet
- and clear the second sheet ONLY every time I change A1 on the first
sheet.

Any help is really appreciated.

Many thanks.
Domenico.
 
M

Max

One formulas play to try ..

Assume the source data is in Sheet1,
cols A to D, data from row2 down,
with the key col being col A (customer codes)

Assume A1 will be reserved for input of the desired customer code

Using an empty col to the right, say col G
Put in G2: =IF(A2="","",IF(A2=$A$1,ROW(),""))
Copy G2 down to say, G100
to cover the max expected data range in cols A to D
(can copy down ahead of expected data)

(Leave G1 empty)

In Sheet2
----------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$G:$G,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G,0)))

Copy A1 across to D1, fill down to D99
(cover the same range as in col G in Sheet1)

Sheet 2 will automatically extract only the rows from Sheet1 with customer
codes equal to that input in cell A1 in Sheet1. These will be bunched
neatly at the top, with blank rows below. Adapt to suit ..
 
T

tacarme

My best thanks in first place !


I created a 'new' work with two sheets to try your solution.
It' s 'excel' lent.

but adding a colunm to my already big work to put in first part of
the solution makes excel recalculate, very slooooly, every time I
change
values in any cell.

I already have my rows filtered. Only need to automate copying to the
second

sheet filtered data whe I change value in A1.

(Sorry for my english.)

Thanks again.
Domenico
 
M

Max

Glad to hear you got it working. In Sheet1, the source table need not be
filtered, it can remain as-is. To control calculation until it is needed,
you might want to consider making the calc mode manual (via Tools > Options
Calculation tab > Check "Manual" > OK). Then the routine data entries, etc
Click to expand...
can proceed smoother. And when all is done, just press F9 to recalc.
 
Top