Page 2 of 2 FirstFirst 1 2
Results 11 to 20 of 20

Thread: Printing Slicer Contents & Saving to .txt File

  1. #11
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #12
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    I understand exactly what you want to do - it's just that you can't without specifically selecting the cells containing data. Sorry!
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #13
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    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.

  4. #14
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    Let me know if you need any help with it.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #15
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    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
    Attached Files Attached Files

  6. #16
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    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!

  7. #17
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    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
    Enthusiastic self-taught user of MS Excel!

  8. #18
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    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.

  9. #19
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    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.

  10. #20
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,282
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 2 of 2 FirstFirst 1 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •