Automatically copy and paste rows based on cell value

**no.18shirt** · 06-14-2013, 07:03 AM

Hi

At the spanish company I'm working with they have an Excel sheet with all the countries they've made sales in and the exchange rate.
What I want to do is to select only rows in the sheet which are not in Euros. For example below, I want to copy all of the Mexcio, Belize, Venezuela and USA sales into a separate sheet so I can analyse them separately to the €. There are over 3000 lines of sales and they are all mixed up so it's not as simple as just selecting the foreign sales manually as it would take ages.

How can I tell Excel to copy all rows that include an Exchange rate >1? This should get the result I'm after but I just need to know how to do it.

Thanks,

Please Login or Register  to view this content.

**:) Sixthsense :)** · 06-14-2013, 07:08 AM

Apply Autofilter on Country Column and select Euros from the drop down...

**no.18shirt** · 06-14-2013, 07:49 AM

Great, thank you! Is there a way for me to be able to count the number of times certain countries appear? EG COUNTA=name of country

Eg Mexico appears 20 times
USA 45 times
etc

**:) Sixthsense :)** · 06-15-2013, 12:14 AM

Use Countif function like this..

Formula:

Please Login or Register  to view this content.

Or pivot table..

Select Countries data with header and Press Alt+D+P>>Next>>Next>>Finish. Click the cursor inside Pivot and Select Countries Field and drag and drop it in Row Label and again choose the field country and drop it in Data/Value area.