+ Reply to Thread
Page 2 of 9 FirstFirst 1 2 3 4 ... LastLast
Results 16 to 30 of 133

Please comment on what each row is about to do

  1. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,786

    Re: Please comment on what each row is about to do

    You can do the custom sort order in PQ as well: https://stackoverflow.com/questions/...ing-powerquery
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  2. #17
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Please comment on what each row is about to do

    Quote Originally Posted by AliGW View Post
    You can do the custom sort order in PQ as well: https://stackoverflow.com/questions/...ing-powerquery
    That won't change the display order in a slicer though - which is what we wanted in this case..
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #18
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2016, Excel 2019
    Posts
    651

    Re: Please comment on what each row is about to do

    Hi Olly

    Boy you guys sure know how to throw curved balls to someone who is getting to know how EXCEL works, just to
    learn one arena the finds out that the arena is bigger than thought.


    Attached is the original files = 5 mb I have zipped it multiple ZIP files 1 is the top, 2 is the middle 3 is the bottom Roughly 280 rows each file UNZIPPED

    This is the start of the WICEN in row 128 and each of the Cells for wicen the rows are formatted in yellow RED here



    VK1RWP 147.1250 147.7250 VK1RWP - portable WICEN
    VK2RAG 147.1250 147.7250 VK2RAG Someb1 Somersby WICEN
    VK2RAV 147.1250 147.7250 VK2RAV - portable WICEN
    VK2RSR 147.1250 147.7250 VK2RSR Scadns Scaddens Ridge WICEN
    VK1RWP 147.1500 147.7500 VK1RWP - portable WICEN
    VK2RAV 147.1500 147.7500 VK2RAV - portable WICEN
    VK2RWC 147.1500 147.7500 VK2RWC Chats1 Chatswood WICEN
    VK2RWH 147.1500 147.7500 VK2RWH Nwcsle Newcastle WICEN
    VK2RWK 147.1500 147.7500 VK2RWK KurraJ Kurrajong Heights WICEN
    VK1RBH 147.1750 147.7750 VK1RBH BullsH Bulls Head WICEN
    VK1RWP 147.1750 147.7750 VK1RWP - portable WICEN
    VK2RAV 147.1750 147.7750 VK2RAV - portable WICEN
    VK2REP 147.1750 147.7750 VK2REP - portable WICEN
    VK2RFI 147.1750 147.7750 VK2RFI - portable WICEN
    VK2RWS 147.1750 147.7750 VK2RWS - portable WICEN




    Pete
    Attached Files Attached Files

  4. #19
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Please comment on what each row is about to do

    Okay - it looks like you've already done some sort of manual manipulation to those files?

    We could quite easily combine them in a query. But better to work with your actual raw, source data, to eliminate any requirement for initial manual processing.

    Can you post your actual RAW source data?

  5. #20
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2016, Excel 2019
    Posts
    651

    Re: Please comment on what each row is about to do

    I have that is how it is sent to me I cannot put up the complete file as it is too big. over 5gb

    I will make a call now and ask for the file UNMARKED stand by \.......................................

  6. #21
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2016, Excel 2019
    Posts
    651

    Re: Please comment on what each row is about to do

    Here is the raw .CSV file


    Remove the Temp at the end
    Attached Files Attached Files

  7. #22
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Please comment on what each row is about to do

    Ignore this post - you've since posted a CSV file. See my next post for a CSV version.
    Last edited by Olly; 06-05-2019 at 05:19 AM.

  8. #23
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Please comment on what each row is about to do

    And here's a version which picks up data straight from the CSV file:

    VK Data:
    Please Login or Register  to view this content.
    You can name the CSV file anything you like - and when you get an updated dataset in CSV format, simply change the filename parameter, and refresh all data, to update the report.
    Attached Files Attached Files
    Last edited by Olly; 06-05-2019 at 05:21 AM.

  9. #24
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2016, Excel 2019
    Posts
    651

    Re: Please comment on what each row is about to do

    Hi

    I have just looked at the wicen sheet, and I have activate the HEADINGS and noticed some peculiarities as far as the row
    numbers they start at 38 and go up to 785 ?? I know there is only 56 rows for wicen as it stands

  10. #25
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Please comment on what each row is about to do

    ?

    Look at VK Data v1.3xlsx atachment, in my post #23.

    There are indeed 56 WICEN rows. Don't worry one bit about row numbers, they're completely irrelevant. We could change the sort order of the query, and the actual row numbers for each subset of data would change again.

    EDIT: You do understand that all the data is being presented in one table, on one worksheet, and the slicer at the top of the page is merely filtering that table?
    Last edited by Olly; 06-05-2019 at 05:29 AM.

  11. #26
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2016, Excel 2019
    Posts
    651

    Re: Please comment on what each row is about to do

    Olly MANY MANY THANKS

    I dont want to sound ungrateful can you code th esheets in this order please..

    "VK1", "VK2", "VK3", "VK4", "VK5", "VK6", "VK7", "VK8". "Wicen","VK23cm", "VKDStar", "VKP25", "VKDMR", "VKC4FM", "VKATV",


    I like to be able to reward you somehow.

    Peter

    EDIT I found that I can't use my scroll wheel on the mouse in the sheets.
    Last edited by Potholes; 06-05-2019 at 05:37 AM.

  12. #27
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Please comment on what each row is about to do

    To change the order of the slicer, simply edit your Custom List (File > Options > Advanced > Edit Custom Lists)

    Or run this VBA code once:
    Please Login or Register  to view this content.
    Happy to have helped. I'll feel rewarded if you feel you've learned something new about how you can use Excel

  13. #28
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2016, Excel 2019
    Posts
    651

    Re: Please comment on what each row is about to do

    Olly attached is a screen shot of what I see with your file, is there anyway I can get it to go side
    to side in the screen as the digits are a bit small for my old eyes.
    Attached Files Attached Files

  14. #29
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,985

    Re: Please comment on what each row is about to do

    What do you mean, "side to side in the screen" ?

    Are you asking how to change the zoom level? (slider in bottom right corner of Excel window)

  15. #30
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2016, Excel 2019
    Posts
    651

    Re: Please comment on what each row is about to do

    OK thanks for that one


    Now one last thing how do I use that code on the Parameters sheet to update the data for new ones please.

+ Reply to Thread
Page 2 of 9 FirstFirst 1 2 3 4 ... LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1