Hi there dear reader, so, I know that in the previous article that I penned, I stated that I'd be showing you how to create buttons that you can interact with (and I know you know that because you're reading these in order...right?) but I recently had to create something that was so cool, I had to write about it (once you've drank enough data Kool-Aid, you too will find solutions to data manipulation problems cool).

Here's the scene: we've got a list of people, each with a particular issue, however, due to why I needed to put the data together, we needed to combine all of the issues that each person had in a single cell for the person they were associated with.

Now, if this were regular computer programming, the solution would be simple: create an iterative FOR loop. What is this you ask? it's a pretty standard computer programming function (up there with the IF statement and the OR statement) that does a particular thing FOR a certain period of time in which a particular condition is true (like for instance, as long as the person's name remains the same. Granted, you can do FOR loops in VBA, but it's not as easy as it should be, and we'd probably have to do most of the same steps in order to get the full benefit.

However, this isn't standard programming...THIS IS EXCEL! As such, we needed to get creative and approximate the FOR loop without using VBA. Here's what we had to work with:

• A list of names (in this case, it'll be column A)
• A unique ID for each of the people on the list (This will be Column B)
• An issue (in this case, a diagnosis) that each person had, and each line has a different issue, even if it's the same person. (Column C)
• A bunch of other information that isn't important for this trick. (Who cares?)

Here's how we go about doing it:

1. Sort the entire list by the ID, that way, the data for the same people are all next to each other.
2. Create two new columns, one that does the heavy lifting, and one that signifies (or "Flags) that Excel should stop iterating.
3. In the first of the two created columns (let's put a heading on it called "combined") put in the following formula starting with cell D2 and populate your whole column:
   =IF(B1=B2,CONCATENATE(D1, ";",C2),C2)

  What this is doing is checking to see if the two IDs match (and thus are the same people) and it builds a string combining all of the issues every time there's a match. In the end, column D is going to be the column where the "work" is done. Of note, the ";" added in the middle is for aesthetics, feel free to ignore it, or just plug in some other type of spacer.

 4. In the second of the created columns (let's call this one "flag) put in the following formula: =IF(B2=B3,"",1)

 This basically adds a 1 (or whatever you decide to write in) on the last line of each loop, signifying that Excel should start the process over on the next line instead of continuing to build the string. you start on the cell below the initial cell so that you don't accidentally put the flag on the first line (as the heading and the name is never going to match) This leads us to the final step:

 5. Filter the sheet by the "flag" column for 1, and if so desired, copy and paste this filtered list into a new sheet.

This final step allows you to hide all rows that aren't the final row in the loop, thus giving you only 1 line per person with the combined issues that the person had! See below for the finished product.

So there you go, using only 2 columns and as many formulas as you can easily inject a loop into Excel! This technique has many uses, as you may want to continue running a formula down a column until a certain "thing" happens elsewhere on the sheet...the possibilities are endless!

So today, we've learned the following:

1. What iterators and FOR loops do in computer programming.
2. How to create and utilize an iterator to complete any task that needs to continue running until a particular goal is reached.

Next time, I think we'll probably delve back into Active X and Form controls so that you can improve the life of your less savvy co-workers, and make yourself look like a boss in the meantime.

Signing off,

-Snacks