+ Reply to Thread
Results 1 to 8 of 8

Regarding conditional If statement

  1. #1
    Registered User
    Join Date
    03-07-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    Exclamation Regarding conditional If statement

    Hello Dear Friends,

    I have 16 types of Landuse parcel attributes which I wish to Classify into Residential, Non Residential and Other category (3 Categories).
    Eg. PublicSpace: Non Residential
    Commercial : Non Residential
    Public Transport: Other
    Residential: Residential
    Its hard to do manually for list of 225 rows.
    Is there some code using IF statement which says if Landuse code is Publicspace, commerical then assign value as non residential in next column, if code is Public transport then assign it as Other, if code is Residential then, assign it as Residential.
    Please find attached excel sheet.
    Attached Files Attached Files
    Last edited by prasad_inamdar05; 03-07-2011 at 10:01 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Regarding conditional If statement

    The logic will be straightforward once we clarify your classification rules, but your data doesn't really match your description here:

    Quote Originally Posted by prasad_inamdar05 View Post
    ...if Landuse code is Publicspace, commerical then assign value as non residential in next column, if code is Public transport then assign it as Other, if code is Residential then, assign it as Residential.
    Is "public space commercial" anything that says "public" in it that's not "public - transport"? Here are some examples of other data that doesn't seem to match the rules above:

    PUZ3 - Public Use - Health Community is this public space commercial?
    PUZ7 - Public Use - Other Public Use is this public space commercial?
    PPRZ - Public Park & Recreation is this public space commercial? Doesn't sound commercial
    IN1Z - Industrial 1 this is not public space commercial or public transport or residential; what is it?
    B2Z - Business 2 this is not public space commercial or public transport or residential; what is it?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-07-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Regarding conditional If statement

    Hello StringJazzer,
    Thanks for your kind reply. Actually I would like to classify landuses which deal with water consumption.
    They can be broadly classified as Residential, Non Residential and Other( Public Transport: No water demand).

    To get things simple, Just classify the code RZ:Residential Demands as "Residential"and rest of the land uses as "non residential". (Ignore Other Category)

  4. #4
    Registered User
    Join Date
    03-07-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Regarding conditional If statement

    I just mistakenly put Public Transport as Residential. Please ignore it. It should be Other

  5. #5
    Registered User
    Join Date
    02-02-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Regarding conditional If statement

    I attach a file which shows another way that you might consider solving your problem.

    On the worksheet called 'Worksheet', you can choose a land use type from a drop-down menu. The classification is then automatically displayed in column 2.

    The drop down menu is created by using Data Validation (Data tab). It is set to use a table called 'Land_use_choices' (which is on the 'Reference tables' worksheet).

    The answers that appear in the second column are created using the VLOOKUP function, which uses the table called 'Land_use' (also on the 'Reference tables' worksheet).

    I hope this is useful.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-07-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Regarding conditional If statement

    Quote Originally Posted by marketshare View Post
    I attach a file which shows another way that you might consider solving your problem.

    On the worksheet called 'Worksheet', you can choose a land use type from a drop-down menu. The classification is then automatically displayed in column 2.

    The drop down menu is created by using Data Validation (Data tab). It is set to use a table called 'Land_use_choices' (which is on the 'Reference tables' worksheet).

    The answers that appear in the second column are created using the VLOOKUP function, which uses the table called 'Land_use' (also on the 'Reference tables' worksheet).

    I hope this is useful.
    Dear Marketshare,
    Thanks for your kind reply and help. I have a list of 250 rows. It seems that I need to create the reference table with classification for each of 16 land use type for each row. Then I may able to use VLOOKUP function. I just want to check the landuse type and it will classify it as residential / non residential in next column for all rows. I need to use that file in Arc-GIS where I have to extract data using this Residential/ Non Residential classified Parcels (In Water consumption Context).
    I am again enclosing the file. I wonder if I am able to explain it properly. Sorry for inconvenience if any. Thanks
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-02-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Regarding conditional If statement

    I am a bit confused about what you want to achieve. I am thinking that you could copy and paste your 250 records into the first column of my spreadsheet - the second column will then show the correct classifications (as long as the reference table contains all 16 types of land use).

    If you want to automatically find the 16 unique records from your 250 records, you can do it like this:
    Click anywhere in your list of 250 records
    From the Data tab, from the Sort&Filter section, choose Advanced
    In 'List Range', make sure your list of 250 records (including the column header) is shown
    Check 'Unique Records Only'

    Hope this helps.

  8. #8
    Registered User
    Join Date
    03-07-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Regarding conditional If statement

    Thank you very much for your reply. I will try the thing which you suggested.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1