+ Reply to Thread
Results 1 to 18 of 18

Create an Alphabetic List Based on Data in a Worksheet

  1. #1
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Create an Alphabetic List Based on Data in a Worksheet

    I know this cannot be done with formulas, and I am not even sure it can be done with VBA. I am trying to figure out a way to create an alphabetic list with a schedule of when/where names occur in a schedule. Row 1 includes the location and Column A has the times. I need an alphabetic list of when and where names occur (what time and what location).

    I have attached a sample of what a schedule looks like. Basically, what I would like is to be able to create a report that says something like:

    Bronze - 8:00/Court 2, 9:00/Court 2, 11:00/Court 1, 12:0/Court 1
    Copper - 8:00/Court 1, 10:00/Court 2, 11:00/Court 2, 1:00/Court 1

    Any help in figuring this out would be greatly appreciated. Or, if it is not possible, that would be good to know so I can move on to other ideas.

    Thanks!

    AlphaTest.xlsx

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Hi,

    i am not sure whether i can do this or not, but i will try my best.

    one quick clarification, can u provide me list of unique Color Name
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  3. #3
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Thanks! Here is the list:

    Black
    Blue
    Bronze
    Brown
    Copper
    Gold
    Green
    Orange
    Purple
    Red
    Silver
    Teal
    White

    Obviously these are just samples. There would be real names used, but if I can figure out the basic framework, I think I can make the necessary adjustments for future schedule.

    I appreciate your help!

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Create an Alphabetic List Based on Data in a Worksheet

    This should do.
    Attached Files Attached Files

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Hi Racket Stringer and welcome to the forum,

    I needed to insert a row (22?) to make all the times on rows which were a multiple of 3. Then with some Index Match magic I could do a formula to get what you wanted. See the attached. No VBA needed...
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create an Alphabetic List Based on Data in a Worksheet

    @ jindon,

    I got a debug message on running your code.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Quote Originally Posted by MarvinP View Post
    @ jindon,

    I got a debug message on running your code.

    What error?

    No error for me, and I wouldn't post the file if it get error.

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create an Alphabetic List Based on Data in a Worksheet

    I error on this line:

    Please Login or Register  to view this content.
    I don't have any "Missing" in my Tools - > References..

    I must be missing something.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Create an Alphabetic List Based on Data in a Worksheet

    If you are still with Win XP or earlier, it won't work.
    It does not support net flamework.

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create an Alphabetic List Based on Data in a Worksheet

    I'm using the latest Office 365 with Excel 2013, so I'm clueless why I get a debug message.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Make sure that you have Framework installed.

    "C:\Windows\Microsoft.NET\Framework"

  12. #12
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create an Alphabetic List Based on Data in a Worksheet

    @jindon

    This is awesome!! Thank you so much!

    R. Stringer
    Last edited by rstringer; 05-13-2014 at 07:02 PM.

  13. #13
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create an Alphabetic List Based on Data in a Worksheet

    @MarvinP

    Thank you! I was very close to getting this to work then...I have a good bit of knowledge of Excel formulas, but I was struggling to get it to return the correct court numbers.
    Last edited by rstringer; 05-13-2014 at 04:15 PM.

  14. #14
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create an Alphabetic List Based on Data in a Worksheet

    I really appreciate everyone's help with this project :-). Now that I have done some more work, I realize that I probably need to have the list of names already in place on a separate Alpha worksheet and then pull the schedule for each name. There will be 400 or more people on the schedule, and this will make it easier to find if someone is accidentally left off the schedule. I also forgot about the column for the position. I have attached a new sample. The idea would be to have the code use the names in Column A on the Alpha worksheet and pull the related schedule with the time/position/court.

    Black 08:00/R1/Court 6 09:00/R1/Court 6 10:00/R1/Court 6

    Thanks!

    AlphaTest2.xlsx

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Try the attached and see if this is how you wanted.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Jindon,

    The concept is correct, but the results were not complete and they were not in any order that I could discern. For example, when I ran the macro, for "Black" it only displayed three of the nine results (although there were six blank cells) in the row.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Yep, I haven't looked at the new file carefully.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Create an Alphabetic List Based on Data in a Worksheet

    Jindon,

    Thank you! This is perfect!

    R. Stringer

+ 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. I'm trying to create a list of errors from raw data based on a master list
    By aperring in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2014, 10:47 AM
  2. [SOLVED] Create new list based on data from another dynamic list
    By y_not in forum Excel General
    Replies: 6
    Last Post: 01-04-2013, 09:16 AM
  3. Create list of Unique values based on Worksheet Data
    By todmac in forum Excel General
    Replies: 3
    Last Post: 02-09-2012, 08:17 PM
  4. Replies: 3
    Last Post: 01-15-2012, 02:31 PM
  5. alphabetic list - copy each letter to different worksheet
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-17-2010, 08:17 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