+ Reply to Thread
Results 1 to 8 of 8

Matching duplicate data to different categories in sorted array

  1. 06-14-2011, 06:56 AM #1
    macca345
    macca345 is offline
    Registered User
    Join Date
    06-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Matching duplicate data to different categories in sorted array

    Hi guys, need a bit of help with this one:

    I have a list of categories in column A, with a value in column B. In column D, Column B is sorted with an array by descending order, and I need for the categories in column A to be sorted in column C in exactly the same way.

    However when I use a match formula as follows: INDEX(A$1:A$78,MATCH(D1,B$1:B$78,0),1), if there is a duplicate value in column D, it will only use the one category for all of the duplicate values.

    Is there a way to match the values to the categories so that this doesn't happen?

    Cheers
    Macca
    Last edited by macca345; 06-15-2011 at 04:41 AM.
    Reply With QuoteRegister To Reply
  2. 06-14-2011, 08:04 AM #2
    Blake 7
    Blake 7 is offline
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: Matching duplicate data to different categories in sorted array

    Quote Originally Posted by macca345 View Post
    I have a list of categories in column A, with a value in column B. In column D, Column B is sorted with an array by descending order, and I need for the categories in column A to be sorted in column C in exactly the same way.
    My head is spinniing! read this back to yourself!!!

    Why dont you upload a sample of your spreadsheet which demonstrates what you want to achieve. You could even do a before and after.
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/
    Reply With QuoteRegister To Reply
  3. 06-14-2011, 08:10 AM #3
    Sadath31
    Sadath31 is offline
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: Matching duplicate data to different categories in sorted array

    better to sort the categories in column C using the UDF given below, then find the value in col D using Vlookup 

    Please Login or Register  to view this content.
    this is a array UDF, hope you know how to use
    Reply With QuoteRegister To Reply
  4. 06-14-2011, 09:01 AM #4
    macca345
    macca345 is offline
    Registered User
    Join Date
    06-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Matching duplicate data to different categories in sorted array

    Sorry Blake, was a bit wordy wasn't it. I've attached a sample of what I'm trying to do, hope it clears things up.

    Sadath, that's a bit advanced for me at the moment, but I'll read up on on array UDF's tomorrow and see if I can get it to work for me, thanks for that.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-14-2011, 09:49 AM #5
    Blake 7
    Blake 7 is offline
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: Matching duplicate data to different categories in sorted array

    if there are duplicate values in Category but with different values associated in the value col, how do u want these to be treated?
    Reply With QuoteRegister To Reply
  6. 06-14-2011, 10:03 AM #6
    Sadath31
    Sadath31 is offline
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: Matching duplicate data to different categories in sorted array

    I assumed that there will not be any duplicates in category.
    ignore the VBA,

    I managed to arrive some formula, It should work

    =INDEX($A$1:$A$78,MATCH(D1+COUNTIF($D$1:D1,D1)/10,$B$1:$B$78+COUNTIF(INDIRECT("$b$1:b"&ROW($A$1:$A$78)),D1)/10,0))

    see the attached workbook too
    Reply With QuoteRegister To Reply
  7. 06-14-2011, 10:06 AM #7
    Sadath31
    Sadath31 is offline
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: Matching duplicate data to different categories in sorted array

    Sorry, i missed to attached workbook,

    please note that above is CSE formula
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 06-15-2011, 04:40 AM #8
    macca345
    macca345 is offline
    Registered User
    Join Date
    06-03-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Matching duplicate data to different categories in sorted array

    Yes that's right, the categories will not have duplicates in this case.

    That works great Sadath. Thanks to you both for the help.
    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)

Tags for this Thread

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