+ Reply to Thread
Results 1 to 12 of 12

Need macro to display information based on given data

  1. #1
    Registered User
    Join Date
    06-22-2022
    Location
    Virginia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    7

    Need macro to display information based on given data

    Hi

    I am sure there is a way to do this, just need help in figuring it out.

    I am given the info in yellow in my example. So I have to enter information into a system but need the starting points (these are in a group of 24) to enter them.

    First, I am given Start and End numbers of DLs. In the attached example I am building members 1 - 312. Based on that, I need 13 entries in this example (312/24 = 13). So I need my DL list (in green) to be 1, 25, 49, 73, etc, and stop and 289 (since 313 would be next, and I only need 312).

    Here might be a tricky part. I also have to include PMs (which are also given). But if I am told to Skip members I have to skip numbers x97 - x100. If not then I can include them in my list.

    Please see my attached example, the expected results are in Green.

    Please let me know if this makes sense or if you have any questions.

  2. #2
    Registered User
    Join Date
    06-22-2022
    Location
    Virginia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    7

    Re: Need macro to display information based on given data

    Am unable to attach file. Maybe because I just signed up? How long once I activate can I post?

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    Re: Need macro to display information based on given data

    Are you able to attach a screenshot at least?

  4. #4
    Registered User
    Join Date
    06-22-2022
    Location
    Virginia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    7

    Re: Need macro to display information based on given data

    Still can't attach a screenshot or my file. Didn't know it would be an issue!!

    Need something that will generate the DL and PM lists based on the info given

    Appreciate the help.


    GIVEN: Example 1 (Skipping PMs)

    DL Mbr Start
    1

    DL Mbr End
    312

    PM Mbr Start
    2001

    PM Mbr End
    2295

    Skip PMs
    Yes
    Skips x097-x100

    Expected Results

    DLs PMs
    1 2001
    25 2025
    49 2049
    73 2073
    97 2101
    121 2125
    145 2149
    169 2173
    193 2197
    217 2200
    241 2224
    265 2248
    289 2272



    GIVEN: Example 2 (No Skips in PM)


    DL Mbr Start
    1

    DL Mbr End
    312

    PM Mbr Start
    2001

    PM Mbr End
    2312

    Skip PMs
    No


    Expected Results
    DLs PMs
    1 2001
    25 2025
    49 2049
    73 2073
    97 2097
    121 2121
    145 2145
    169 2169
    193 2193
    217 2217
    241 2241
    265 2265
    289 2289
    Last edited by mikester; 06-23-2022 at 07:05 PM.

  5. #5
    Registered User
    Join Date
    06-22-2022
    Location
    Virginia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    7

    Re: Need macro to display information based on given data

    Attaching file.
    Attached Files Attached Files

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    Re: Need macro to display information based on given data

    In 1st expected results, I could see DLs and PMs has grown with step 24, except D10 to D11, step 3.
    Is it from PMs cell A15 = "skip x097-x100 " condition?
    How 2197 - 2200 belongs to this criteria? because that conditon is 1097 or 2097 or 3097? not 2197?

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,794

    Re: Need macro to display information based on given data

    Named ranges:
    DL_Mbr_End =Sheet1!$A$5
    DL_Mbr_Start =Sheet1!$A$2
    PM_Mbr_End =Sheet1!$A$11
    PM_Mbr_Start =Sheet1!$A$8
    Skip_PMs =Sheet1!$A$14
    NumEntries =Sheet1!$F$1

    SkipStart =Sheet1!$A$15
    SkipEnd =Sheet1!$A$16

    ** Data validation in A14 ----> Yes, No **

    calculate DLs (C2):
    Please Login or Register  to view this content.
    For PMs,
    D2:
    Please Login or Register  to view this content.
    D3:Dn:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  8. #8
    Registered User
    Join Date
    06-22-2022
    Location
    Virginia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    7

    Re: Need macro to display information based on given data

    Bebo --- I apologize. I should have looked at the numbers more closely in my first example. I want to skip 97-100, 197-200, ....., 997 - 1000, 1097 - 1100, ----- 2097 - 2100, etc.
    Correct values should be:
    1 2001
    25 2025
    49 2049
    73 2073
    97 2101
    121 2125
    145 2149
    169 2173
    193 2201
    217 2225
    241 2249
    265 2273
    289 2301



    Leah - thanks, I'll check it out.

  9. #9
    Registered User
    Join Date
    06-22-2022
    Location
    Virginia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    7

    Re: Need macro to display information based on given data

    Leah --- thanks very much, I need a couple of adjustments the more I think about this. Won't always be 13 entries per say, could vary depending on the order.

    I see what you are trying to do with the Name Ranges, but I was hoping that I could just enter the numbers in the yellow parts and it would adjust accordingly. So if I only need 96 members in DL, my DL list would just stop at entry 73 (since the next entry would be 97 and I don't need that one).

    Also, sorry I didn't make this clear but if I skip PMs it just won't be 2097 - 2100. I meant like 97 - 100, 197-200, 297-300, ------ 1097 - 1100, 1197 - 1200, ----- 2097 - 2100, 2197 - 2200, etc.

    Thanks again for the help. Sorry if I didn't make myself clear at the begining.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,794

    Re: Need macro to display information based on given data

    v.2 Modified to determine number of entries based on start/end calculations.

    C2:
    Please Login or Register  to view this content.
    D2:
    Please Login or Register  to view this content.
    Counts are reported in F2:F3

    I'm not sure about the "skip" option...
    Attached Files Attached Files

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    Re: Need macro to display information based on given data

    It requires:
    A14 = Yes or No
    A15 contains limit range, end with 000-x000 (with "000" is 3-digit number)

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-22-2022
    Location
    Virginia
    MS-Off Ver
    Microsoft Office 2016
    Posts
    7

    Re: Need macro to display information based on given data

    Leah and Bebo--- thanks, it is so very close! Thanks for all the help and info!

+ 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. [SOLVED] display information based on two cirteria
    By kobiashi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-20-2018, 08:57 PM
  2. Linking Data between sheets that only display information based on criteria
    By Alan.Vivian in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-04-2016, 11:10 PM
  3. How to display information based on date?
    By bryceowen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2008, 04:13 PM
  4. [SOLVED] HOW DO I HAVE A CELL DISPLAY INFORMATION BASED ON 2 OTHER CELLS
    By CC in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  5. [SOLVED] HOW DO I HAVE A CELL DISPLAY INFORMATION BASED ON 2 OTHER CELLS
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 02:05 PM
  6. [SOLVED] HOW DO I HAVE A CELL DISPLAY INFORMATION BASED ON 2 OTHER CELLS
    By CC in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  7. [SOLVED] HOW DO I HAVE A CELL DISPLAY INFORMATION BASED ON 2 OTHER CELLS
    By CC in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM

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