+ Reply to Thread
Results 1 to 20 of 20

How to sort room schedule data automatically for a VLOOKUP

  1. #1
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    How to sort room schedule data automatically for a VLOOKUP

    I have a table of data for room schedules. However, a bunch of the fields are combined (such as weekday, month day, and start & end times).
    So, I paste the raw data into one worksheet. On a second worksheet, I use formulas to pull from that data so that each column has only one piece of data (weekday, start time, end time, room name).

    So, why I’m trying to do is first sort everything in that second worksheet automatically according to levels. First by room, then weekday, then by start time.

    Once sorted, I have another worksheet with a drop down data validation list of rooms. The idea was to use a VLOOKUP to pull the schedule data, and display it in a table format where the weekdays are in columns, and the schedule data is displayed in cells below in order of start time using CONTACT to combine the data for display. And then print it.

    Ideally, all someone would have to do is paste the raw schedule data, then go to the display worksheet, select the room name, and print the sheet.
    Now, I sort of been able to put bits & pieces of this together, but haven’t managed to get something to flow from start to finish—I think I’m missing a few steps.

    The main problem I’m encountering, how to get the formatted data into a sorted state that I then can use VLOOKUP to retrieve.
    Could anyone point me in the right direction?

    Thanks

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    Example file attached.
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    I converted the data range on the raw data sheet to an Excel table for two reasons:
    1. Tables copy down formulas automatically, so when you add lines to the table, you don't have to do this manually.
    2. Tables know how big they are so pivot tables, formulas, charts, etc. don't have to be changed when the number of rows change. You can reference exactly the amount of data you need without having to include all million rows+.

    I added some helper columns and these can be hidden.

    Real Date - this is the date parsed out of the Date column. It depends on the date being in this exact format: DDDD, MMMM d, yyyy - there is a little leeway there, but it depends on the day of the week being first and separated from the rest of the date by a comma.

    Start Time - this is the start time parsed out of the Time Column. It depends on the times being separated by a hypen and having AM or PM.

    Date / Time - this is a combination of the date and the time and is used to sort the data.

    Use - this is true if the Room and Location selected on the display schedule page match. I gave these cells names: Sel_Location and Sel_Room. You might consider adding a start and end date for the display and modify this cell if you plan on having more than one week's worth of data in the table.

    I added a lookup sheet, The quickest and dirtiest way to get a unique list of values from a set of data is to use a pivot table and put the item in as the row header. I had to do a bit of a two-step here and used a helper column for both room and location to convert (blank) to the null string, so if it is selected, it will match when one of those items is blank.

    I overlaid both of these ranges with a named dynamic range. Here is more information on that: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

    The result is a lot cleaner drop-down lists. Now that I look at it, I should have made these cascading drop down lists. That is once you pick a location, then you can only pick a room in that location. Other choices will not be available. Maybe I'll work on that next week when I have a chance

    I kept your old pages, but don't use them. You can delete them.

    I notice that you have a lot of famous people working for you .

    This is a good layout, I did not have to do a lot to make it work.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    Thank you very much for taking the time for this.

    However, I get an error message when opening the file with the message:

    Excel found unreadable content in 'schedule example.xlsb'. Do you want to recover the contents of the workbook. If you trust the source of this workbook, click yes.

    And if I click yes:

    Excel was able to open the file by repairing or removing the unreadable content.
    Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))


    I already set the macro security setting to enable, but it still throws that error. Any ideas?

    Thanks

    [edit]: The microsoft office shared featured "visual basic for applications" is installed.

    [edit 2]: Ok, got it working. There was another VBA setting that was disabled, so now I'm able to see the script. I'll play around with it. Thanks!
    Last edited by Force Flow; 01-11-2019 at 05:02 PM.

  6. #6
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    I think I found a small bug.

    If I click the "make schedule" button more than one, it adds duplicate data under the Tuesday column.

    I'm guessing the LRowD is initialized each time the button is clicked, and then loses track of how many rows that have been added?

    If I change this:
    shD.Range("A6:A" & LRowD).EntireRow.Delete

    To this:
    shD.Range("A6:A500").EntireRow.Delete

    Then the data appears to be fully deleted each time that the "make schedule" button is pressed. Though, that probably shouldn't be hard coded...

    I haven't quite fully digested all the code logic yet.

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    I've pressed the button a couple of times. It clears the calendar for me.

  8. #8
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14
    Quote Originally Posted by dflak View Post
    I've pressed the button a couple of times. It clears the calendar for me.
    Try the "multi purpose room" selection. I think I first noticed it on that one.

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    OK. I can duplicate that result. I'll step through the code to see what is happening. It's not a complete duplication.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    I found the issue. I also put in the cascading drop-down list. So when you select a building (location) only the rooms in that location are available for a room number.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    Great, thanks!

    How would be the best way to paste a new table of raw data into the "rawdata" worksheet without messing up the additional columns or header row?

    Normally, I'd highlight everything in the source worksheet, copy it, highlight everything in the destination worksheet, then paste, but that destroys the extra columns and header.

    [edit]: Also, when I do paste new data into the "rawdata" worksheet, the data on the "lookup" worksheet doesn't update and still retains the older data. Would I have to add a new button and VBA function to refresh the data like with the "Make Schedule" button, or could it be refreshed automatically?
    Last edited by Force Flow; 01-14-2019 at 02:02 PM.

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    In this version, there is a button to clear the data on the raw data sheet. Then go to the source file. Copy the data (without headers) in cell A4. Don't worry about the formulas in the helper columns, they will take care of themselves.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    I renamed the two pivot tables in the "lookups" worksheet, and tried adding adding this to the "rawdata" worksheet to refresh the pivot tables on the "lookups" worksheet.

    Please Login or Register  to view this content.
    However, it throws a "runtime error 1004, reference not valid".


    [edit]: I was typing up this post before I saw your post with the clear button. I'll take a look at it--thanks!
    Attached Files Attached Files
    Last edited by Force Flow; 01-14-2019 at 02:31 PM.

  14. #14
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    If I copy the entire table of the source data from another workbook, click clear table, then select A4, and try to paste, I get the error:

    To paste all cells from an excel worksheet into the current worksheet, you must paste into the first cell (A1 or R1C1)
    Attached Files Attached Files
    Last edited by Force Flow; 01-14-2019 at 02:49 PM.

  15. #15
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    Generally speaking, code in a sheet module fires off on an event. In this case, you fire the code any time anything changes on the sheet. Normally, you want to limit it to specific ranges. In this case to any change in the table.

    Please Login or Register  to view this content.
    What I did notice is that the pivot tables were referencing a source outside the workbook. This is consistent with the error. Change the data source for the pivot tables back to Table_Raw_Data.

    Assuming that the raw data is coming from another workbook, I can add code to import this workbook so you don't have to do the copy and paste. I can link this into the same code that I use to clear the table.

    I will clear the table, open the source workbook, copy the data in, close the source workbook and refresh the pivot tables. All you will have to do is supply the name of the source workbook in a cell and put it in the same directory as the spreadsheet running the report.

  16. #16
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    Quote Originally Posted by Force Flow View Post
    If I copy the entire table of the source data from another workbook, click clear table, then select A4, and try to paste, I get the error:

    To paste all cells from an excel worksheet into the current worksheet, you must paste into the first cell (A1 or R1C1)
    Select only those rows that have data to copy. Do not select the entire sheet. See my other post. I can do this for you automatically. Provide a sample source workbook.

  17. #17
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    Quote Originally Posted by dflak View Post
    Generally speaking, code in a sheet module fires off on an event. In this case, you fire the code any time anything changes on the sheet. Normally, you want to limit it to specific ranges. In this case to any change in the table.
    I understood the firing of the event, but not quite how to select table. Thanks, that appears to be working


    Quote Originally Posted by dflak View Post
    What I did notice is that the pivot tables were referencing a source outside the workbook. This is consistent with the error. Change the data source for the pivot tables back to Table_Raw_Data.
    Oops, a mistake with copying & pasting. I fixed it.


    Quote Originally Posted by dflak View Post
    Assuming that the raw data is coming from another workbook, I can add code to import this workbook so you don't have to do the copy and paste. I can link this into the same code that I use to clear the table.

    I will clear the table, open the source workbook, copy the data in, close the source workbook and refresh the pivot tables. All you will have to do is supply the name of the source workbook in a cell and put it in the same directory as the spreadsheet running the report.
    Yes, the data is coming from another workbook. That actually sounds like a much simpler approach. Thanks
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    11-24-2005
    Location
    USA
    MS-Off Ver
    2010
    Posts
    14

    Re: How to sort room schedule data automatically for a VLOOKUP

    Quote Originally Posted by dflak View Post
    Provide a sample source workbook.
    Ok, here's an example of the source data. It actually arrives as a tab delimited format with an xls extension. Would it need to be converted to an actual workbook first?
    Attached Files Attached Files
    Last edited by Force Flow; 01-14-2019 at 03:12 PM.

  19. #19
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    I see an issue with this file. It is a csv file with an XLS extension.

    If I put a CSV extension on it and open it, then I have to do a text-to-columns with a tab delimiter. That should NOT happen. Excel should open CSV files into columns automatically.

    If I open it as an XLS file, then I get a dialog box telling me that there is a type mismatch, but it does open and it opens with columns.

    In either case, there is an extra column (D) that is a combination of class and room. I can live with that. The quickest and easiest way to deal with that is to import it into the table and hide that column.

    The big issue I have is with this hybrid source file. Is it CSV or XLS? Did you download it correctly?

  20. #20
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: How to sort room schedule data automatically for a VLOOKUP

    I got this to work with the XLS extension. I told the program to shut up about the error message and it opens the workbook without complaining.

    There is the extra column (Column D) that you can hide. I notice that the data starts on row 2 - will this always be the case?

    This is how the program works: Fill in the name of the file to import in cell E1. Click the button.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Automatically Sum Totals for Room Bookings based on Price Criteria
    By irishshopper in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2018, 03:18 PM
  2. [SOLVED] If date matches and room matches, return room info in to cell
    By HonorBray in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2018, 12:09 PM
  3. [SOLVED] How to Calculate Hotel room nights booked, for each night, and broken down by room type?
    By salsadantzr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-09-2018, 04:53 PM
  4. Room Allotment:remove allotted room from dropdown list
    By amdrosm in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-05-2017, 04:50 AM
  5. Converting tabular data into room-by-room
    By tomca in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-03-2016, 07:00 AM
  6. Replies: 6
    Last Post: 10-31-2005, 07:05 PM
  7. [SOLVED] room schedule
    By jenn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-15-2005, 12:06 PM

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