I was asked the following:
[ul][li]A file is filtered such that only the data that is needed is displaying. Apparently (I don’t use the Filter, so I don’t know) the rest of the data that is not being displayed is still out there and there is a time penalty having it there. Once data is filtered, how do you delete the rows/columns that are not being displayed, leaving only the filtered results in the spreadsheet?[/li][li]The data of interest comprises the purchases of our products made by some 140 divisions of a company. The company would like to know what divisions bought what. For some reason, they would like each of the 140 divisions to have its own page in the workbook. How can we move each division into its own workbook page without cutting and pasting? In effect, how do we make a ‘page break’ for each division such that each page is moved to its own worksheet?[/ul][/li]I told the boss-lady I’d ask my imaginary friends, who have been so helpful in the past if they knew how to do that.
I am not an expert on Excel though I do use it in my job. If you have filtered a sheet and you don’t want the unfiltered data, my suggestion would be to copy that page and paste it into a new sheet. You will have to redo the formatting but that will remove the unwanted data.
I can’t imagine having 140 separate worksheets but as far as I know, the only way to move them would be cut and paste.
I think you need a pivot table, basically. They are not hard to do and you can select what data you want to display.
No idea how to make or use a pivot table. I get one file that is one, and I c&p it into another page so that I can do anything with it.
I can’t imagine why they’d want 140 separate pages either. Why not have one worksheet with each division segregated? But this is what I’m told they want.
(NB: This is not my job, nor my project. Just trying to be helpful.)
Here’s something I was thinking of on the way home.
Import the file into Excel. Set up a tab for each division, and save this as a ‘shell’ or ‘template’. Then use VLOOKUP to get the data for each tab. If the division name = one division, get all the information for that. Copy to the next tab and change the division name. Once it’s set up, the other worksheets will be populated by simply replacing the input file, right? (Not that I actually know how to do all of this, but I might be able to figure it out with my notes and your help!)
The other thing I thought of was doing the same thing, only with a macro on each worksheet instead of VLOOKUP. Can this be done? I have no idea how to write a macro.
At the very least, I can use my Easytrieves to create a delimited file from the fixed-position text file so that it can be imported in about a minute instead of the boss importing the whole file and editing that part manually.
Short answer for bullet #1: invert the filters.
Use the filter option to hide everything you want to keep instead of the more typical displaying what you want to keep, delete the exposed rows, then clear the filters, leaving the stuff you originally selected through filtering.
While I can think of a couple of cumbersome ways to copy data from one worksheet to multiple worksheets, my first thought would be to look at the ftp routine to see whether it allows a worksheet to be defined on the transfer. If so, simply set up the file that is to be downloaded to be sorted by division, inserting an ftp control record specifying the separate division-specific worksheet at every control break.
As long as we’re discussing Excel, I have another question: how do I export a column of e-mail addresses from Excel into the “TO:” section of Outlook? I have several hundred addies i would send an e-mail to, but I can’t highlight, cut and paste this column without needing to insert semi-colons in between each one. I sure don’t want to do that a few hundred times if I can help it.
Regarding the OP, I’m with Dorabella. A pivot table gives them a ton of options when it comes to focusing in on what you want to see.
For pseudotriton ruber ruber, a quick little macro will do that for you. Copy this code into the macro window:
Function ConcatEmail(vRange As Range, vDelimiter As String)
'Variable for each cell
Dim cl As Range
'Loop through each cell and add the delimiter
For Each cl In vRange
ConcatEmail = ConcatEmail & cl & vDelimiter
Next cl
' Drop the last delimiter at the end, allowing for multiple character delimiters
ConcatEmail = Left(ConcatEmail, Len(ConcatEmail) - Len(vDelimiter))
End Function
Then to call it, just enter this into a cell:
=concatemail(A1:A133,";")
A1:A133 is the range of cells with the email addresses, while the “;” is the delimiter. I could have hardcoded the delimiter, but left it variable in case you need to put commas or something else in the future.
I just tested this in Excel 2007, but as it only uses pretty standard functions in the code, it should work fine with other versions.
I should probably add that you could get really fancy and actually have Excel add the results to an outgoing email and even have it perform the mailing itself, but that is a bit more effort.
This is how I’ve always done it (until I discovered how to use pivot tables). There is an extra step once you’ve deleted the unwanted data though - reorder by one of the columns in order to get rid of the blanks that will inevitably be dotted through the data.
Since I’ve just rolled out of bed after a lot of eating and six hours of sleep, I hope I can make sense. As I said, the customer wants a separate page for each division. I’m not sure how we’ve sent them their data before, or even if we were sending this to them before. I know that they were receiving similar data from a competitor. I’m not in the loop. ‘Sue’ provided them the report, in a fixed-position text file, that we receive. They said, ‘But we want it the same way Company D gives it to us.’ Our IT guy was in the office for his weekly visit, so she asked him if he could make the report in that format. He didn’t know how to, but he asked someone else (outside of the company) who might. Someone mentioned, ‘Hey, Johnny L.A. writes programs all day long. Why don’t you see if he can do it?’ So they came to me. I told them Easytrieve can strip out the customer’s data from the report, and write it to a file. (The report contains data for many customers.) I said that I could also write each division to a separate file. Since ‘Sue’ had imported the text file into Excel using fixed-width, I said I could write the file(s) with delimiters so that it would be easier to upload. But Easytrieve only writes fixed-position text files. I can make files with any data they want, but I’ve no way to write a file directly to Excel, nor do I know how to tell Excel, IF DIV = ‘DIV A’ [write the record to Worksheet 1] and so forth.
This sort of thing is actually something that should be done by our data house. But they charge a fortune, and we’re a non-profit company that needs to conserve resources wherever possible. So this is a case of ‘Let’s see if we can do it ourselves.’ When I had a few minutes after finishing my real work, I ended up writing a text file that contains only this customer’s information, with a line break between each division. ‘Sue’ thought it was neat, but she can do what I did manually. Of course, my way it takes like a minute to open Easytrieve and create the file. At the very least, just getting to that point saves a lot of time.
Anyway… As I said, I’m not in the loop. Just before I left the office Wednesday ‘Sue’ showed me the file she was making manually. She seemed to be deciding what she wants on each page. So at this point I don’t even know what the finished report is supposed to look like – except that I know each division needs to be on its own worksheet. But it doesn’t matter what the final report is supposed to look like. It’s easy to write the data in any layout using the Easytrieve tool I use every day. The part I don’t know about is making Excel do with the data what ‘Sue’ wants to do with the data.
Filtering
I’ll pass the suggestion about ‘inverted filtering’ on to ‘Sue’. That will answer her first question. Only, I can do the same thing for her much faster with Easytrieve.
I’m not sure what you mean about the ftp routine. AFAIK Company E just emails the text file to us. Or they may have the text file available on their server, and ‘Sue’ selects it and saves it locally as a text file. (Probably the latter.) I don’t know what an ftp control record is. It sounds like there would be a record in the text file that would tell Excel to write subsequent records to a worksheet, and when another control record is reached write subsequent records to a different worksheet and so on. Is that correct? I can use Easytrieve to insert something that would indicate a new division. For example, I could put a ‘1’ at the end of the record or I could do what I’ve done and put a blank line between divisions. The trick is for Excel to say, ‘Oh, here’s a different batch of data. I’ll write it to a different worksheet.’
As far as ‘cumbersome ways’, I was thinking of a ‘shell’ if Excel can’t distribute the data automatically. As I said, I could make a file that contains all of the code, and that file could be opened each month, the raw data (or the output I’ve described from the Easytrieve) could be inserted into a spreadsheet and the Excel formulas can distribute it. Then the new file could be saved. I assume that this could be done with VLOOKUP. It would take a lot of time to set up, but once it’s done the first time, it’s done. The only issue I can think of (other than that I hate using VLOOKUP because I’m not experienced with it) is that if I put 100 rows of it on each page then there would be a lot of blank records instead of only having the number of records matching the number of records in the input file. I thought maybe someone might know how to write a macro that distributes the data, making a ‘nicer’ file. (As I said, I don’t know how to do macros. But again, if I can get some help to do it once, then it’s just a matter of copying the ‘shell’.) What I’m looking for is something like what I would do in Easytrieve to write separate files, like this:
IF DIV = ‘DIV1’
PUT FILE1
ELSE
IF DIV = ‘DIV2’
PUT FILE2
ELSE
.
.
.
END-IF
END-IF
Only instead of Easytrieve writing the divisions to separate files, I’d like Excel to use the same sort of logic to write from one ‘master’ worksheet to different worksheets in the same file.
I also have access to Access. We use Access to make reports, but that’s not my bailiwick at all. I only use it because we get a lot of data as Excel files and Easytrieve requires fixed-position text files. That’s the limit of my knowledge there. The company paid for a woman to go take classes in Access, and she created our webpage-looking Access database. (The way I use it is much, much less sophisticated.) Then she took off for a job that paid better. Since then TPTB are leery about paying for someone’s education, since they don’t want to pay for someone to go find a job elsewhere. But if there’s a way to create this report in Access, then I’m sure the company can find someone to do it. (I’d be happy to get the training, and I’ve no desire to leave the company. But they’ve been burned once.)
It’s not my job, and if I can make the report the way ‘Sue’ wants it I won’t get anything out of it. But it’s a puzzle, and I can’t resist it. I’m grateful for any help you can provide.
I’m basically bypassing all the previous responses, apologies if this is too redundant, but you’re basically describing the entire purpose of pivot tables. Excel makes them really easy and perfect for this solution. An alternate solution would be to import everything to Access, as databases make this type of selective data retrieval much easier than Excel.