+ Reply to Thread
Results 1 to 7 of 7

Faster way to categorize data (Faster than nested-if)

  1. #1
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    8

    Faster way to categorize data (Faster than nested-if)

    I have to define some 12 categories, satisfying conditions from multiple different columns(some are string, some are no.). How should I go on proceeding with this. If I use nested if, it will take 12 if statements. Is there a faster and nice way to do this.

    E.g. Cat 1 if it satisfies conditions from Col A, B,F,Y (some of these columns are string like Yes/No, names and some are no.)
    Cat 2 if it satisfies conditions from Col B, E,G,I,M,O,U (some of these columns are string like Yes/No, names and some are no.)
    Cat 3 if it satisfies conditions from Col D, H,L,N,Y,Z (some of these columns are string like Yes/No, names and some are no.)

    and so on..

    How to go on about this

    Moderator's Note:
    read forum rules.

    1. A single thread is enough. ( Expect that duplicates will be closed)
    2. If you post same thread in different forums be sure ti include a link to that site.

    crosspost:
    http://www.mrexcel.com/forum/excel-q...nested-if.html
    Last edited by vlady; 03-05-2014 at 02:03 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Faster way to categorize data (Faster than nested-if)

    We will show the varieties, if you show it in excel

  3. #3
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Faster way to categorize data (Faster than nested-if)

    Quote Originally Posted by :) Sixthsense :) View Post
    We will show the varieties, if you show it in excel
    Hi,

    PFA in the message. These are some sample categories.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Faster way to categorize data (Faster than nested-if)

    Please show me the "varieties"

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Faster way to categorize data (Faster than nested-if)


    I
    J
    K
    L
    M
    N
    O
    1
    Concatenation
    Travel
    Couples
    Child
    From
    To
    Result
    2
    Frequent|Yes|Yes
    Frequent
    Yes
    Yes
    10000
    150000
    Rich frequent Traveller with Kids
    3
    Frequent|Yes|No
    Frequent
    Yes
    No
    10000
    150000
    Rich frequent Traveller w/o Kids
    4
    Not Frequent|Yes|Yes
    Not Frequent
    Yes
    Yes
    10000
    150000
    Rich not frequent Traveller with Kids
    5
    Not Frequent|Yes|No
    Not Frequent
    Yes
    No
    10000
    150000
    Rich not frequent Traveller w/o Kids
    6
    Not Frequent|Yes|No
    Not Frequent
    Yes
    No
    5000
    10000
    Couples w/o Kids (Middle class)
    7
    Not Frequent|Yes|No
    Not Frequent
    Yes
    No
    0
    5000
    Couples w/o Kids (Lower class)
    8
    Not Frequent|No|No
    Not Frequent
    No
    No
    10000
    150000
    Bachelor Not Frequent Travel (Rich)
    9
    Not Frequent|No|No
    Not Frequent
    No
    No
    5000
    10000
    Bachelor Not Frequent Travel (Middle)
    10
    Not Frequent|No|No
    Not Frequent
    No
    No
    0
    5000
    Bachelor Not Frequent Travel (Lower)
    11
    Frequent|No|No
    Frequent
    No
    No
    10000
    150000
    Bachelor Frequent Travel (Rich)
    12
    Frequent|No|No
    Frequent
    No
    No
    5000
    10000
    Bachelor Frequent Travel (Middle)
    13
    Frequent|No|No
    Frequent
    No
    No
    0
    5000
    Bachelor Frequent Travel (Lower)



    F
    G
    1
    Concatenation
    Result
    2
    Frequent|Yes|Yes
    Rich frequent Traveller with Kids
    3
    Not Frequent|NO|No
    Bachelor Not Frequent Travel (Rich)
    4
    Frequent|Yes|Yes
    Rich frequent Traveller with Kids
    5
    Not Frequent|NO|No
    Bachelor Not Frequent Travel (Rich)
    6
    Frequent|Yes|Yes
    Rich frequent Traveller with Kids
    7
    Frequent|Yes|No
    Rich frequent Traveller w/o Kids
    8
    Frequent|Yes|Yes
    Rich frequent Traveller with Kids



    F
    G
    1
    Concatenation
    Result
    2
    =B2&"|"&D2&"|"&E2
    =INDEX($O$2:$O$13,MATCH(1,($I$2:$I$13=F2)*($M$2:$M$13<=A2)*($N$2:$N$13>=A2),0))
    3
    =B3&"|"&D3&"|"&E3
    =INDEX($O$2:$O$13,MATCH(1,($I$2:$I$13=F3)*($M$2:$M$13<=A3)*($N$2:$N$13>=A3),0))
    4
    =B4&"|"&D4&"|"&E4
    =INDEX($O$2:$O$13,MATCH(1,($I$2:$I$13=F4)*($M$2:$M$13<=A4)*($N$2:$N$13>=A4),0))
    5
    =B5&"|"&D5&"|"&E5
    =INDEX($O$2:$O$13,MATCH(1,($I$2:$I$13=F5)*($M$2:$M$13<=A5)*($N$2:$N$13>=A5),0))
    6
    =B6&"|"&D6&"|"&E6
    =INDEX($O$2:$O$13,MATCH(1,($I$2:$I$13=F6)*($M$2:$M$13<=A6)*($N$2:$N$13>=A6),0))
    7
    =B7&"|"&D7&"|"&E7
    =INDEX($O$2:$O$13,MATCH(1,($I$2:$I$13=F7)*($M$2:$M$13<=A7)*($N$2:$N$13>=A7),0))
    8
    =B8&"|"&D8&"|"&E8
    =INDEX($O$2:$O$13,MATCH(1,($I$2:$I$13=F8)*($M$2:$M$13<=A8)*($N$2:$N$13>=A8),0))



    I
    1
    Concatenation
    2
    =J2&"|"&K2&"|"&L2
    3
    =J3&"|"&K3&"|"&L3
    4
    =J4&"|"&K4&"|"&L4
    5
    =J5&"|"&K5&"|"&L5
    6
    =J6&"|"&K6&"|"&L6
    7
    =J7&"|"&K7&"|"&L7
    8
    =J8&"|"&K8&"|"&L8
    9
    =J9&"|"&K9&"|"&L9
    10
    =J10&"|"&K10&"|"&L10
    11
    =J11&"|"&K11&"|"&L11
    12
    =J12&"|"&K12&"|"&L12
    13
    =J13&"|"&K13&"|"&L13


    Refer the attached file for details....
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Faster way to categorize data (Faster than nested-if)

    Is there any method to use apart from using concatenation in the formula

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Faster way to categorize data (Faster than nested-if)

    Revised Formula

    =INDEX($M$2:$M$13,MATCH(1,($H$2:$H$13=B2)*($I$2:$I$13=E2)*($J$2:$J$13=D2)*($K$2:$K$13<=A2)*($L$2:$L$13>=A2),0))

    Refer the attachment file for revised solution
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Faster way to categorize data (Faster than nested-if)
    By excel1985 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2014, 01:56 AM
  2. How to make data entry faster in excel ?
    By Indra Rai in forum Excel General
    Replies: 2
    Last Post: 10-30-2013, 10:49 AM
  3. data on same worksheet faster?
    By kriminaal in forum Excel General
    Replies: 1
    Last Post: 12-13-2010, 03:50 PM
  4. [SOLVED] faster way of data entry
    By jd in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-11-2006, 07:20 PM
  5. Replies: 2
    Last Post: 09-28-2005, 06:05 AM

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