Steve has to sum numbers that have been entered in a text column
There are multiple lines in each cell, separated by Alt Enter
Need to split those lines to rows, then parse the dollar amount from the middle of each cell
Summarize by Cost Center
Build a lookup table
Get totals from the lookup table, using IFNA to ignore the errors in the blank row
Bonus: Add an Event macro to update the worksheet when they change a cell.
There are multiple lines in each cell, separated by Alt Enter
Need to split those lines to rows, then parse the dollar amount from the middle of each cell
Summarize by Cost Center
Build a lookup table
Get totals from the lookup table, using IFNA to ignore the errors in the blank row
Bonus: Add an Event macro to update the worksheet when they change a cell.
Transcript of the video:
Learn Excel from MrExcel, Podcast Episode 2160: SUM Data That’s Been Alt+Entered.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
I am not making this up.
I got a question from someone who has data -- budget data -- that looks like this.
Now, I put fake words in here so that we don't have their budget information, but person's new to the accounting department, went to a company, and this company for years has been doing their budgets like this.
They're not accountants doing the budget, they are line people, but this is the way they've been doing it, and he can't get them to change.
So, here's our goal.
He says this is as bad as typing the budget into Word.
Well, almost, but luckily, thanks to power query, it's going to save our problem.
Here's our goal.
For every COST CENTER over here, we want to report the total of all of those numbers.
So, there's expense name, a -, routinely a -, then a $ sign, and then, just to make life interesting, every once in a while, a random note after; not all of the times, just some of the time.
Blank row between each one.
Tons and tons of data.
So, here's what I'm going to do.
I'm going to come down to the very bottom, the very last cell, I’m going to select all of this stuff, including the headings.
I'm going to create a NAME.
I'm going to call it MyData.
MyData, like that, okay?
Alright.
Now we're going to use power query which is free in 2010 or 2013, built into 2016 and 2016 Office 365.
That's going to come from a TABLE OR RANGE.
Alright.
First thing, anytime that we have those blanks in COLUMN A, all the NULLS we want to get rid of.
So I’m going to uncheck NULL.
Awesome.
Okay.
Really, in this data, in this version of the data, because I'm going to build a VLOOKUP, we don't need this column.
So, I'm going to right-click and get rid of that column, so REMOVE column.
Alright.
Now, here's where the freaking magic is going to happen.
Choose this column, SPLIT COLUMN BY A DELIMITER, and we're definitely going to go into ADVANCED.
The delimiter is going to be a special character and we're going to split each occurrence of the delimiter.
So, here, I think they've actually already figured it out because I expanded it, but I’m going to show you.
INSERT SPECIAL CHARACTER.
I’m going to say it's a LINE FEED, alright, so, at each occurrence of the LINE FEED, and I'm going to SPLIT INTO ROWS.
Alright, and just what's going to happen here is, 1, 2, 3, 4, 5, I'm going to get 5 rows or I'm going to say 1001, but, in each row, it's going to have a different line from this cell.
This is amazing.
There is 1, 2, 3, 4, 5, 1001.
Alright.
Now we just need to parse this bad boy out.
Alright, so, choose that column, SPLIT COLUMN BY A DELIMITER.
This time, a delimiter is going to be a $ sign.
That's perfect, once, at the first $ sign we find, just in case there's a $ sign out there in the future part.
We’re going to SPLIT INTO COLUMNS.
Click OK.
Alright.
So, there are details.
Here's our money.
Now, I'm going to split this at the SPACE.
So, choose this column, SPLIT COLUMN BY A DELIMITER, and the delimiter is going to be a SPACE, yes, once at the LEFT-MOST DELIMITER, click OK, and I don't need those comments out there so those comments we're going to REMOVE.
Actually, don't need this either because I'm just trying to get a total of all that stuff, so I'm going to REMOVE.
Now, transform.
GROUP BY COST CENTER, NEW COLUMN NAME is going to be called TOTAL, the OPERATION is going to be the SUM, and which column are we going to SUM?
The DETAILS2.1.
Beautiful.
Click OK, alright, and what we end up with is one line per COST CENTER with the TOTAL of all those line items.
HOME, CLOSE & LOAD.
It’s probably going to insert a new worksheet.
I'm hoping it inserts a new worksheet, and it does, and that worksheet is called MYDATA_1.
MYDATA_1.
Alright.
Now we're going to come back here into the original data and do these steps.
On the very first one, =VLOOKUP of 1001 into our results.
This is kind of like setting up a circular reference but it's not going to give us a circular reference.
, 2 , FALSE.
I want the exact match.
Alright, but we're not going to want to do that for the blank cells.
So, I'm going to say, well, actually, let's just copy it down all the way.
CONTROL+C, go all the way down just to see what we're getting.
Maybe we're getting N/As and I can get rid of it with the IFNA.
Yeah, beautiful, alright.
So, let's just get rid of the N/As.
If N/A, then we just want “”. We don't want anything in there.
CONTROL+ENTER.
Alright.
Now, that should be the TOTAL.
Let's see if we can find a short one and just do the math.
=627.37+7264.25+6066.01+4010.66+9773.94, and the TOTAL, 27742.23 is that.
Freaking Awesome.
Now, here's the deal.
So, we have those line people who are out here changing stuff, alright, and so let's say that they go through and they change the budget, 40294.48, and they come up here and change this one to 6000, like that, and they add a new one, ALT+ENTER, SOMETHING - $ sign, $1000 just added.
Alright.
Now, of course, when I press ENTER, this number, 40294.48, is not going to update, alright, but what we have to do is go to the DATA tab and we want to REFRESH ALL.
So, 40294.48.
Watch, watch, watch, watch.
REFRESH ALL.
Freaking amazing.
I love power query.
Power query is the most amazing thing.
This data, which essentially is just like word data in a cell, we now have it being updated.
You could probably even make some sort of a macro that says that every time someone changes something in COLUMN C, we go ahead and click REFRESH ALL using the macro and just have those results constantly, constantly refreshing.
What a horrible question sent in.
I feel bad for Steve who has to deal with this, but now, using power query in Office 365 or downloaded for 2010 or 2013, you have a very, very easy way to solve this.
Wait.
Okay, an addendum: let's make it even better.
This sheet is called DATA and I've saved the workbook as macro-enabled, so xlsm.
If you’re xlsx, don't skip saving as xlsm.
ALT+F11.
Find the workbook called DATA, double-click, top left, WORKSHEET, and then CHANGE anytime we change the worksheet, and we're going to say ACTIVEWORKBOOK.REFRESHALL, and then close, alright, and now let's try it.
Let's edit something.
So, we'll take those raspberries which are currently 8,000 and we’ll change it to 1000, so we're reducing by 7000.
When I press ENTER, I want to see that 42,000 go down to 35,000.
Ah.
Awesome.
Well, hey.
This is where I usually beg you to buy my book but, today, I'm going to ask you to buy my friends’ book -- Ken Puls and Miguel Escobar -- M is for (DATA) MONKEY.
Everything I learned about power query, I learned from this book.
It's an amazing book.
Check that out.
Episode wrap-up: Steve has numbers to sum that have been entered in a text column; multiple lines in each cell, separated by ALT+ENTER; need to split those lines to rows, then parse the dollar amount from the middle of each cell; summarize by COST CENTER; build a lookup table; get totals from the lookup table, using IFNA to ignore the errors in the blank row; and then, a bonus, macro at the end, an event macro to update the worksheet when they change a cell.
I want to thank Steve for sending that question in and I'm so glad I have an answer -- before power query, it would have been really, really tough – and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
I am not making this up.
I got a question from someone who has data -- budget data -- that looks like this.
Now, I put fake words in here so that we don't have their budget information, but person's new to the accounting department, went to a company, and this company for years has been doing their budgets like this.
They're not accountants doing the budget, they are line people, but this is the way they've been doing it, and he can't get them to change.
So, here's our goal.
He says this is as bad as typing the budget into Word.
Well, almost, but luckily, thanks to power query, it's going to save our problem.
Here's our goal.
For every COST CENTER over here, we want to report the total of all of those numbers.
So, there's expense name, a -, routinely a -, then a $ sign, and then, just to make life interesting, every once in a while, a random note after; not all of the times, just some of the time.
Blank row between each one.
Tons and tons of data.
So, here's what I'm going to do.
I'm going to come down to the very bottom, the very last cell, I’m going to select all of this stuff, including the headings.
I'm going to create a NAME.
I'm going to call it MyData.
MyData, like that, okay?
Alright.
Now we're going to use power query which is free in 2010 or 2013, built into 2016 and 2016 Office 365.
That's going to come from a TABLE OR RANGE.
Alright.
First thing, anytime that we have those blanks in COLUMN A, all the NULLS we want to get rid of.
So I’m going to uncheck NULL.
Awesome.
Okay.
Really, in this data, in this version of the data, because I'm going to build a VLOOKUP, we don't need this column.
So, I'm going to right-click and get rid of that column, so REMOVE column.
Alright.
Now, here's where the freaking magic is going to happen.
Choose this column, SPLIT COLUMN BY A DELIMITER, and we're definitely going to go into ADVANCED.
The delimiter is going to be a special character and we're going to split each occurrence of the delimiter.
So, here, I think they've actually already figured it out because I expanded it, but I’m going to show you.
INSERT SPECIAL CHARACTER.
I’m going to say it's a LINE FEED, alright, so, at each occurrence of the LINE FEED, and I'm going to SPLIT INTO ROWS.
Alright, and just what's going to happen here is, 1, 2, 3, 4, 5, I'm going to get 5 rows or I'm going to say 1001, but, in each row, it's going to have a different line from this cell.
This is amazing.
There is 1, 2, 3, 4, 5, 1001.
Alright.
Now we just need to parse this bad boy out.
Alright, so, choose that column, SPLIT COLUMN BY A DELIMITER.
This time, a delimiter is going to be a $ sign.
That's perfect, once, at the first $ sign we find, just in case there's a $ sign out there in the future part.
We’re going to SPLIT INTO COLUMNS.
Click OK.
Alright.
So, there are details.
Here's our money.
Now, I'm going to split this at the SPACE.
So, choose this column, SPLIT COLUMN BY A DELIMITER, and the delimiter is going to be a SPACE, yes, once at the LEFT-MOST DELIMITER, click OK, and I don't need those comments out there so those comments we're going to REMOVE.
Actually, don't need this either because I'm just trying to get a total of all that stuff, so I'm going to REMOVE.
Now, transform.
GROUP BY COST CENTER, NEW COLUMN NAME is going to be called TOTAL, the OPERATION is going to be the SUM, and which column are we going to SUM?
The DETAILS2.1.
Beautiful.
Click OK, alright, and what we end up with is one line per COST CENTER with the TOTAL of all those line items.
HOME, CLOSE & LOAD.
It’s probably going to insert a new worksheet.
I'm hoping it inserts a new worksheet, and it does, and that worksheet is called MYDATA_1.
MYDATA_1.
Alright.
Now we're going to come back here into the original data and do these steps.
On the very first one, =VLOOKUP of 1001 into our results.
This is kind of like setting up a circular reference but it's not going to give us a circular reference.
, 2 , FALSE.
I want the exact match.
Alright, but we're not going to want to do that for the blank cells.
So, I'm going to say, well, actually, let's just copy it down all the way.
CONTROL+C, go all the way down just to see what we're getting.
Maybe we're getting N/As and I can get rid of it with the IFNA.
Yeah, beautiful, alright.
So, let's just get rid of the N/As.
If N/A, then we just want “”. We don't want anything in there.
CONTROL+ENTER.
Alright.
Now, that should be the TOTAL.
Let's see if we can find a short one and just do the math.
=627.37+7264.25+6066.01+4010.66+9773.94, and the TOTAL, 27742.23 is that.
Freaking Awesome.
Now, here's the deal.
So, we have those line people who are out here changing stuff, alright, and so let's say that they go through and they change the budget, 40294.48, and they come up here and change this one to 6000, like that, and they add a new one, ALT+ENTER, SOMETHING - $ sign, $1000 just added.
Alright.
Now, of course, when I press ENTER, this number, 40294.48, is not going to update, alright, but what we have to do is go to the DATA tab and we want to REFRESH ALL.
So, 40294.48.
Watch, watch, watch, watch.
REFRESH ALL.
Freaking amazing.
I love power query.
Power query is the most amazing thing.
This data, which essentially is just like word data in a cell, we now have it being updated.
You could probably even make some sort of a macro that says that every time someone changes something in COLUMN C, we go ahead and click REFRESH ALL using the macro and just have those results constantly, constantly refreshing.
What a horrible question sent in.
I feel bad for Steve who has to deal with this, but now, using power query in Office 365 or downloaded for 2010 or 2013, you have a very, very easy way to solve this.
Wait.
Okay, an addendum: let's make it even better.
This sheet is called DATA and I've saved the workbook as macro-enabled, so xlsm.
If you’re xlsx, don't skip saving as xlsm.
ALT+F11.
Find the workbook called DATA, double-click, top left, WORKSHEET, and then CHANGE anytime we change the worksheet, and we're going to say ACTIVEWORKBOOK.REFRESHALL, and then close, alright, and now let's try it.
Let's edit something.
So, we'll take those raspberries which are currently 8,000 and we’ll change it to 1000, so we're reducing by 7000.
When I press ENTER, I want to see that 42,000 go down to 35,000.
Ah.
Awesome.
Well, hey.
This is where I usually beg you to buy my book but, today, I'm going to ask you to buy my friends’ book -- Ken Puls and Miguel Escobar -- M is for (DATA) MONKEY.
Everything I learned about power query, I learned from this book.
It's an amazing book.
Check that out.
Episode wrap-up: Steve has numbers to sum that have been entered in a text column; multiple lines in each cell, separated by ALT+ENTER; need to split those lines to rows, then parse the dollar amount from the middle of each cell; summarize by COST CENTER; build a lookup table; get totals from the lookup table, using IFNA to ignore the errors in the blank row; and then, a bonus, macro at the end, an event macro to update the worksheet when they change a cell.
I want to thank Steve for sending that question in and I'm so glad I have an answer -- before power query, it would have been really, really tough – and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
