Printing Slicer Contents & Saving to .txt File

jdanniel

Member
Joined
Jul 16, 2018
Messages
84
Reaction score
0
Points
6
Excel Version(s)
MS365
I'd like to print out the content of a slicer, or save its contents to a text file. Is this possible? Thank you!
 
Last edited by a moderator:
Still trying to figure this out. Google hasn't been as helpful with this one as I had hoped. Thank you!
 
I'm afraid I'm not clear about what you are trying to do. What do you expect the contents of the text file to look like? And why do you need to do this, anyway?
 
Ali,
My spreadsheet has three slicers. One of them lists all of the "Artists" in the spreadsheet. (It's a music inventory spreadsheet. I also have a database for it, but I prefer spreadsheets.) Anyway, I figured it would be a lot easier to print out a list of artists via the slicer. That's all I want: A list of the artists. I know I could copy the Artists column to the Clipboard and paste it into Word. But I'd have to edit that list quite a bit.

So what I'm really asking is: What is the absolute easiest way to print out a list of artists from this particular spreadsheet, without having duplicates in that list? I've done some reading on this, but honestly, being a novice, I'm a little bit fuzzy with the details.

If the list has to be in table format, I can work with that. I can probably convert the table to text, and have what I'm looking for. Or, if Excel allows you to print out reports, similar to Access or FileMaker Pro, that would work, as long as there aren't duplicates.

Which direction do you think I should be steered in?
 

Attachments

  • DL Music 2019.xlsm
    105.4 KB · Views: 7
Copy and paste the column of artists’ names from the data table to a new worksheet, then, with the copied data still selected, click on the Remove Duplicates option on the Data ribbon. Once you have done this, sort the unique list alphabetically. Then just copy the list and paste it to wherever you want it. All done!
 
TOO easy. TOO simple. Ali, Thank You!
 
Ali,
I have a follow-up question, if you don't mind my asking it.

When I select Column A and copy it to the Clipboard, it includes all of the blank cells below the last cell that has data in it.

So, if I paste the text in that column into Word or Word Pad or Notepad, I have seemingly endless pages of blank space that I have to delete. I don't yet know of any easy way to delete all that blank material.

I know one workaround to this problem: I can simply select ONLY the cells in Column A that have data. That's easy, and it works, but it's a little time-consuming. Not a huge time waster, but it's something I'd rather not do. Eventually, I'll have a few hundred artists in that column. That will make selecting that column more time-consuming than it is now.

So…my question is: Is there a way to select the data in Column A without including all of the blank cells at the end? Thanks again! Jd
 

Attachments

  • Music Catalog with Artist List.xlsm
    119.1 KB · Views: 6
When you have removed duplicates, select just the cells that are left after sorting and paste those across. You are making this more difficult than it needs to be, honestly! ;)
 
Here's a way to generate the list using Power Query - this is set to refresh every minute, so all you have to do is copy and paste from this list to your other documents.
 

Attachments

  • Music Catalogue - Artist List with PQ.xlsm
    126.7 KB · Views: 7
Ali,
I'm sorry...I don't think I'm on the same wavelength with you here. Let's put the Power Query thing on hold for just a moment. Let's talk strictly the column with duplicates removed. Even AFTER I remove the duplicates, I still get all those trailing cells. Unless I'm doing something completely wrong, I can't eliminate those extra cells. The only way to remove those cells is by manually selecting the cells that have data in it. Like I said in my previous post, it's easy and it works, but it's time consuming. I currently have 195 artists, which means I have to select 195 cells. Unless there is no other way to do this, and unless this is the only method possible, I'd prefer not to select these cells manually. Eventually, I'll probably have 500 cells.

Now, regarding the Power Query: I'm going to have to study this, learn this, and understand this, because right now, I don't. I'm not at that point yet. Yeah, I probably am making this much, much more difficult than it needs to be, but it's all part of the learning process. Once I get on your wavelength and figure out what you did and how you created the PQ, then perhaps this will all be a piece of cake. Until then, I'm workin' on it!
 
I understand exactly what you want to do - it's just that you can't without specifically selecting the cells containing data. Sorry!
 
Okay...now I know! (grin) I'm going to study your Power Query. I understand queries in Access (I even know a little SQL--not much, but some), so hopefully I'll be able to get the gist of Excel queries. Thanks again for your help.
 
Ali (and whomever else may be reading this),
I'm pretty sure I created a Power Query. I'm using Office 365 and Excel 2016, and I learned Power Query is in the Data menu under "Get Data."
So, please let me explain in detail what I've done.

1. Data/Get Data
2. From File/From Workbook/I select the spreadsheet file I'm currently in.
3. Navigator/Table 1
4. I select Edit
5. I select all of the columns except Artist, then select Remove Columns.
6. Close and Load
7. In the new worksheet, I rename the sheet "Power Query."
8. I sort the column.
9. I click Remove Duplicates.

Voila…I have what appears to be a Power Query of my Artist column. At least, I think I do.
Hopefully, I've done this correctly so far. Have I?

If I have done everything correctly so far, then this is the point where I get lost. I put in a test row at the end of the Main worksheet.

The test data is as follows:

Artist: ZZZAAABBB
Album: Test
DVD #: 1000
Year of Release: 1999
Date: Ctrl + ;

So, now I have one row that was put into the worksheet after the Power Query was created. I do not know how to update the Power Query so that subsequent entries are displayed.
If I select Refresh, then it just replaces the PQ column with the original data, complete with duplicates. Definitely what I don't want.

What should I do, to get that column updated, without it refreshing to its original state?

I'm wondering…should I select the modified data in that column, and then select Convert to Range?

Or, do I need to use the Power Query Editor to update the column? If so, what's the procedure for that?
Have I made any progress with this? Thank you! Jd
 

Attachments

  • Power Query Test 1.xlsm
    136.6 KB · Views: 6
I think I figured it out. When the Power Query worksheet is open, a menu at the top of the screen appears, named Query Tools. I click Refresh in THAT menu, not the Refresh in the Table Tools menu, and it updates the PQ.

I think I got it!
 
Well done! I did set the query to refresh once a minute, though, so add something to the main sheet, wait a minute or two and then check if it has done it. To see the wuery itself, click on Queries & Connections, then double click the query that appears in the pane on the right - this launches the PQ editor, and yoyu can step through what I did. :)

Connection properites under Refresh is where you will find to option to set an automatic refresh, by the way. Having now read your earlier post, yes, I think you've got it - almost! The removal of the duplicates and the sorting should also be done in PQ. Have a look at my sample file and the steps I took.
 
Ali, I'm really sorry. I spoke too soon. I can get the power query updated, via the method I described above (Query Tools/Refresh). But I was wrong about something.... that is restoring the duplicates. I thought it was not restoring the duplicates, but it is. And when I select Page Down, it goes down to Row 4527.

I'm sorry. I do need a bit of help with this, after all.
 
I posted that last request for help before reading your most recent reply. You said the removal of duplicates and the sorting should be done in PQ. I was doing the duplicate removal via the Table tools. I used the PQ Editor to remove duplicates, and I think that solved the problem. Let me run a few more tests, so I don't come back and say I spoke too soon.
 
Don't worry - PQ takes a bit of getting used to! As I said, have a look at the sample file I provided to you. Run the query and look at the steps I added, which:
1. Removed the unwanted columns.
2. Removed the duplicates from the remaining column.
3. Sorted the remaining column alphabetically.

If you don't do the duplicate removal in PQ then you are creating extra work for yourself, essentially.
 
Back
Top