Evaluate all data on 1st sheet and only bring lines with a specific criteria to 2nd sheet

Posted by sandy on February 02, 2002 2:49 AM

I posted a different version of this on 1st Feb but
the responder (unhelpfully) replaced my post with someone else's -so I'll have to try and explain it all again !

1st sheet is a list (21 columns/5000lines )of sales product data, ("data") mostly text. All this data pertains to 3 different sales areas:- "POLH", "POP", "POH" (shown in column R)

I have a sheet each for POLH,POP,POH set up in exactly the same format as the 'data' sheet.
I need the (say POLH) sheet to show only the lines pertaining to POLH from those listed on the data sheet.

For example, this formula works - in F5 of POLH sheet it shows the data contained in F5 of the "data" sheet

=IF(Data!$R$5:Data!$R$5000="POLH",Data!$F5)

Trouble is, because POLH does appear in R5:R5000
(although not necessarily on every line) when I fill
this formula down the cells show the data exactly as listed on the "data" sheet !

I know I could get round this in a more simplistic way
by returning blank cells if POLH isnt there, but I don't want the 3 "area" sheets to show loads of blank cells, or be 5000 lines long.

I know this is a bit long winded - but in short I'm
looking for a formula (or macro?) that says :-

Go and have a look at all of the 'data' sheet, If "POLH" shows up in column R return cell xxx
on that line. If POLH isn't shown on that line ignore it.(report if POLH, but not if POP or POH)

I've tried all sorts of permuatations and types of formula ...There must be a way to do this..can anyone
help? !

Posted by Aladin Akyurek on February 02, 2002 4:58 AM

This is what I'd do.

Insert 3 empty rows before the label row of data. I'll assume that you have 21 such labels.
Copy the label in column R (that's the column which has the sales area values like POHL, POP, or POH) to A1.

In A2 enter: POHL

Activate the cell immediately underneath of the label cell in A (I'm NOT talking about A2!).

Activate Data|Filter|Advanced Filter.

In the Advanced Filter window:

check "Copy to another location";
check the value in the List range box: it should have a range that covers all of your data;
enter for Critera range: $A$1:$A$2
enter for Copy to: $W$1;
click OK.

Copy what you see from $W$1 on to the POHL sheet.

Repeat this procedure for other areas after deleting everything from $W$1 on the data sheet.

If you had in mind just one time operation, you're done. If you need to keep the data sheet and update the area sheets, try to record a macro while you're running thru the above procedure.

=============

Posted by sandy on February 02, 2002 8:38 AM

Thanks for taking the time to respond to this Aladin - it's fantastic - works a treat !

I elaborated slightly on your idea - by filtering the 3 different areas to all show (east of W!)
and recorded macros doing this.
I then got the 3 individual sheets to pick up the
filtered data where it appeared on the data sheet.
Saved all that copy/paste stuff - and I can re-use the 'template' using different data.
I did notice that the 'Copy to' cells have to be blank !

Once again, thank you VERY much for this !!

Sandy