• 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.

please guide to get a shorter IF function

M

mandarp22

Member
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
 

Attachments

  • 23 combination.xlsx
    10.5 KB · Views: 13
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 ?
 
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
 
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
 

Attachments

  • 23 If function sample.xlsm
    64.4 KB · Views: 8
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
 
Hi Narayan thanks for throwing some light....
But unfortunately I am not good in vba...
Can I get some solution??..

Mandar
 
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
 

Attachments

  • Book 1.xlsx
    14.3 KB · Views: 7
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
 
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!
 
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..
 
also need to apply same formula in each cell of "Sheet2 Column BB"
 

Attachments

  • 23 If function sample.xlsb
    60.4 KB · Views: 1
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!
 

Attachments

  • 23 If function sample (1).xlsb
    64.6 KB · Views: 3
You must log in or register to reply here.
Back
Top