In business, we often have to work with data of which we have little or no control of the source. This can introduce problems like unwanted characters or unworkable formatting. And this is exactly the problem that Alex has this week. He's helping a client who organizes events. They get their customer database sent to them in a report, which they then paste into a spreadsheet. They need to create certain pivot tables using this data. But because of the country name only being included once, they're unable to do this. The first problem Alex is going to help them solve is the issue with replacing the blanks with the repeating country names. There are a couple of approaches we can take. If it's an occasional fix, there's quite a neat solution you can use using the Go-To Special, and that's the first approach we're going to look at. We're going to start by clicking in the first blank cell and typing equals. And then clicking on the country above to get it to repeat. We're then going to copy that formula, clicking back on that cell, I'm going to press Ctrl and C. We are now going to go to our Find and Select on our Home ribbon and we are going to come down to Go To Special and we are going to select blanks. What this will do, is find the blanks in that area and select them. We are going to click on OK and there we go. Now with all those cells selected we are simply going to paste the formula we created earlier. So Ctrl+ V and that has immediately copied the country above into all the blank cells. The last step is to replace the formulas with the values and to do this we select the data. I'm going to start by selecting from Armenia, Ctrl +Shift+Down Arrow, and I'm going to copy Ctrl + C. And I'm going to choose to Paste Values. What this will do, is replace the formulas with the results. We have now removed all the blanks, quite a neat little solution. And if you had to do this on a semi-regular basis, you could of course record yourself doing it in a macro and simply use your macro for future fixes. The only problem with what we've done here is we've lost our original data. And if you're going to have to repeat this process on a weekly basis this is probably not the best method. Where you have data that needs to be regularly repaired or corrected, it is probably a better idea to pull the original data into one sheet. And then have a separate sheet where you use functions and formulas to correct that data. This gives you the benefit of preserving your original data, but it also means that you don't need to run the fixes every time because the fixes are sitting in another sheet. That's the approach we're going to look at next. Let's quickly undo and duplicate the sheet by holding Ctrl+Down and dragging the worksheet tab. And we've done that so we can get the headings and the formatting all done for us. And now what we're going to do is pull through the values from the data sheet using calculations. A nice simple calculation, click on A2 type equals click into Sheet1, click on Argentina, and click Enter. And because of the blanks, we are going to have to drag it. So we're going to drag that whole way down to the bottom and then we're going to drag it across. What we've done there is replaced our pasted values with formulas that actually pull the values through from the original data sheet. Now, we're going to look at how we can improve on these simple calculations and repair the data in the process. The first one we're going to look at it is how to get the country repeating again. And for this, we're going to use two simple functions. The IF function which we've seen before, and the IS_BLANK function. Which simply does a check for if a cell is blank or not, and returns true or false. I'm going to double click on A2. And we're going to click after the equal sign and we're going to pop in an IF, and we want to check if the value in sheet A2 is blank. We're going to type ISBL and tab to select ISBLANK and I am just going to copy this sheet 1 A2. Because we're going to need this in a moment and then I'm going to close my brackets. If it is a blank, it is going to return in a true, in which case I wanted it to use whatever value is in the cell above. So I'm going to click on the one above. If it isn't blank, so it has the correct value, then we're going to use the value in Sheet1 A2. Close our bracket for our IF and Enter. Double click to copy that formula down, and now we've got all our repeating country values and we're ready to start pivoting this data. That's one problem solved and there are still other issues. We're going to deal with that in the next video so keep watching.
