+ Reply to Thread
Results 1 to 14 of 14

Excel List Sorting

  1. #1
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2003,2007,2010,2013 and 2016
    Posts
    51

    Excel List Sorting

    Can anyone help me with this? I have a very serious problem on how can I sort specific data like Time C/In and C/Out to their respective AC-No. and NAME. What I need is that the AC-No. and its NAME must be in one row (not duplicate) and all other entries. I just need formula to come up with what I desire to happen. Thank you so much.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    Please amend your sheet to show us a few samples of your desired results.
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    This formula:

    =IFERROR(INDEX(A$2:A$722,MATCH(0,INDEX(COUNTIF(G$1:G1,A$2:A$722),0),0)),"")

    gives you unique C and names. So, what else do you need??

    You have Excel 2016. Why are you using the old Excel 2003 and earlier .xls file ?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2003,2007,2010,2013 and 2016
    Posts
    51

    Re: Excel List Sorting

    Hello Glenn,

    Thank you for your help. Partly, that's what I really need. I have attached a file with sample output that I need it to automatically sort the specific data to its respective cells and aligned to its specific AC-No and NAME.

    Regarding to your question on why I am using the old Excel 2003, it is because I need this to be accessible to all versions of MS Excel. I am new to this. What I have in mind is that higher versions would possibly not compatible with the lower versions. So I am using the 2003 to make it run on 2016. I really don't know. Maybe I'm wrong.
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    To do this with your current date format could get messy, so I separated them out into two columns. This enabled the avoidance of array formulae, which could get slow.


    I have left this in .xlsx format, so it will not work with Excel 2003 and earlier. Is this really an issue? If it is, it can be amended, but will be slower as every formula will have to be replaced by a longer (twice as long) version. This will have performance implications.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2003,2007,2010,2013 and 2016
    Posts
    51

    Re: Excel List Sorting

    Glenn,

    Thank you so much. What you have shared was awesome. I learned a lot. I studied the formula you have provided and I found out most of them which are very much useful for this file. I have added some data in columns E and H which are extracted from column C. I used some of the formulas in columns Y until AF you have provided and tried to modified them. I almost got the desired output but I really found it very hard that some data don't fall at the exact places, like that of cells AE4 and AF4, AE5 and AF5, AF7, AE8 and AF8, AE9 and AF9 and so on. They must be in order but I can hardly figure out how to do it?

    I am really sorry for making this very difficult. I just really need this.

    Thank you once more and more in advance.
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    OK. Gotcha. Select K1 to N25, as a block. Now drag across as far as needed (BJ25 for your sample data).

    BtW, it's much more sensible (now) to have the time values in separate cells.

    I changed the yellow highlighted column, and killed my former time column.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2003,2007,2010,2013 and 2016
    Posts
    51

    Re: Excel List Sorting

    Yehey! you got it Glenn. But, I think 95% of the desired output is reached. As I go over with the data, I still can't figure out how these data in RED text appeared in the cells where they're not supposed to be placed. And only one from the duplicate data should reflect (in cells P8 and Q8). I have highlighted cells C214:C218, and what should appear in cell Q8 is suppose to be the time reflected in cell C218. This could be possible to happen in some other cells.

    Maybe this can be fixed.

    Please be patient.
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    Fixing a puncture. Look back later...

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    I see the issue. It's s complete re-write job. I'm a bit weary now and will come back to this later, or in a.m. Performance on large datasets may become an issue.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    I gave up a beer o'clock meeting to get one more version tonight. these are now array formulae.

    We might be able to do something about that later, but first...

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    There are still some issues... but I think that they're "your problem" M6-P6 and the raw data C144:D147...

    take another look...

    It IS ABSOLUTELY beer o'clock now.

    G'nite.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2003,2007,2010,2013 and 2016
    Posts
    51

    Re: Excel List Sorting

    Glenn,

    I have added 3 columns E, F, and G where G generates unique letters every change of date which serve as the ID for reference. This enables all extracted time logs to fall exactly to their respective places. I hope that what I did is correct.

    Thank you so much Glenn. Hoping more from you and your kind help and collaboration to this task, next time.

    MORE POWER!
    Attached Files Attached Files

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    New learning curve! Office 365
    Posts
    25,413

    Re: Excel List Sorting

    Looks OK, but you keep changing your mind about whether you want all values in one cell (where they are dead text) or in separate cells. I must say, I find this output very heard to read!!

    Anyway, .... you're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  14. #14
    Registered User
    Join Date
    03-01-2018
    Location
    Philippines
    MS-Off Ver
    2003,2007,2010,2013 and 2016
    Posts
    51

    Re: Excel List Sorting

    My apology. Actually, it was the first option to merge all values in one cell. Its all okay if the output really is hard to read. But this is what the desired one. I added a text-wrapping to each cell which made all 5 characters fit and have them printed.


    YOU ARE GREAT!...


+ 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. Excel conflict list sorting
    By amadtaz in forum Excel General
    Replies: 1
    Last Post: 07-22-2011, 04:43 PM
  2. Sorting a multi column list in Excel
    By jonsidneyb in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 08:55 AM
  3. Need Help Sorting Excel List to Notepad
    By Seanis00 in forum Excel General
    Replies: 15
    Last Post: 05-17-2010, 08:46 AM
  4. Searching and Sorting a list of data (numbers and letters) in excel
    By bramble in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-25-2008, 06:54 PM
  5. Excel Pivot field list sorting
    By maxik in forum Excel General
    Replies: 5
    Last Post: 05-24-2007, 05:38 PM
  6. Excel List Sorting
    By ben.lopresti@gmail.com in forum Excel General
    Replies: 7
    Last Post: 08-06-2006, 11:55 AM
  7. Excel List Sorting Problem (Descending)
    By Erik in forum Excel General
    Replies: 2
    Last Post: 10-11-2005, 07:05 AM
  8. Does font color affect sorting a list in Excel?
    By Valser in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2005, 03: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