Susan writes in with an annoying problem. Crystal Reports is creating a report where every physical record is taking three rows in Excel. Episode 580 will walk through the steps necessary to get this into a nice sortable data set.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question soon by Susan.
Susan has a problem with a report created by Crystal Reports.
If you have a question for the netcast, please feel free to drop us a voicemail or an email bill@mrexcel.com, and we can get to you on a future podcast.
Now, here's Susan's problem.
Susan says that when she creates a report from Crystal.
Crystal is basically, taking three row for creating every single record.
So, she has data in Columns A, B, C, D and E of Row 1 and then 2 annoying fields.
In just, columns A and C of row 2 and then a blank field in row 3.
And she said is there any way that I can get this into a nice sort able data set, with all of the fields on one line getting rid of those blank rows?
Now, here's what I'm gonna do is I'm going to create two new heading.
So, I'm going to copy the headings over two columns F and G and I'm going to create a formula in the first record, to solve just the first one.
So, I'm going to set up a simple formula here that says equal A3 and a simple formula here that says equals C3.
Now, I want to create those formulas basically just so that way I have a pattern to copy.
But, I don't want to copy it to all of the rows.
I only want to copy it to the rows that are basically the first row.
So, I have to take a look at this data set and say OK.
Well, It looks like if Column B is blank it's not row 1, row 1 is the only item that has something in Column B.
So I'm going to select my entire data set.
I'm gonna hit End, Home, to go to the end of the data and then hold down the Shift key while I hit the Home key.
That will select all of my data and now I'm going to go to Data, Filter, AutoFilter.
Now, the AutoFilter is looking for a nice contiguous block of data because of those blank rows.
That's why we had to select the whole data set. I'm going to go to column B, and say that I want the non blanks. Column B filter to just the non blanks.
Now, that's very cool because it gives us just the first row of each data set.
Now, I can copy this data.
So, I'm going to use control+C to copy, select the other cells.
I want the visible cells only.
Alt+semicolon, hold it on the Alt key and hit the semicolon.
And now, I can paste control+V to paste.
Now, let's take a quick look at that data set.
Now, if we go back and clear the filter so, we can see all of the items you'll see that indeed we've copied the second row from each record up to the first row.
We now want to copy columns F and G control+C, when we use Edit, Paste Special, values and we basically, now have a situation where the first row of every item from Crystal has all of our data.
We can go through and delete rows 2 and 3.
I'm going to do that by sorting by column B again.
I want to make sure that I select the entire Data set before I sort So I'll select that data will use Data, Sort and say that we want to sort by the second heading, click OK.
And that basically, gets all of the rows that have something and column B to the top.
I can now go through and delete the other rows.
So, well it's very annoying that Crystal Reports is giving us the data in that weird format.
Using a few extra steps and using some relatively advanced Excel tricks.
We can get it down to a nice sort able data set with all of the fields on one row.
You wonder why Crystal didn't do that in the first place.
Hey, thanks for something by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question soon by Susan.
Susan has a problem with a report created by Crystal Reports.
If you have a question for the netcast, please feel free to drop us a voicemail or an email bill@mrexcel.com, and we can get to you on a future podcast.
Now, here's Susan's problem.
Susan says that when she creates a report from Crystal.
Crystal is basically, taking three row for creating every single record.
So, she has data in Columns A, B, C, D and E of Row 1 and then 2 annoying fields.
In just, columns A and C of row 2 and then a blank field in row 3.
And she said is there any way that I can get this into a nice sort able data set, with all of the fields on one line getting rid of those blank rows?
Now, here's what I'm gonna do is I'm going to create two new heading.
So, I'm going to copy the headings over two columns F and G and I'm going to create a formula in the first record, to solve just the first one.
So, I'm going to set up a simple formula here that says equal A3 and a simple formula here that says equals C3.
Now, I want to create those formulas basically just so that way I have a pattern to copy.
But, I don't want to copy it to all of the rows.
I only want to copy it to the rows that are basically the first row.
So, I have to take a look at this data set and say OK.
Well, It looks like if Column B is blank it's not row 1, row 1 is the only item that has something in Column B.
So I'm going to select my entire data set.
I'm gonna hit End, Home, to go to the end of the data and then hold down the Shift key while I hit the Home key.
That will select all of my data and now I'm going to go to Data, Filter, AutoFilter.
Now, the AutoFilter is looking for a nice contiguous block of data because of those blank rows.
That's why we had to select the whole data set. I'm going to go to column B, and say that I want the non blanks. Column B filter to just the non blanks.
Now, that's very cool because it gives us just the first row of each data set.
Now, I can copy this data.
So, I'm going to use control+C to copy, select the other cells.
I want the visible cells only.
Alt+semicolon, hold it on the Alt key and hit the semicolon.
And now, I can paste control+V to paste.
Now, let's take a quick look at that data set.
Now, if we go back and clear the filter so, we can see all of the items you'll see that indeed we've copied the second row from each record up to the first row.
We now want to copy columns F and G control+C, when we use Edit, Paste Special, values and we basically, now have a situation where the first row of every item from Crystal has all of our data.
We can go through and delete rows 2 and 3.
I'm going to do that by sorting by column B again.
I want to make sure that I select the entire Data set before I sort So I'll select that data will use Data, Sort and say that we want to sort by the second heading, click OK.
And that basically, gets all of the rows that have something and column B to the top.
I can now go through and delete the other rows.
So, well it's very annoying that Crystal Reports is giving us the data in that weird format.
Using a few extra steps and using some relatively advanced Excel tricks.
We can get it down to a nice sort able data set with all of the fields on one row.
You wonder why Crystal didn't do that in the first place.
Hey, thanks for something by.
We'll see you next time for another netcast from MrExcel.
