+ Reply to Thread
Results 1 to 14 of 14

Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicates

  1. #1
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    87

    Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicates

    Edited to attach the correct example workbook - sorry folks.

    I use Excel 2016.

    I have two work sheets in a workbook. Sheet 2, Column B contains duplicates as well as unique values.
    I need a formula in Sheet 1, C1 to look at a every cell in column B:B on Sheet2 and return only ONE of any duplicates and any unique values but in the order that they are found down the column.

    A lot of the other Columns on both sheets will have data but to keep the example as simple as possible I have not used any dummy text in those.

    So far I have tried using INDEX, COUNT and MATCH but without success as I need only one instance of any duplicate returned as well as any unique (non-duplicate) values. If it is at all possible I would like to do this without using array as I just do not understand how they work but if it is the only way then so be it.
    Attachment 684591
    Attached Files Attached Files
    Last edited by Belinea2010; 06-29-2020 at 01:43 PM. Reason: To attach a sample workbook

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Quote Originally Posted by Belinea2010 View Post
    Sheet 2, Column B contains duplicates as well as unique values.
    .
    Hi Belinea2010,
    Are you sure you attached the right file?
    there's no connection between what you describe and what the file contains....please check again.

    Thanks.

  3. #3
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Hello again belinda200

    Thank you for catching that error and sorry about that.

    Both of the workbooks were side by side in a folder and I clicked on the wrong one to upload.


  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Hi to you too Belinea2010
    No worries, it happens to best of us

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    And here s my solution:
    In A2 and down:

    =INDEX(Sheet2!$B$1:$B$120,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$120)/(MATCH(Sheet2!$B$2:$B$120,Sheet2!$B$2:$B$120,0)=ROW(Sheet2!$B$1:$B$120)),ROWS(Sheet1!$K$1:K1)))
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    If you are still using 2007, how about
    =IFERROR(INDEX(Sheet2!$B$2:$B$12,MATCH(0,COUNTIF(A$1:A1,Sheet2!$B$2:$B$12),0)),"")

    Needs to be confirmed with Ctrl Shift Enter, rather than just Enter

  7. #7
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Quote Originally Posted by belinda200 View Post
    And here s my solution:
    In A2 and down:

    =INDEX(Sheet2!$B$1:$B$120,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$120)/(MATCH(Sheet2!$B$2:$B$120,Sheet2!$B$2:$B$120,0)=ROW(Sheet2!$B$1:$B$120)),ROWS(Sheet1!$K$1:K1)))
    Hi belinda200

    Thank you for such a quick reply and your formula works perfectly.

    Rather than just asking for help I am trying to learn from the help that people kindly give and I have been trying to dissect your formula to see how it works so may I ask what the purpose is for
    Please Login or Register  to view this content.
    ? I only ask as I am curious because in my example there is no data in that column but in the final worksheet there will be.

    The final workbook Sheet 2 will have anywhere between 4000 – 7000 rows of data which will be updated by a Macro (importing raw data from a delimited text file) and I have been able to change your cell references from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    which appear to work fine.

    During the text file import/update the VBA macro also inserts all of the formulas and to stop the whole workbook growing to a silly size (MB wise) I always wrap formulas in IF statements like
    Please Login or Register  to view this content.
    to only insert the formula where associated data exists. I have also wrapped an IFERROR statement around your formula as it was returning #NUM! on cells with no data in Sheet 2 which worked fine until I wrapped the if statement around the whole code and that caused the #NUM! error to return.

    Is there a better way (or should I say the correct way) for me to use the IF data exists and IFERROR with your formula?

    Your formula with the IFERROR statement
    Please Login or Register  to view this content.
    And with the IF (data exists) statement
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Quote Originally Posted by Fluff13 View Post
    If you are still using 2007, how about
    =IFERROR(INDEX(Sheet2!$B$2:$B$12,MATCH(0,COUNTIF(A$1:A1,Sheet2!$B$2:$B$12),0)),"")

    Needs to be confirmed with Ctrl Shift Enter, rather than just Enter
    Hi again Fluff13

    Thank you for your formula which seems to work perfectly - I am about to test extending it downwards to see if it will handle 8000 rows and as array formulas are like a dark art to me (in a good way) I wonder if they have any advantage over non array formulas?

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    As Belinda's formula worked for you , you are no longer using 2007, can you please update your profile to show what version you are using now.

  10. #10
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Quote Originally Posted by Fluff13 View Post
    As Belinda's formula worked for you , you are no longer using 2007, can you please update your profile to show what version you are using now.
    Hi Fluff13

    I am using both 2007 and 2016.

    When I started this project (working from home during the covid lockdown) I was using my own personal laptop but as the finished workbook was to be used on a work system that uses 2016 my boss allowed me to bring a work laptop home which has 2016 on it. I did mention in my original post that I am using 2016 for this question but I will change my profile as you request.

    My I ask if an array formula has advantages over non array ones?

  11. #11
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Hi,
    Regading your first question:
    My formula didnt reach 17 , it got only to 6 but since you continued the data in your original file, I assume you copy it from there.
    so (Sheet1!$K$1:K17) or more precisely ROWS(Sheet1!$K$1:K17) will give you the number 17 , and as you drag it down where K1 is locked it will continue producing consecutive numbers (18,19,20,etc.)
    This is needed for the AGGREGATE function which is constructed this way:
    AGGREGATE( function, options, array, [K] )
    The function I used is 15 which represents the SMALL function, and for the option category I used 6 to Ignore error values
    The array is the data instructing the formula to bring only unique values and the "K" which is what you asked about is the location of the SMALL number inside the array. so the first cell will bring the 1st smallest value [ROWS(Sheet1!$K$1:K1)], and as you drag the fotmula along the lines it will become 2,3,4,etc and ROWS(Sheet1!$K$1:K17) will produce the 17th smallest value....

    Hope this makes sense.
    The net is full of explanations about the AGGREGATE function, you can read more in here for example:

    https://exceljet.net/excel-functions...egate-function

    Regarding the formula parts you have added it seems fine except this part in bold that somehow has changed to K:P
    It should be fixed on column K and as explained above K1 should be fixed and the second part should be adding 1 as you drag the formula down:
    =IF(Sheet2!B2<>"",IFERROR(INDEX(Sheet2!$B$1:$B$12000,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$12000)/(MATCH(Sheet2!$B$2:$B$12000,Sheet2!$B$2:$B$12000,0)=ROW(Sheet2!$B$1:$B$12000)),ROWS(Sheet1!$K$1:K1))),"No Data Found"),"")

    Good Luck.

  12. #12
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    Hi belinda200

    Thank you for taking the time to write such a comprehensive explanation which is very much appreciated.

    Yes it makes some sense to me which in its self is amazing as only two months ago it would have made none and what I have learned is from reading online tutorials and from the help and advice of good people such as yourself within the excel forums community.

    Thank you to each every person who gives up their free time to help people out.

    Thanks to you again.

    Regards

    Bel
    Last edited by AliGW; 06-30-2020 at 01:41 PM. Reason: Please don’t quote unnecessarily!

  13. #13
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    You're welcome Blinea, glad you understood the explanation despite my lame english
    and yes indeed you can definitely learn a lot from reading online tutorials.

  14. #14
    Registered User
    Join Date
    01-31-2015
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Formula on Sheet 1 to list only 1 of values in a column on sheet 2 with some duplicate

    There is nothing lame about your english belinda, you speak it perfectly.

+ 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. Values of Column to Other Sheet, With out duplicates
    By UNNI NAIR in forum Excel General
    Replies: 1
    Last Post: 10-11-2019, 05:50 AM
  2. Replies: 9
    Last Post: 03-28-2017, 08:30 PM
  3. Replies: 7
    Last Post: 04-08-2014, 10:06 AM
  4. Import data from specfic column in Sheet Sheet to List Box userform
    By z-eighty2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2013, 05:54 PM
  5. [SOLVED] Pull unique values from column without duplicates and export to another sheet
    By vandetta in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2013, 10:35 PM
  6. Replies: 4
    Last Post: 10-05-2012, 02:58 PM
  7. how to find and copy values on sheet 2, based on a list on sheet 1
    By evanmacnz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-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