• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

select all columns at once which need special criterium

K

Kurt

New Member
Hello forum!


My problem is as follows; I have a very long list with data. the first column contains values like this: cityname/country, eg. New Dehli/India or Madrid/Spain. (cityname, slash, country name).

I want to select all rows of a country , eg. Spain and extract this subset into a new spreadsheet. Searching e.g. for "Spain" and selecting the first row individually and copying, then selecting the next occurence and copying and so on is too tendious, because this is a really large list.


Please give me a tip, how i can do this in a more elegant way. Is there a way to select all rows at once and copy them?


thank you very much

Kurt
 
Kurt


Firstly, Welcome to the Chandoo.org forums.


You might want to have a look at the technique Chandoo has described here:

http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/
 
Hui,

thank you yery much for your friendly welcoming!


I looked at the link (and watched the video too), but I dont understand how this could help solve my problem. The demo has a "simple" sort-criteria: name of salemen like Jospeh, Maria ...

My data dont have such a simple" criteria like an extra colum countryname. Unfortunately my criteria-column has the following form: cityname plus slash plus ciuntryname, e.g. New York/USA , so I cant sort on USA. Any hints how I can split this?


thank you very much


Kurt
 
Why not do a Text to Column first on the Field with the Madrid/Spain values

Using the / as a Field seperator

Then follow instructions at the above link


Text to Column is found on the Data Ribbon
 
Hi Kurt,


Firstly welcome to Chandoo.org.


Your problem could have been solved in many ways. But right now the easiest way that I can think of (without getting into any formula stuff..) is:


1)remove the slash(/) between city and country(to do the same select the entire column having this data, press ctrl+h to get the find replace dialogue box,in find what section place the slash (from keyboard..left to shift button), take the cursor at replace with section and press the specebar once......this will remove the "/" and replace it with a space


2)Again select the same column...from ribbon "data">"text to column">make sure dilimited is selected>check space>hit next>hit finish.....you will get two columns...one for city another one for country


Hope this helps...


Kaushik
 
Hi Kaushik,


thank you very much for your friendly welcoming too!


@Hui and @Kaushik: thank you very much for your kind support, it works :) you made my day!!


Sincerely


Kurt
 
You must log in or register to reply here.
Back
Top