1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

please guide to get a shorter IF function

Discussion in 'Ask an Excel Question' started by mandarp22, Nov 1, 2017.

  1. mandarp22

    mandarp22 Member

    Messages:
    45
    Hi All
    I have 23 combinations from column A to E and in column G…. I want to give each combination unique number. At present I am doing it with IF function and it's working fine as I am using office 2007 but it is too long and any small mistake can be painful. Is there any shorter way to do it.
    Thanks
    Mandar

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
    Hi ,

    Is this a one-off exercise ?

    If it is only a code number , then why is a formula necessary to arrive at the code number ? What will happen if you just arbitrarily assign these 23 code numbers to the combinations in some pre-decided order ?

    Can you explain the whole exercise in more detail ?
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,266
    If you want to assign a unique number based on column location I would suggest something like
    G2: =SUMPRODUCT((A2:E2)*10^(COLUMN(A2:E2)-1))
    or
    =HEX2DEC(SUMPRODUCT((A2:E2)*10^(COLUMN(A2:E2)-1)))
    Copy down
    Thomas Kuriakose and fredieusa like this.
  4. mandarp22

    mandarp22 Member

    Messages:
    45
    Hi Narayan and Hui

    Sorry to confuse you, here is the whole concept in sample file. In this file I have Invoice data stored in sheet2 and in sheet1 I want to generate Invoice Statement based on filters (basic 5 filters) and we can make 23 combinations of these filters together to generate Invoice statement.

    The sample file works fine but as I said there are 23 filter combinations so I nested 23 If function in “sheet1 cell A4” and “sheet2 column BB” also in sheet1 column F, G and H need a solution to address “0”. I did it with the only way I know. Please guide me any simpler way for this. 1 more point is, what if I add 1 or 2 more filters in the future? It will increase the length of IF function.
    Regards
    Mandar

    Attached Files:

  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
    Hi ,

    If your requirement is to use the different statuses to arrive at the possible combinations , then you need to actually list the possible combinations first ; assigning unique numbers to these possible combinations is a matter of just starting at 1 and going down till every combination has been assigned a number , incrementing the number each time.

    At present , you have the following 5 statuses :

    Dates Between

    Name

    Invoice Value

    Paid

    Unpaid

    The twist here is that status 4 and status 5 are mutually exclusive , and we will have to both specify this and take this into account while generating the possible combinations.

    I would think that since this is a one-off exercise , it is not really necessary to have a formula to assign numbers ; what is more important is to generate all the possible combinations , to ensure that no combination has been left out.

    How is this to be done and will a formula solution be possible ?

    As you mention , if flexibility is desired so that future additions are also provided for , I am doubtful that a formula solution will be possible.

    Narayan
  6. mandarp22

    mandarp22 Member

    Messages:
    45
    Hi Narayan thanks for throwing some light....
    But unfortunately I am not good in vba...
    Can I get some solution??..

    Mandar
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
    Hi ,

    This is not a solution ; it is just a workbook which will give you an idea of how to go about listing all the combinations and getting the descriptions and assigning unique numbers.

    I doubt that it is worth investing time and effort into getting a fully automated solution , since this is just a one-time effort.

    As and when there are changes to this setup , you can easily make those changes in this workbook , or you can approach this forum once again and save yourself time and effort.

    Narayan

    Attached Files:

  8. mandarp22

    mandarp22 Member

    Messages:
    45
    Thanks Narayan for the help..
  9. vletm

    vletm Excel Ninja

    Messages:
    3,458
    mandarp22
    Could it be something like this?
    The 'Key' sample below:
    Screen Shot 2017-11-04 at 18.39.48.png
    ( There were some ActiveX-components ... no match with me! That's why another format! )

    Attached Files:

    Thomas Kuriakose and sathishsusa like this.
  10. mandarp22

    mandarp22 Member

    Messages:
    45
    Hi Vletm....
    yes something like this....
    but in this when I put filter it works and produce the statement i.e. dates between and name.. but when I remove filter (name) nothing happens..

    Regards
    Mandar
  11. vletm

    vletm Excel Ninja

    Messages:
    3,458
    mandarp22 ... hmm?
    You did something and it works and
    after that You did something and
    nothing happens - correct?

    What filter?
    Did You change layout?
    Without clear information someone would only guess!
  12. mandarp22

    mandarp22 Member

    Messages:
    45
    Hi...... no I just downloaded the file the put name (Mandar) in "sheet1 D4" and it worked then I delete name (Mandar) in "sheet1 D4" with delete key on keyboard but nothing happens..
  13. vletm

    vletm Excel Ninja

    Messages:
    3,458
    Did You press <ENTER> after that 'delete key'
    or even moved to another cell?
  14. mandarp22

    mandarp22 Member

    Messages:
    45
    Yes I did press ENTER key after DELETE..
  15. vletm

    vletm Excel Ninja

    Messages:
    3,458
    Upload that file here.
  16. mandarp22

    mandarp22 Member

    Messages:
    45
    also need to apply same formula in each cell of "Sheet2 Column BB"

    Attached Files:

  17. vletm

    vletm Excel Ninja

    Messages:
    3,458
    Yes I did press ENTER key after DELETE..
    ... and it worked!

    ... You also would like to need something same with other sheet
    Did did it to column "AV" ...
    I didn't want to delete ... any Your formulas.
    I marked with red fonts (columns) which I did not use!

    Attached Files:

    sathishsusa likes this.

Share This Page