+ Reply to Thread
Results 1 to 3 of 3

Clustering Matrix values- how to sort and index?

  1. #1
    Registered User
    Join Date
    11-08-2016
    Location
    Munich, Germany
    MS-Off Ver
    2010
    Posts
    28

    Clustering Matrix values- how to sort and index?

    Dear all,

    I am trying to code a Macro which utilizes the Direct Clustering Algorithm.
    As input, I have a binary matrix.
    For example:

    Please Login or Register  to view this content.
    [note that the first row and column are headers]

    Now, the rows of the matrix are interpreted as a binary value, which then should be sorted descendingly:

    First: Sort rows

    Please Login or Register  to view this content.
    The row wise sorted matrix then should be sorted columnwise, also descendingly.

    Please Login or Register  to view this content.
    What I have so far is the preperation to have arrays with the binary values stored:

    Please Login or Register  to view this content.
    What now puzzles me is: how to I sort an array and efficiently save its index, so I can swap the rows and columns? And also, if the problem gets bigger, lets say a matrix with 100+ columns and rows, does my way of concatenating the cells still work? I am already getting overflow errors when I use integers for the arrays arraybinC and arraybinR, I hope double arrays have enough storage...

    Maybe someone can help me, that would be aweseome Please find a sample attached. (in the first sheet there is a sample problem, in the second there is how I imagine the code should work like)

    BTW: If someone knows the last step, how to determine cluster and colour the matrix accordingly, please feel free to tell me As of now, I am trying to get the matrix first

    Thank you so much and have a nice weekend!
    Attached Files Attached Files
    Last edited by simz92; 04-10-2017 at 02:34 AM.

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Clustering Matrix values- how to sort and index?

    Please Login or Register  to view this content.
    only for the sort, for the colors i dont see the criteria

    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Clustering Matrix values- how to sort and index?

    This will sort the array myArray.
    Note that myArray is declared as a module wide variable.
    (Note also that the first bit of code that gets the test data should be altered to get the data from your source. The sorting stuff begins where indicated)
    (Note also that if you are using more than 10 columns, you need to adjust (or remove) the final output line)

    The code sets up an array arrRows and then sorts that on which row is greater.
    It then re-orders myArray according to that sort.
    Then it does the same thing with arrCols and columns.

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

+ 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: 2
    Last Post: 11-05-2014, 03:41 AM
  2. Formula to Index, Match, Rank, and Sort a Dynamic Range of Values
    By AustinLe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2014, 03:49 AM
  3. Replies: 11
    Last Post: 03-26-2014, 12:48 AM
  4. [SOLVED] INDEX/LOOKUP function for Matrix
    By 2011 in forum Excel General
    Replies: 4
    Last Post: 05-31-2012, 09:55 AM
  5. Help Match Index Formula for a table matrix
    By prkhan56 in forum Excel General
    Replies: 2
    Last Post: 09-29-2011, 04:40 AM
  6. Sort from one matrix, then lookup and assign corresponding value
    By spnpls in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2010, 10:33 PM
  7. colour index matrix
    By DHallam in forum Excel General
    Replies: 3
    Last Post: 06-01-2006, 01:55 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