+ Reply to Thread
Results 1 to 18 of 18

Adding Preset Data to Template with Lists

  1. #1
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Adding Preset Data to Template with Lists

    Okay, so I have a spreadsheet that has nearly 15,000 brands on column A and their appropriate brand keys on column B. It is rather annoying having to go to this every time I need to find a brand key and I would like to make a list of the brands so that I can choose from the appropriate one and then the cell to the right automatically matches that brand to the appropriate brand key.

    I figured I could use AutoFilter and some formulas to do this, but it would take forever to scroll through the thousands of brands to get to one that starts with a letter like Z.

    I thought about having multiple AutoFilter lists, one for each letter of the alphabet, but that would just take up too much space and even if I did, there are over 2,000 brands that start with the letter M alone.

    I don't know if there is a setting in AutoFilter that enables you to search within the list by typing or something of that nature, or if there is something else besides AutoFilter that would do the job instead, but I just need an easier way of sorting through this list of brands.

    Any ideas?
    Last edited by amyxkatexx; 03-09-2010 at 12:44 PM. Reason: Changed Title

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Split Large AutoFilter List into Chunks

    Amy, are you familar with VLOOKUP?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Split Large AutoFilter List into Chunks

    Yes, I am familiar with VLOOKUP, but I guess not enough to see how that would help.

    Please enlighten me.


  4. #4
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Split Large AutoFilter List into Chunks

    Bump, no response.

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Split Large AutoFilter List into Chunks

    Have you looked at the advanced filter? Using a criteria range, you can type in text that matches the start of your data, then filter to restrict the view to only matching records.
    Everyone who confuses correlation and causation ends up dead.

  6. #6
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Split Large AutoFilter List into Chunks

    Thanks Romper, that seems like that will work, but I'm having trouble understanding how to do the criteria range part. Do I just copy the same range that I have in the List Range part? Also, I really don't want my list on the same sheet as the AutoFilter, but it says that I have to have them on the same sheet whenever I specify otherwise. I haven't messed with AutoFilter a lot in the past so this is kinda all new to me.

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Split Large AutoFilter List into Chunks

    It would be helpful to see a sample workbook so we can work out what it is you are trying to achieve. My intention was that you use the advanced filter on your original list to restrict the data you are viewing.
    The criteria range is a range (at least two rows) with the first row having headers that match your list headers. If you want the filtering to happen automatically as you type, you can use code to achieve that, but again, we'd need more detail.

  8. #8
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Split Large AutoFilter List into Chunks

    I have no problem making a sample sheet if necessary, but I almost think that might confuse you even more because I still haven't even really gotten a start on it because I am not sure what to do just yet.

    Let me explain more what the full idea is and see if anyone knows of a solution.

    I get in sheets each day that have information that I use to cross reference with spreadsheets I already have to fill in the blanks. For instance, I will get in a sheet that has a brand name in column AC. Next to it, in column AB, is where the Brand ID is supposed to go (that is a numeric key that our database can read). The brands will always be in column AC, but they may not always be written exactly how they are supposed to (because people make mistakes). So, I have to copy the brand then open up a spreadsheet that has all of the Brands and Brand IDs on it (columns A and B), then search column A to find the Brand I am looking for. Once found, I copy and paste both the brand and the brand key back into my original sheet.

    This can be quite a task, especially when there is a mispelling on the original sheet. What I am hoping to do is make a template which will essentially have all of these brands and brand keys in some sort of list format that I can simply have both the template and the original sheet up at once and work on the template until all the information necessary is filled in (there are quite a few others like this as well), and then I can write a macro that will take everything on the template and paste it into the appropriate cells on the original sheet.

    The problem with having this huge list on my template is that it takes forever to scroll to the correct brand and I want this to be the simplest method possible. And using the Find pop-up just takes too long. I like the idea of using code to have it autofilter the list as I am typing, but I really don't want the list to be directly below the cell in which I am typing in, if that makes sense.

    Hopefully this gives you a better idea of what I am looking for.

    Thanks!

  9. #9
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Split Large AutoFilter List into Chunks

    If you have one master list with all the reference data in it, then I would probably create an add-in with a userform that has a textbox for the filter value and a listbox to display the matching results, using the textbox's Change event to filter the list as you type.
    Unfortunately, I've just been handed a huge data clean up operation to do, which makes it unlikely I would have time to do this for the next couple of weeks...

  10. #10
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Split Large AutoFilter List into Chunks

    Well, that sounds exactly like what I want to do. I'd prefer to do it myself, as I am trying to learn more about excel. I'll do some googling, but if you have any pointers you could give me or websites that might be helpful, that would be amazing. I know you are busy though, so only if you have time.

    Thanks so much though for all your help!

  11. #11
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Split Large AutoFilter List into Chunks

    I'd suggest you start out and post any specific questions here as you come across them. I confess, I've never tried to set up anything like this on a Mac, so I don't know what the pitfalls might be.

  12. #12
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Split Large AutoFilter List into Chunks

    Okay, so here is what I got so far...

    Please Login or Register  to view this content.


    The part in bold is where I get a Run-time error, so I haven't gotten very far, sadly.

    I've attached the file I am working with, but I had to cut down the list of Brands so that I could get it to attach because the file was too large. I also have added a list of Manufacturers as well.

    I am using the guides from these sites to help me create this.

    User Forms 1
    User Forms 2
    ComboBox Lists

    Anyone know what's wrong with my code?
    Attached Files Attached Files

  13. #13
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Adding Preset Data to Template with Lists

    Your BrandIDList range is defined incorrectly - the definition uses COUNTC rather than COUNTA. If you change that it should be fine.

  14. #14
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Adding Preset Data to Template with Lists

    Okay, wow, what a silly mistake. Thanks for catching that for me.

    So it's working now, but when you select a Brand and a Mfg and tell it to add to template, it is only adding the Mfg ID and the Brand ID, instead of both. How can I fix that to where it fills out both columns?

  15. #15
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Adding Preset Data to Template with Lists

    Also, each time I run the userform it overwrites the data that was already there instead of pasting it in the row beneath the last row of data.

    What can I write into my code to fix this and where do I put it?

  16. #16
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Adding Preset Data to Template with Lists

    Your code does fill out both columns as far as I can see?
    It also checks column A to find the next row to put data in. If column A is not filled out, it will overwrite the data.

  17. #17
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Adding Preset Data to Template with Lists

    Well, can I change it to where it isn't just checking column A, but all columns for any data at all? Column A may not always have data in it at the time this userform will be run.

    Also, I don't know how it is letting you add data to both Mfg ID and Mfg Name, and Brand ID and Brand Name, mine is only putting the ID's on the sheet and not the names.
    Last edited by amyxkatexx; 03-09-2010 at 02:27 PM.

  18. #18
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Adding Preset Data to Template with Lists

    Mine wasn't adding names - I thought you were saying that yours was only adding one of the IDs. Try this version - I've added a new function to get the last used row in the sheet and code to add the names from the comboboxes too.
    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)

Tags for this Thread

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