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

Suggest alternate Formula..for large number

kundanlal

Member
Dear Experts,

I use simple formulas...

Attached herewith sale report where Branch Name, Opening balance, Day Sales and closing balance available in the file.

Now, there may be some occassions where some of branch(s) do not send any report and I need to mention this at the bottom of report manually,

Tried to do it by simple way using if function seperately for each branch depending upon value of Q cell of each branch=0.00. I have to apply some logic. Formula used is in cell F17.

This works.. Just want to know if this can be done in better alternative way, as at the moment, the list of branch is limited but what if it runs through say 50 or 100 branches, what would be alternative.

Thanks and Regards,

Kundanlal
 

Attachments

  • Test - Copy.xls
    59.5 KB · Views: 9
Hi:

If you are using latest versions of Excel you can use native function Textjoin to achieve this, else there are custom functions available on the net that will do the same, find the attached with one of the custom function (assuming you are using older version of excel)

Note:

Execute the function as an array by pressing (Control+Shift+Enter) keys.

Reference:
Thanks
 

Attachments

  • Test - Copy.xls
    63 KB · Views: 1
Hi:

If you are using latest versions of Excel you can use native function Textjoin to achieve this, else there are custom functions available on the net that will do the same, find the attached with one of the custom function (assuming you are using older version of excel)

Note:

Execute the function as an array by pressing (Control+Shift+Enter) keys.

Reference:

Thanks

Thank you very much.. I appreciate..
 
Hi,

Solution has been done using VBA. Write function Join_Data anywhere in excel and select then range from Q6 upto last cell and press enter then you have the city name in which 0.00 is placed. Make sure that your first row must be start from 6 or if any change then you need to go in program and change the value of i.
 

Attachments

  • Test - Copy.xls
    62.5 KB · Views: 2
Hi,

Solution has been done using VBA. Write function Join_Data anywhere in excel and select then range from Q6 upto last cell and press enter then you have the city name in which 0.00 is placed. Make sure that your first row must be start from 6 or if any change then you need to go in program and change the value of i.
Many Thanks Nikunji..
 
Back
Top