Results 1 to 9 of 9

Thread: formulas for multiple values with duplicates

  1. #1

    formulas for multiple values with duplicates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am trying to get a formula that will return the date (column D) into column G using the lookup value (unique value), but as you can see, there are duplicate codes for the add code, which have different dates. I would like it so that the formula looks up the first lookup up code, then pulls through the date for the first code, then if there is another add code but a different code for it to pull that date through but have it in column G (1st) and H (2nd). In essence, I would like to see how many times the add code appears and if its more than once, to place it along in columns G and H.

    I have tried lookups, Index, Index with rows, 2 way lookups, I just cant seem to get it to work so that it looks down the rows and pulls through the correct dates for the add code and if there are duplicates to place them side by side.

    As you can see Add code 123 has code 1,2 and 6.

    =index($D$1:$D$6,small(if($B$1:$B$6=$F$1,if($A$1:$ A$6=$E$1:$E$5,row($D$1:$D$6)-row($D$1)-1))))


    Code Add Code Name Date Lookup Value 1st Time 2nd Time
    Col 1 1 123 Jordan 1st Jan 2008 123
    Col 2 4 234 James 2nd April 2008 234
    Col 3 3 456 Luke 3rd Jan 2008 456
    Col 4 2 123 Sarah 4th Sept 2008 789
    Col 5 5 789 Mike 5th Mar 2008 987
    Col 6 6 123 Vanessa 1st Jan 2009

    Any help would be greatly appreciated.

    Many thanks in advanced.
    Last edited by happy_smiler1; 2012-08-28 at 10:56 PM.

  2. #2
    Try this array formula

    =IFERROR(INDEX($D$1:$D$7,SMALL(IF($B$1:$B$7=$F2,ROW($B$1:$B$7)),COLUMN((A1)))),"")

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Try this array formula

    =IFERROR(INDEX($D$1:$D$7,SMALL(IF($B$1:$B$7=$F2,ROW($B$1:$B$7)),COLUMN((A1)))),"")

    Hi,

    Many thanks for the reply, i have tried this, but it doesnt return the value(s) that i need as it doesnt lookup the correct cells, back to the drawing board i guess.

  4. #4
    It worked in my tests. Did you array-enter it? What did it give, what should it have given?

  5. #5
    It just returned an error. I tried tweaking it to no avail. What I need is one lookup that will return multiple values. From the example you can see that add code 123 displays multiple code values 1, 2 and 6. I need to show the dates for add code 123 like this:

    add code 1st time 2nd time 3rd time
    123 1st Jan 2008 4th sept 2008 1st jan 2009

    so on and so on for every code that is in the spreadsheet, I have alot of data and the example is just a snippet and i wanted to see if there was a shortcut of doing it as doping it manually will take a good few days. I have tried a pivot table but that doesnt work?

    Any ideas?

    Thank you for your help, this is really appreciated.

  6. #6
    I bet you didn't array-enter the formula.

  7. #7
    Quote Originally Posted by Bob Phillips View Post
    I bet you didn't array-enter the formula.
    By you meaning ctrl+shift+enter, tried it, got an error #name.

    The formula you tried above, if that works, would it not just give you the first value of the duplicate each time, rather than looking down to the next value in the duplicate and pulling the next date through?

    Thanks,
    Last edited by happy_smiler1; 2012-08-30 at 07:14 AM.

  8. #8
    No, because you pull it across to the next cell (G2,H2) so as to get them both.

  9. #9
    Quote Originally Posted by Bob Phillips View Post
    No, because you pull it across to the next cell (G2,H2) so as to get them both.

    Thank you sooooooo much, you are the man!!!!!!!!!!!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •