One Year Anniversary - Excel Hero LinkedIn Group

One year ago, this was the beginning of Excel Hero Linkedin group:

Let's make this The Place for Excel answers, sharing, and networking. So what's on your mind?

What happened in the past one year?

Now we have almost 1800 members. That is a huge number. Only 1% of Linkedin groups have 1000+ members! Regarding Excel-related Linkedin Groups we are not the largest, but I am sure I can say we are one of the best! Why? Because we have lot of interesting discussions, we posted some really good challenges and shared knowledge and helped to each other.

A few weeks ago Daniel opened the group - this turning point was an important change in the life of the group. 

Let us summarize the most interesting threads from the period we were a closed group!

We had 123 discussions and 830 comments posted. 220 members commented - good work!

We are very proud that we had 53 discussions asking for help in very different topics - mainly in connection with formulas - and almost all of the setters had great solutions to the their problem.

28 discussions were started to share knowledge - you posted several great links to your work, articles and other training materials. Just some examples: we received fantastic active cell-based dynamic charts, great non-VBA solutions for barcodes and text related Pivot tables, useful add-ins to help work with chart labels. You collected the most important excel add-ins in a discussion started by Chun Lee. 19 of you posted comments there, with very different add-ins according to your needs.

You were talking about some general topics questions (issues?). An interesting one was about the Non-accepted knowledge discussed by Kris - Krisztina Szabó: What can we do when company/boss/colleagues afraid of our knowledge? The outcome of the discussion was:

The most popular discussion was the Awesome Formula Challenge! where we tried to convert numbers into written text. We had 83 comments on this discussion from 22 members! Daniel started the discussion with a 1371 character-long formula - it was exciting to see how some experts of you challenged, changed and shortened it. The most important enhancements were posted by our formula-savant, Roberto Mensa. The result of this fantastic "team" development process was that we were able to drop the formula below 600 characters! Amazing!

Another great, long-life discussion was the one with the title: What is the Best Excel Tip You Can Share With Us? 41 members shared tips with us in 66 comments. We had many different tips from VBA-codes to regular expressions, from tricky formulas to practical keyboard shortcuts. Plus some effective advices for project teams, like this very important one: "ALWAYS think about the end user." by Oz du Soleil.

Many of us use non-English Excel and/or have non-English local settings in the operating system.  Some questions focused on the problems coming from these kind of settings and difficulties in VBA. Daniel gave us a great tip to use the arguments of Application.International to find out which separator characters are used in a particular computer. If you develop for international use, good to memorize it!

The last discussion of 2011 was posted by Roberto Mensa on new year's eve: Countdown starts .... Useless Functions.... You don't want to see in 2012! 77 great comments were posted by 23 of you. There were great debates around the necessity of CONCATENATE - good reason that sometimes it is more convenient to type the list separator character then typing &.

We had an interesting discussion around the INT-ROUNDDOWN-TRUNC formulas. It is possible to use one with some calculations to substitute the other. Don Love stated an essential point when using formulas: these formulas are "important to keep because they document your work." - because the formula name has a meaning!

The story of CHOOSE is also interesting - some of you (experts) never used, meanwhile some think it is essential. The conclusion is that our projects, our challenges could be very different - we need very different formulas to use.

VALUE was also questioned - but we made a nice formula-reference fun with it:

 {=VALUE(OR:NOT VA:LUE)} 

=ISTEXT(FR:OM HAM:LET) 

The only thing you agreed was the un-necessity of BAHTTEXT - no one knows why this formula is there. ☺

It was a great game, guys - at the end, we keep all the formulas, or even, want more!

That is why Paul Benson started the discussion: Desired Functions. 

First we mentioned the famous CONCATENATE - why it does not work with ranges? We have great VBA solutions for this problem. Some of you need some other string-related formulas, mainly more sophisticated search/find possibilities. Paul mentioned bitwise operators - and Roberto again shown us how can we build up these formulas using the existing base-formulas in array-formula. Many of the commenter's mentioned that user defined formulas could have been built up for the special requests.

Jay Hunnemeyer stated a great conclusion: "The beauty of Excel is that, with its inherent set of functions, and VBA, most anything imagined can be accomplished!"

However I could imagine what Gordon Cooper told: "=thisweekslottonumbers() would be useful :)"  -  maybe it is the only one could not be done with VBA? ☺

Playing with formulas is always interesting - these kind of discussions are sure successes. Some examples: In one of Roberto's challenge we created formulas to count how many different characters are in a text expression. It was a great challenge with 3 different formulas: for case sensitive, non-case sensitive and for all (including non-ASCII) characters. It was nice to see how we used some parts of each other's formula to build up a shorter formula.

Here is a solution combining the suggestions of Daniel and Kris for counting all the characters in a case-sensitive way:

=SUM(N(FIND(MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1),A1)= ROW(OFFSET(A1,,,LEN(A1)))))

The conclusion: "when you take away all the superfluous ... formulas seem so easy ..." by Roberto.

Another favourite of mine is: How to create an array of numbers from a comma-separated text entered into A1 like: 8,9,11,13 (Array question for the experts! By Alun Hewinson)

Seems the speciality of our group is to provide adequate solutions to the group members questions. Really good to read these kind of sentences:

"Thank you all so very much for taking the time to offer your solutions, I really do appreciate it. This has really shown me the power of LinkedIn and selecting the right Groups to join!" Mark Lowe, FCCA, MInstLM.

To reach a wider audience, Daniel decided to open the group. That means all discussions started in the open group are fully visible, searchable, and shareable on the Web. We have two managers: Suzanne and Kris - their mission is to keep this place energized: stir the pot to encourage group member participation and group membership growth. The first month is amazing! We have 58 new discussions and 630+ comments within a MONTH! 

We started a poll to get to know you better. After 135 votes we can see that significant part of our readers are Excel Power user or Excel developer. And take a look at the number of Professional Excel/VBA Developers! It is the proof that we attract the best minds! ☺

 

Many of you (especially who are Excel Power Users) mentioned that you are learning or plan to learn VBA, so I would like to highlight a post by Jordan Goldmeier: Top 5 VBA Development Environment Tips. We collected very useful and important tips there. This discussion could be a "must read" for all who starts to learn VBA.

What is in your PERSONAL.xlsb? is a popular discussion, we shared several great VBA codes, and sometimes suggested changes/corrections/better solutions.

Excel VBA - avoid or use? Here we also have some great tips and thoughts. Many of the commenters mentioned that the Macro recording tool could be a good starting point to get to know VBA.

And... we continue to ask, to talk about formulas, to solve challenges in many different way, to teach, to share knowledge, to post great examples, teaching materials...

Let's continue this way!