Suggest alternate Formula..for large number

[kundanlal]

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

 

[vletm]

kundanlal
Did You noticed that Excel Challenges are...
Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!

 

[Nebu]

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:

https://www.reddit.com/r/excelevator/comments/5movbv

Thanks

 

[kundanlal]

kundanlal
Did You noticed that Excel Challenges are...
Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!
[/QUOT

Dear Sir.. Extremely sorry for that.. Will take care next time..Thanks..

 

[kundanlal]

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

 

[Nikunj Rastogi]

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.

 

[kundanlal]

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