+ Reply to Thread
Results 1 to 28 of 28

Convert 2 lists into 1

  1. #1
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Convert 2 lists into 1

    Hello all,

    I need to create a name range that is the total of 2 name range.

    I can also create a new column with the total lists, but i want it to be flexible cuz i will insert several more items to lists. Hope you can convert this.

    What i have is: in (sheet "login")
    Column E Column Z
    Username Username
    A D
    B E
    C

    I need to have another list called: in (sheet "Validações")
    Users
    A
    B
    C
    D
    E

    Once in a while i increase users so i need this to be automatic in order not to forget.

    Hope some one can help me

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    What are the actual range addresses for the lists in the Login sheet?

    Where EXACTLY do you want the new combined lists to appear?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    The atual range adress are in login sheet
    G8:G20 but some are empty for new users
    And T8:T20 in login sheet as well.

    I want to put the combine list in worksheet("validações"). Range("L2:L")

    Hope you can understand me

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    Quote Originally Posted by brainzlp View Post
    The atual range adress are in login sheet
    G8:G20 but some are empty for new users
    And T8:T20 in login sheet as well.
    Are the empty cells scattered within the range or are the empty cells at the bottom of the range?

  5. #5
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516
    Quote Originally Posted by Tony Valko View Post
    Are the empty cells scattered within the range or are the empty cells at the bottom of the range?
    Sorry for late reply. The empty cells are always at the Bottom of the range.
    Which means that once we hit empty we can go to next range.

    Hope to hear from you

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    Create these dynamic named ranges.

    Name: List1
    Refers to:
    =Login!$G$8:INDEX(Login!$G$8:$G$100,MATCH("zzzzz",Login!$G$8:$G$100))

    Name: List2
    Refers to:
    =Login!$T$8:INDEX(Login!$T$8:$T$100,MATCH("zzzzz",Login!$T$8:$T$100))

    Then, enter this formula in cell L2 on the Validações sheet:

    =IFERROR(IF(ROWS(L$2:L2)>ROWS(List1),INDEX(List2,ROWS(L$2:L2)-ROWS(List1)),INDEX(List1,ROWS(L$2:L2))),"")

    Copy down until you get blanks.

    In the formulas you may have to replace the commas with semi-colons.

  7. #7
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    The last named range I CREATE:
    Please Login or Register  to view this content.
    is not working properly, since the range has formula, he doesn't assume an empty cell.

    How can i get a drop down list without blank cells on that value? (Instead of doing it manually always i insert a user of course)

    Hope to hear from you
    Last edited by brainzlp; 10-06-2015 at 12:36 PM.

  8. #8
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    Quote Originally Posted by Tony Valko View Post
    Create these dynamic named ranges.

    Name: List1
    Refers to:
    =Login!$G$8:INDEX(Login!$G$8:$G$100,MATCH("zzzzz",Login!$G$8:$G$100))

    Name: List2
    Refers to:
    =Login!$T$8:INDEX(Login!$T$8:$T$100,MATCH("zzzzz",Login!$T$8:$T$100))

    Then, enter this formula in cell L2 on the Validações sheet:

    =IFERROR(IF(ROWS(L$2:L2)>ROWS(List1),INDEX(List2,ROWS(L$2:L2)-ROWS(List1)),INDEX(List1,ROWS(L$2:L2))),"")

    Copy down until you get blanks.

    In the formulas you may have to replace the commas with semi-colons.
    All your job worked perfectly, just had to convert to portuguese Excel the formula and switch , to ;
    Although now i need to get a drop down list of that ALL users RANGE, but since there is a formula, it appears in blank several values

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    Does that mean you want to use the range L2:L? as the source for a drop down list?

    If so, use something like this...

    =OFFSET(L2,,,COUNTIF(L2:L100,"?*"))

  10. #10
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516
    Quote Originally Posted by Tony Valko View Post
    Does that mean you want to use the range L2:L? as the source for a drop down list?

    If so, use something like this...

    =OFFSET(L2,,,COUNTIF(L2:L100,"?*"))
    Thanks for your replt, Tomorrow i will try what you mean. What i want is really to have that range as source to one drop down list.

    I will feedback you tomorrow after checking if this works.

    Thanks in advance for your time and Patience

  11. #11
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    Quote Originally Posted by Tony Valko View Post

    =OFFSET(L2,,,COUNTIF(L2:L100,"?*"))
    Works perfectly, can't understand why is "?*" :/ although it works like this

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    The COUNTIF criteria "?*" means to count only those cells that contain printable ascii text characters.

    A formula blank "" is not a printable ascii character so cells that contain those are not counted.

  13. #13
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516
    Quote Originally Posted by Tony Valko View Post
    The COUNTIF criteria "?*" means to count only those cells that contain printable ascii text characters.

    A formula blank "" is not a printable ascii character so cells that contain those are not counted.
    Wow, ok got it. Thanks

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    You're welcome. Thanks for the feedback!

  15. #15
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    Quote Originally Posted by Tony Valko View Post
    You're welcome. Thanks for the feedback!
    Hello tony,

    Sorry to bother you again,

    Can you help me out with the same problem, although instead of 2 Lists, i have 5

    For 2, the formula:
    Please Login or Register  to view this content.
    works.

    How can i put this working with List1, List2, List3, List4, List5 (Already defined all ranges)

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    With 5 lists the formula gets quite long...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    List1
    List2
    List3
    List4
    List5
    Combined
    2
    1
    2
    4
    5
    8
    1
    3
    3
    6
    2
    4
    7
    3
    5
    4
    6
    5
    7
    6
    8
    7
    9
    8
    10
    11
    ------
    ------
    ------
    ------
    ------
    ------
    ------


    This formula in G2 and copied down until you get blanks:

    =IFERROR(INDEX(List1,ROWS(G$2:G2)),IFERROR(INDEX(List2,ROWS(G$2:G2)-ROWS(List1)),IFERROR(INDEX(List3,ROWS(G$2:G2)-SUM(ROWS(List1),ROWS(List2))),IFERROR(INDEX(List4,ROWS(G$2:G2)-SUM(ROWS(List1),ROWS(List2),ROWS(List3))),IFERROR(INDEX(List5,ROWS(G$2:G2)-SUM(ROWS(List1),ROWS(List2),ROWS(List3),ROWS(List4))),"")))))

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Convert 2 lists into 1

    Tony, see if you can get here now?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  18. #18
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    Quote Originally Posted by Tony Valko View Post
    With 5 lists the formula gets quite long...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    List1
    List2
    List3
    List4
    List5
    Combined
    2
    1
    2
    4
    5
    8
    1
    3
    3
    6
    2
    4
    7
    3
    5
    4
    6
    5
    7
    6
    8
    7
    9
    8
    10
    11
    ------
    ------
    ------
    ------
    ------
    ------
    ------


    This formula in G2 and copied down until you get blanks:

    =IFERROR(INDEX(List1,ROWS(G$2:G2)),IFERROR(INDEX(List2,ROWS(G$2:G2)-ROWS(List1)),IFERROR(INDEX(List3,ROWS(G$2:G2)-SUM(ROWS(List1),ROWS(List2))),IFERROR(INDEX(List4,ROWS(G$2:G2)-SUM(ROWS(List1),ROWS(List2),ROWS(List3))),IFERROR(INDEX(List5,ROWS(G$2:G2)-SUM(ROWS(List1),ROWS(List2),ROWS(List3),ROWS(List4))),"")))))
    Already tried your code and works great. Thank you.

    I just need the other thing, divide the Combined COLUMN into the others, depending on criteria.

    That's my question in another thread. http://www.excelforum.com/excel-form...ml#post4224715

  19. #19
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    Quote Originally Posted by FDibbins View Post
    Tony, see if you can get here now?
    Couldn't get here then but I'm able to get here now! (10/25/2015 9:45 AM)

  20. #20
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    Quote Originally Posted by brainzlp View Post
    Already tried your code and works great. Thank you.
    You're welcome. Thanks for the feedback!

  21. #21
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    Quote Originally Posted by brainzlp View Post
    I just need the other thing, divide the Combined COLUMN into the others, depending on criteria.

    That's my question in another thread. http://www.excelforum.com/excel-form...ml#post4224715
    Looks like you have a solution in that thread.

  22. #22
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    Quote Originally Posted by Tony Valko View Post
    Looks like you have a solution in that thread.
    Yuuppp All solved.
    Thank you

  23. #23
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    Sorry to bother you again but the formula is giving me an error:

    This formula:
    Please Login or Register  to view this content.
    It's copy paste of yours.

    The problem is that, if the one of the columns is empty, it simply stops.

    Can't it stop only in the last list?

    I tried with the List3 when it is empty, it stops gathering the list4 and 5

    Hope you can help me out

  24. #24
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    Can you make up a SMALL sample file that demonstrates the problem?

    I won't download files >10kb in size.

  25. #25
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    In the List4 delete the 5/6/7 and let that column in blank.

    Then check that in the combined column the number 8 of the List5 column doesnt appear.

    Hope you can understand me, at moment i am without computer

    Get back to me asap
    Last edited by brainzlp; 10-26-2015 at 04:20 PM.

  26. #26
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516

    Re: Convert 2 lists into 1

    Quote Originally Posted by Tony Valko View Post
    With 5 lists the formula gets quite long...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    List1
    List2
    List3
    List4
    List5
    Combined
    2
    1
    2
    4
    5
    8
    1
    3
    3
    6
    2
    4
    7
    3
    5
    4
    6
    5
    7
    6
    8
    7
    9
    8
    10
    11
    ------
    ------
    ------
    ------
    ------
    ------
    ------

    Please check this table and according to last comment.

  27. #27
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Convert 2 lists into 1

    When I tried it the cells returned 0 but I used static named ranges.

    I need to see this in your file in context.

    I'm guessing that if we can do this the formula will be extremely long.

  28. #28
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Covilhã
    MS-Off Ver
    2010
    Posts
    516
    Quote Originally Posted by Tony Valko View Post
    When I tried it the cells returned 0 but I used static named ranges.

    I need to see this in your file in context.

    I'm guessing that if we can do this the formula will be extremely long.
    Well i am using dynamic range cells for it. I will try with static ones. If it shows something not good i will just add 1 field saying test or NEW and its fine.

    Probably we nees do put a test for if cont. Val (list1) = 0 tgen go to list2. Although dont know how
    If it allowed the empty and once empty is reached go to another list would be perfect but if we cant... Have to figure another thing out

+ 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: 07-27-2014, 10:23 AM
  2. [SOLVED] Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists
    By alipezu in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-01-2014, 12:28 AM
  3. Validation Lists - Multiple dependent lists with unique values
    By Lewigi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 06:42 AM
  4. Replies: 1
    Last Post: 06-14-2013, 04:06 PM
  5. how to ignore blank cells in creatig dropdown lists (validation lists)
    By Elainefish in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2013, 07:45 AM
  6. [SOLVED] How do you convert embedded drop-down lists in Excel '97?
    By RavensChild in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2005, 06:05 PM
  7. Replies: 2
    Last Post: 08-10-2005, 07:05 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