Today we will explore 3 ways of quickly combine text from multiple columns in Excel. In this example, we will combine the name of the City and the Country, while adding a comma in between. For example Ottawa, Canada.
Combine Text from Multiple Columns using Flash Fill
Excel 2013 introduced a new feature called flash fill, which seems to be getting better with every new release. Using the Customers – Exercise Book tab, we will be filling in column E, titled Flash Fill.
In cell E2, we will write the results how we would like to see them, in this case we will write Ottawa, Canada.
Next, we will select cell E2, and you can either press Control+E to fill in the rest of the column automatically, or you can right-click and hold the square in the bottom-right, and drag your mouse to the bottom of the table, as we can see here:
Select Flash Fill in the drop-down that appears. Excel’s AI will automatically fill in the rest.
Combine Text from Multiple Columns Using the Concat() function
The Concat function can be used to combine data from multiple columns. In column F, we will do a quick formula to show how it works. We will start with concat(C2, B2), which will combine cells C2 and B2 together.
The end result is not quite what we want. As you can see, it squished both cells together, which we will fix next.
We will fix this by adding the text we want as a separator by using “, ” as follows: concat(C2, “, “, B2). As you can see, this formats the text as we expect it: Ottawa, Canada.
As always, you can drag the formula down by left-clicking the square in the bottom-right of cell F2 and dragging it to the bottom of the table to fill in the other cells automatically.
Combine Text from Multiple Columns Using &
The last method we will see today is the one I actually use the most, which uses the & instead of the concat() function. To begin, in G2, we will write =C2&B2. Again you will notice that we get OttawaCanada all bunched up together. We will fix this in the next step.
As we did with the concat() function, we will use “, ” to add a comma and a space between the City and Country, giving us the following formula: =C2&”, “&B2.
As always, you can left-click the square in the bottom right of the cell to drag down the formula to fill in the City, Country for the rest of the table.
In this post, I want to discuss the concept of Coronavirus Waves. Waves can be best shown graphically, where we can visually see how many new cases are confirmed each day. As we can see from the below Graph representing how many new COVID-19 cases have been confirmed globally by date, I believe we can clearly see 3 distinct waves of the virus.
While comparisons between countries can be interesting, looking at waves can allow you to investigate how well various policies are working for a particular country. Something that works well for one country might not work well in another country, and often times countries try to balance health concerns and the impact on the economy.
The First Wave started around March 16th 2020 and continued until May 28th, 2020. We can see a period of acceleration and a clear plateau around the 75,000 to 100,000 new cases per day starting around April 1st.
The Second Wave started around May 28th and also featured a period of acceleration until around July 22th, where we can again see a plateau around the 200,000 to 360,000 new cases per day.
The Third Wave, of which we are still in the acceleration period, has already reached over 565,000 new cases in a day, and is not yet showing a sign of plateauing.
Some factors to take into consideration when analyzing COVID data is the graphs are only as good as the data. To get 100% accurate data, you would need every country to test 100% of the population daily and publish those numbers truthfully. As this is not possible, the numbers are usually under-represented. The more testing that is done in a region, the more likely The real number of confirmed cases is most likely higher. You also have countries that manipulate COVID-19 numbers for political reasons, usually under-reporting the data to appear to doing better. This can be done either reporting false data, or by making it it more difficult to get tested.
Each Country is Different
One important thing to note when looking at COVID-19 statistics, is that the spread in each Country can be very different. There can be many reasons for this, including different lock-down policies and compliance, the ability of each country to test adequately, including the availability of tests, the requirements to get tested, and the ease of testing (distance to nearest testing center, time commitment to get tested, stigma with being diagnosed, etc), and the willingness of each country to report truthful numbers.
We will look at two examples here, Canada and US, where we can see very different waves. Looking at other countries, we can also see similarities and differences between each country. It would be very interesting to see what factors contributed to keeping the cases low during some waves, and what factors favored the spread of the Coronavirus in other waves.
Canada COVID-19 waves
In the case of Canada, as we can see in today’s dashboard, it would probably be more appropriate of talking about two waves. As you can see, there was a wave in Spring and a current wave going on in Fall. We could argue if we should break it up in 3 waves also, a peak in Spring, a lull during the summer, and another peak in Fall. This would greatly depend on the type of analysis you want to do.
Canada Newly Confirmed Cases by Date
United States COVID-19 Waves
If you look at the graph for the United States, we can clearly see the three waves as we did in the global graph.
Projected Confirmed Cases if Daily Gain stays at 4.2%.COVID-19 World Dashboard updated April 12, 2020. Click the image to see a high-resolution version.
In this tutorial, I show how to add custom visualizations to MicroStrategy Desktop and Workstation such as VitaraCharts. VitaraCharts is a set of custom visualizations available from vitaracharts.com for free for MicroStrategy Desktop and Workstation, and for a license for MicroStrategy Web.
Requirements:
First, open or create a Dossier in either MicroStrategy Dossier or MicroStrategy Workstation. Both are available for free from MicroStrategy.
Download a custom visualization you would like to install from either VitaraCharts, the MicroStrategy Community, or other vendors. Alternatively, you can create your own.
In the Gallery Panel, in the Custom section, click the + sign and select Import Visualization
Browse to the location of your saved visualization, and select your visualization. You do not need to unzip it first, as long as it is in .viz or .zip format it should work.
We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.Ok
Recent Comments