+ Reply to Thread
Results 1 to 12 of 12

Sort large list of data from a pulldown menu and reorganize it on another sheet.

  1. #1
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Sort large list of data from a pulldown menu and reorganize it on another sheet.

    I have program I am trying to develop and am having some difficulty in creating a macro to do what I need it to do.

    I have attached a simple versions so you can better understand what I need. The actual application will have many more lines of data and have a longer choice list but overall this simplified version should get me to where I need to be for understanding how to accomplish this on a larger scale.

    It’s important to note that I really don’t understand the whole concept of tables so I would prefer that this be a macro as I can then understand how the process of the macro works by looking at it. I just don’t know how to write it from scratch.


    So this is what it does:
    On Sheet1 you will in your information in A:D. You are forced to choose from the pulldown option in B and C. This list will continue to grow so the macro will need to check to see how many times it needs to run to complete the task.

    Sheet2 is where all the magic happens. You choose the person from whom you want to run the report for in B1. Again it’s a pulldown menu. Once you do that you click on the Run button and it will then take all of the data from Sheet1 (I have included it in yellow for illustration purposes only) and it will then sort the data for just that person into the specific colors as seen in J:Q. Basically it puts the data (date and price) into the correct color column.

    IF you would be so kind as to notate in the code what is actually going on I SHOULD be able to alter it for my specific needs.

    Thank you so very much for your assistance with this.

    -Ken
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    Hi Sungen,

    I am pretty sure there are a few excel guru's out there that can write a macro for this.

    Attached is an alternative that you may find acceptable.

    You can achieve a similar result with a PIVOT table with a slicer.

    See attached file as example.

    Hope this is helpful.

    Cheers
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    Please Login or Register  to view this content.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    You can do it with a few formulae, as follows:

    In F10 of Sheet1 I put this formula:

    =IF(B10<>Sheet2!$B$1,"-",C10&"_"&COUNTIFS(B$10:B10,B10,C$10:C10,C10))

    coloured it blue, and copied it down beyond the extent of data, to accommodate more data being added.

    Then I set up a data validation drop-down in cell B1 of Sheet2 and put this formula in J2:

    =IFERROR(INDEX(Sheet1!$A:$A,MATCH(J$1&"_"&ROWS($1:1),Sheet1!$F:$F,0)),"")

    and this one in K2:

    =IFERROR(INDEX(Sheet1!$D:$D,MATCH(J$1&"_"&ROWS($1:1),Sheet1!$F:$F,0)),"")

    Then this pair of formulae can be copied into L2:M2, N2:O2, and P2:Q2, and then after setting up the colours again you can copy the formulae down as far as you need.

    Then you can choose a different name in B1 and the green table will change automatically.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    Deleted... see post below.
    Last edited by sungen99; 06-29-2016 at 03:27 PM. Reason: needed to add a file..

  6. #6
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    walruseggman- this is what i am looking for..... if i might be able to burden you just a little more....... I have attached the new workbook that contains your macro.


    Lets say that on Sheet1 J1 has a date in it. It will be the same date that will be used for all 4 colors on Sheet2. Lets say then J2 is $100.00, J3 is $500.00, J4 is $600, and J5 is $900.

    What I would like to do it after the macro is run….
    If looks at col J to see if there is anything in it. If so.. it puts the data from Sheet1 J1 and J2. Then it checks L on Sheet2 to see if there is anything there and so on. If there is not anything in the column then it does not put anything there.

    Thank you so much!!!!!
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    If so.. it puts the data from Sheet1 J1 and J2.
    ...where? i don't see on your example sheet where Mike Red 100 is going.

    Then it checks L on Sheet2 to see if there is anything there and so on.
    When did we check J on sheet 2? What is it doing with the values in those columns?

    Sorry, but you example doesn't clearly show me what you want done with the data. Can you perhaps try again show a more clear relationship between the data sets?

  8. #8
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    Hmm, I think this may be what you want. If so, disregards post #7.

    Put this code at the end of your sub. The order of the colors in Sheet 1 Column I doesn't matter.

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    Actually this is ALMOST right. I ONLY want to add the special date and special number IF there is already existing data in that line. Now it looks like its adding it only if there is NOT anything there.

    Thank you!

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

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    Try
    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    I ONLY want to add the special date and special number IF there is already existing data in that line
    Ooops. Try:

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Sort large list of data from a pulldown menu and reorganize it on another sheet.

    YEP....... THANKS GUYS!!! this thread is CLOSED!!!

+ 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. Replies: 1
    Last Post: 02-18-2016, 11:29 PM
  2. [SOLVED] pulldown menu
    By K-Ching in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-20-2014, 10:30 PM
  3. Pulldown Menu
    By scannerdarkly in forum Excel General
    Replies: 3
    Last Post: 07-18-2010, 11:56 AM
  4. pulldown menu
    By electricmason in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-20-2010, 07:00 PM
  5. Programming hyperlink into pulldown menu
    By andrefrancis1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2008, 10:40 AM
  6. [SOLVED] Creating a pulldown menu...
    By Brablo in forum Excel General
    Replies: 10
    Last Post: 04-20-2006, 06:25 PM
  7. Bin and sort a large list of data
    By bsears in forum Excel General
    Replies: 4
    Last Post: 01-25-2005, 05: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