+ Reply to Thread
Results 1 to 6 of 6

Arrange rows according to First column

  1. #1
    Registered User
    Join Date
    03-15-2015
    Location
    Espaņa
    MS-Off Ver
    2013
    Posts
    80

    Question Arrange rows according to First column

    Hello there,

    I have the following raw data:

    ISBN Elsevier ISBN FILBO
    11111 22222
    55555 88888
    44444 55555
    77777 44444
    88888 77777
    22222 11111

    The first column has a special order that I need to maintain. I was wondering if there is any way for excel to identify matches between the First and Second column (the ISBN columns) in order to re-arrange the contents from Column B to F so that they all match Column A order. The result looking like this:

    ISBN Elsevier ISBN FILBO
    11111 11111
    55555 55555
    44444 44444
    77777 77777
    88888 88888
    22222 22222

    Now, Column B has many more ISBNs than Column A, those remaining that can't be matched shouldn't be removed, but simply left after the matches, like this:

    ISBN Elsevier ISBN FILBO
    11111 11111
    55555 55555
    44444 44444
    77777 77777
    88888 88888
    22222 22222
    22242
    55521
    44441

    Any suggestion?
    the actual sheet has been attached.

    Thank you very much for your help.

    (note: in Column A, row 141 there is a string, as well as a series of xxxxx in the following row. That should remain intact. It is because I mixed two columns into one. But should remain the same because I need it in that order)
    Last edited by elbrujo; 04-27-2017 at 11:33 PM. Reason: marked as solved

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Arrange rows according to First column

    I am not real sure that I understand what it is that you want done with the ISBN FILBO's that do not match a ISBN Elsevier. I understand that you want to move those that do match and can do that using a couple of helper columns, one with the formula: =MATCH(A2,B$2:B$532,0) and the other with a series of numbers 1:531. The formula that places the matching ISBN FILBO's, as well as the corresponding information, in the correct row is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    What I am not clear on is what should happen to unmatched ISBN FILBO's. Looking at the third table in post #1 it appears that they should be moved to the bottom of the table. However your comment about rows 141 and 142 indicate that some should remain in their present location. I have attached a copy of the file with the matched ISBN FILBO's etc (columns I:L) in place, and will let you to us what you would like to have done with those that remain unmatched.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    03-15-2015
    Location
    Espaņa
    MS-Off Ver
    2013
    Posts
    80

    Re: Arrange rows according to First column

    Hello JeteMc

    Thank you very much for your time.

    Let me reply to your concern: Indeed, those unmatched should be moved to the bottom. I apologize if this wasn't clear due to my second comment. What I meant by stating that in Column A there are those two cells, was simply to clarify that those two cells aren't misplaced and should be left as they are.

    You achieved pretty much what I needed now that only those remaining unmatched rows should be moved at the bottom.

    Thank you for your time. I apologize for the misunderstanding, English isn't my first language!
    Regards

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Arrange rows according to First column

    This is a pretty inelegant proposed solution, especially at the end.
    The unmatched Filbo's are identified in column O using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    That list is then filtered in column P, to remove blanks, using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Columns Q:T are then populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I then simply copied columns A, and I:M from sheet 1 and pasted (values only) at the top of sheet 2 in columns A:F. I then copied columns P:T from sheet 1 and pasted into columns A:F, starting with row 236, of sheet 2. I finished like that because this has the feel of a one time operation. If I am wrong about that let us know and we'll look at a way to make a final table using either formulas or code (although that would have to be someone else as I don't VBA).
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-15-2015
    Location
    Espaņa
    MS-Off Ver
    2013
    Posts
    80

    Re: Arrange rows according to First column

    Hello JeteMc.

    I'm really impressed. I highly appreciate your help. That is exactly what I needed and you accomplished it...

    Thanks again!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Arrange rows according to First column

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Code for Eliminating duplicate rows and re arrange without deleting rows
    By pvsvprasad in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-21-2016, 02:42 PM
  2. Re-arrange multiple rows into a single row
    By rsanguin in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-29-2015, 10:51 PM
  3. How to arrange vertical Vcards column into horizontal rows ?
    By m.rizeg in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 07-26-2013, 10:00 PM
  4. macro to arrange rows in numerical order using numbers in column
    By blairw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-19-2013, 10:58 PM
  5. [SOLVED] Arrange all the data rows
    By johncena in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-03-2013, 12:23 AM
  6. [SOLVED] re-arrange column and rows?
    By qokalp in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-07-2012, 05:44 AM
  7. Re-arrange rows by column
    By excel_newb2 in forum Excel General
    Replies: 1
    Last Post: 11-06-2006, 11:10 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