After adding Subtotals, Excel will append the word " Total" to the field being subtotaled. In Episode 791, we take a look at how to use a formula to remove the totals so that the data can be used as a lookup table.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Well, here's the question that came in during one of my seminars.
If we go to DATA, SUBTOTALS, and create subtotals by CUSTOMER, COST OF GOODS SOLD, and PROFIT, click OK and then collapse down to the number 2 view, this is a great view of the data.
However, what's frustrating is if someone then needs to do a VLOOKUP to get the total revenue by customer from this, we're going to have a problem because it has the word AIG TOTAL and we just want AIG, and so my solution here was to select all of the visible cells next to those cells.
So, we would use ALT+; to select the visible cells.
What I wanted to do was use =LEFT and just grab the left characters from the cell, so =LEFT of D6, but how many characters do I want?
Well, what I want is I want the length, L-E-N of D6, – 6.
Why 6?
Because we're the letters T-O-T-A-L plus a space before that.
So, basically, I want to take those 6 characters out.
The L-E-N function gives me the length of the entire string, and then I take the 6 characters out, alright?
So, I finish that formula.
I'm going to press CONTROL+ENTER, CONTROL+ENTER, which will enter that formula in only the relevant cells.
[ =LEFT(D6,LEN(D6)-6) ] Now, of course, I'm free to go ahead and choose all of those cells, again, ALT+; to select only those cells, CONTROL+C to copy, and then paste to a new worksheet, CONTROL+V. I now have basically my totals by customer, and it's the original customer name, so I can go ahead and do VLOOKUPs using the L-E-N function in order to figure out the total length of the customer and then subtracting 6.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, here's the question that came in during one of my seminars.
If we go to DATA, SUBTOTALS, and create subtotals by CUSTOMER, COST OF GOODS SOLD, and PROFIT, click OK and then collapse down to the number 2 view, this is a great view of the data.
However, what's frustrating is if someone then needs to do a VLOOKUP to get the total revenue by customer from this, we're going to have a problem because it has the word AIG TOTAL and we just want AIG, and so my solution here was to select all of the visible cells next to those cells.
So, we would use ALT+; to select the visible cells.
What I wanted to do was use =LEFT and just grab the left characters from the cell, so =LEFT of D6, but how many characters do I want?
Well, what I want is I want the length, L-E-N of D6, – 6.
Why 6?
Because we're the letters T-O-T-A-L plus a space before that.
So, basically, I want to take those 6 characters out.
The L-E-N function gives me the length of the entire string, and then I take the 6 characters out, alright?
So, I finish that formula.
I'm going to press CONTROL+ENTER, CONTROL+ENTER, which will enter that formula in only the relevant cells.
[ =LEFT(D6,LEN(D6)-6) ] Now, of course, I'm free to go ahead and choose all of those cells, again, ALT+; to select only those cells, CONTROL+C to copy, and then paste to a new worksheet, CONTROL+V. I now have basically my totals by customer, and it's the original customer name, so I can go ahead and do VLOOKUPs using the L-E-N function in order to figure out the total length of the customer and then subtracting 6.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
