+ Reply to Thread
Results 1 to 11 of 11

Formula for extracting unique list from different sheets

  1. 11-08-2016, 07:22 PM #1
    XLalbania
    XLalbania is offline
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Albania
    MS-Off Ver
    2013
    Posts
    215

    Formula for extracting unique list from different sheets

    Hello Excel gurus
    I'm back because I'm learning excel
    today i need to learn from you ( if possible) how to create a unique list from differents sheets


    normaly i'm able to create a unique list and I do it with INDEX and FREQUENCY function but this time i Stuck because the arrays are in two different sheets

    ok this is the situation

    Thank you!

    A
    1
    		 Sheet 1 dates
    2
    08/01/2016
    3
    08/01/2016
    4
    24/01/2016
    5
    07/02/2016
    6
    07/02/2016
    7
    23/02/2016


    A
    1
    		 Sheet 2 dates
    2
    08/01/2016
    3
    09/01/2016
    4
    10/01/2016
    5
    14/01/1900
    6
    07/02/2016
    7
    23/02/2016



    A
    1
    		 Expected result
    2
    08/01/2016
    3
    09/01/2016
    4
    10/01/2014
    5
    14/01/2016
    6
    24/01/2016
    7
    07/02/2016
    8
    23/02/2016
    Reply With QuoteRegister To Reply
  2. 11-08-2016, 10:22 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Formula for extracting unique list from different sheets

    If you are open to helper columns it's easier.

    In the attached find this array entered formula in C1 and fill down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Then this array entered formula in D1 and fill down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Then the final non-array formula in A1 and filled down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  3. 11-09-2016, 04:22 AM #3
    XLalbania
    XLalbania is offline
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Albania
    MS-Off Ver
    2013
    Posts
    215

    Re: Formula for extracting unique list from different sheets

    Hello FlameRetired
    Excellent Idea, I was Expecting a single formula but your formula works very well so I keep your formula
    but i don't put solved Just in case Somebody else has a single forumula
    Thank you for now
    Reply With QuoteRegister To Reply
  4. 11-09-2016, 04:38 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Formula for extracting unique list from different sheets

    No helper used in this one. In sheet 3, A2, an array formula, copied down.

    =IFERROR(IFERROR(INDEX(Sheet1!$A$1:$A$6, MATCH(0, COUNTIF($A$1:A1,Sheet1!$A$1:$A$6), 0)), INDEX(Sheet2!$A$1:$A$6, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$1:$A$6), 0))),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  5. 11-09-2016, 10:41 AM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Formula for extracting unique list from different sheets

    Thank you for the feedback. It looks like Glenn Kennedy has it!!
    Reply With QuoteRegister To Reply
  6. 11-09-2016, 11:31 AM #6
    XLalbania
    XLalbania is offline
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Albania
    MS-Off Ver
    2013
    Posts
    215

    Re: Formula for extracting unique list from different sheets

    Thank you gleen for your answer
    I appreciate your help but doesnt work because the dates are not sorted from the later day to the earlier days, it is my mistake that i didnt explain you before, sorry
    Do you think is there any way solve in one single formula?
    Thank you very kind
    Reply With QuoteRegister To Reply
  7. 11-09-2016, 01:17 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Formula for extracting unique list from different sheets

    I am not aware of any way that does not involve 1 helper column. The helper column would be my existing results column, which you would then use a simple formula to return in your desired order. If that is not OK - then I think you will need VBA. I'm a bit of a noob (hopeless, actually) with VBA: so I'll not be able to help further.

    But if you're OK with one helper column, let me know.
    Reply With QuoteRegister To Reply
  8. 11-09-2016, 06:29 PM #8
    XLalbania
    XLalbania is offline
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Albania
    MS-Off Ver
    2013
    Posts
    215

    Re: Formula for extracting unique list from different sheets

    Quote Originally Posted by Glenn Kennedy View Post

    But if you're OK with one helper column, let me know.
    Hello Glenn ok let's try with an helper column
    Thank you fro your help
    Bye
    Reply With QuoteRegister To Reply
  9. 11-10-2016, 05:31 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Formula for extracting unique list from different sheets

    Like this?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 11-10-2016, 05:33 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,054

    Re: Formula for extracting unique list from different sheets

    You can (of course) hide column A... or even put it on another sheet and hide the entire sheet....
    Reply With QuoteRegister To Reply
  11. 11-12-2016, 02:46 PM #11
    XLalbania
    XLalbania is offline
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Albania
    MS-Off Ver
    2013
    Posts
    215

    Re: Formula for extracting unique list from different sheets

    Yess!! Glenn
    Good Idea too
    Thank you for the answer e sorry if i answeared late
    Excellent formula Thank you vevryvery uch
    Bye
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extracting unique values from a list in multiple columns into a single column (by formula)
    By Mukun in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-16-2016, 08:09 PM
  2. [SOLVED] Extracting unique values from a list
    By vienvy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-08-2015, 12:49 AM
  3. [SOLVED] Extracting Unique Values From a List...using formula
    By szejtan99 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-24-2014, 07:42 PM
  4. Extracting unique months form a long list of dates using array formula
    By Mian USman in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-30-2013, 09:54 AM
  5. Extracting only Unique Data From a list
    By Hammey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2010, 04:54 PM
  6. Extracting unique values from live list
    By J.W. Aldridge in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-19-2006, 10:10 AM
  7. [SOLVED] Extracting Unique List from a sheet
    By MS Excel in forum Excel General
    Replies: 2
    Last Post: 09-14-2005, 07: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1