Is this sorting possible to do in excelsheet??

Hi,
         Attached please find the excel sheet MyCurrentRecordset that shows the records that I currently have sorted by OrderNo.
But I would like to get this sorted like shown in the worksheet MySortedRecordset that shows the records sorted by OrderNo &OldOrderNo(if there is any available  for that OrderNo).

Is this possible to get this sorting done in excel ???

The reason is I have about more than 2000 rows that need to be sorted in this manner.
The entire excel is originally sorted by the OrderNo column.
the challenging part is ... if there are any OrderNo that has a OldOrderNo in column2...it has to be moved and put it together underneath their respective OrderNo in column1.

Any ideas how I can accomplish this kind of sorting in excel ???

Thanks very much in advance!
MyCurrentRecordset.xlsx
MySortedRecordset.xlsx
jen RadAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
Use this formula...

=IF(OR(B2="",ISERROR(VLOOKUP(INDEX(A$1:A1,MATCH(B2,A$1:A1,0),1),A$1:B1,2,0))),A2,IF(VLOOKUP(INDEX(A$1:A1,MATCH(B2,A$1:A1,0),1),A$1:B1,2,0)=0,B2,VLOOKUP(INDEX(A$1:A1,MATCH(B2,A$1:A1,0),1),A$1:B1,2,0)))

Open in new window


Your workbook for reference...

Saurabh...
MyOriginalRecordset-SaurabhSolution.xlsx
0
 
Missus Miss_SellaneusCommented:
Yes. Here it is.
0
 
Missus Miss_SellaneusCommented:
I don't think the file came through. Once more..
MyCurrentRecordset.xlsx
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Missus Miss_SellaneusCommented:
Use "Add Level" to add old order #.

sort.JPG
0
 
jen RadAuthor Commented:
Thank you for the reply.

But the results I want to achieve is as shown in the attached screenshot.
I would like to move the row just below the OrderNo that has OldOrderNo = OrderNo in column A.
Any ideas?
0
 
jen RadAuthor Commented:
RequiredSortingOrder
0
 
Missus Miss_SellaneusCommented:
Oh, well you can add a hidden column and sort on that.

=IF(ISBLANK(B2),A2,B2)
0
 
Missus Miss_SellaneusCommented:
I guess you want to use the hidden column for the first level and column A for the second level.

=IF(ISBLANK(B2),A2,B2)
0
 
jen RadAuthor Commented:
I am kind of not much familiar with Excel. Sorry...but if it is not too much of trouble could you please explain how to get this done in step by step

Hidden column---how do I create a hidden column?
So, I created new column beside column A.
After that I go to custom sort & add the level 1 as ColumnB and then level2 as OrderNo.???
0
 
jen RadAuthor Commented:
I created new column beside column A.
 After that I did custom sort & added the level 1 as ColumnB and then level2 as OrderNo.?...Is this correct?
0
 
jen RadAuthor Commented:
oh forgot to mention...
I also added this formula to columnB.....=IF(ISBLANK(B2),A2,B2)
0
 
Missus Miss_SellaneusCommented:
The way I wrote the formula, it will have to be changed if you stuck it beside column A.

If you use column F, do it like this.

Just stick that formula I gave you into cell F2. Then hover your mouse over the "F" at the top of the column and right-click.  Choose "Hide." The column won't show, or print, but is still there to use for your sorting.
0
 
Missus Miss_SellaneusCommented:
No, you can't put that formula you wrote into column B, because the former column B is now column C.

So you could do this in your NEW column B:

=IF(ISBLANK(C2),A2,C2)

And your sort levels will be
1. column B
2. column A
0
 
Missus Miss_SellaneusCommented:
And hide column B like I explained above.
0
 
Missus Miss_SellaneusCommented:
And if you ever want to unhide a hidden column to change it or view it, just select the columns in a range from before and after the hidden column, right-click, and choose unhide.
0
 
Missus Miss_SellaneusCommented:
Or maybe you put your new column BEFORE column A? I'm not clear on that. If you still have issues, you can re-upload the file and I'll fix it for you.
0
 
jen RadAuthor Commented:
AfterSortingOrder.jpg
0
 
jen RadAuthor Commented:
I got the sorting as shown in the above screenshot...but ...then I didn't get the required result I want to see...Any ideas??
0
 
Missus Miss_SellaneusCommented:
Can you upload it again?
0
 
jen RadAuthor Commented:
I have attached the following files that shows:

My original recordset  and the Recordset I got after sorting following the above instructions.
And lastly is the file MyDesiredRecordset...which is the one I really want to achieve at the end.


Thanks very much!
MyOriginalRecordset.xlsx
MyRecordset-aftersorting.xlsx
MyDesiredRecordset.xlsx
0
 
Missus Miss_SellaneusCommented:
Looking at your desired set, I'm trying again to get a handle what you want. Why is row 6 where it is ? Do you want that row to come next because the new 1006 in row 5 had an old order # of 1001, and therefore row 6 is next because it's old order # is 1006?

I need a little time to think on this.... it's getting more complicated.
0
 
jen RadAuthor Commented:
Yes! you are correct...I actually want that row to come next because the new 1006 in row 5 had an old order # of 1001, and therefore row 6 is next because it's old order # is 1006?

Thanks very much for helping!
0
 
Missus Miss_SellaneusCommented:
It's looking to me like you won't be able to do this with a simple sort, where you can just re-sort when you add a new row. It looks like it will either need a macro, or you'll need to use VLOOKUP to grab the oldest order number per current number from another worksheet.

The problem is, it could end up as a loop that keeps going multiple times, back towards the oldest order number.

Hmmmm.

I won't be writing a macro, since I don't have enough experience on that. Let me think on it. Maybe someone else will chime in on this in the meantime.
0
 
jen RadAuthor Commented:
Just Revisiting your above comment:
" you'll need to use VLOOKUP to grab the oldest order number per current number from another worksheet.


Ok...If I have two separate worksheets...one for all the OrderNo with no OlderOrderNo
and the another separate one with all the OrderNo with OlderOrderNo.

Can this help to achieve MydesiredRecordset sorting???

I can actually have two separate sheets. Can it work?

Thanks a lot!
0
 
jen RadAuthor Commented:
You think Macro in excel can do this kind of sorting???
0
 
Missus Miss_SellaneusCommented:
Ya, you'll need a lookup table with the oldest order # per higher order #. I'm trying to figure out how to automate that.
0
 
Missus Miss_SellaneusCommented:
A macro should be able to use looping to determine the oldest order # in the list per order #, but I don't know how to do that. The macro could fill a calculated field with oldest order # and you could use that to sort.
0
 
Missus Miss_SellaneusCommented:
Or, someone is going to show up in this tread with a macro that will just do the whole thing for you!
0
 
jen RadAuthor Commented:
But ...will a macro be able to move the entire row to put together all the orderNo with their respective olderOrderNo together to achieve mydesiredrecordset sorting???

If yes...maybe should I reopen this question to grab some attention from Excel Macro experts???
0
 
Missus Miss_SellaneusCommented:
I don't know, and I just spent half an hour without internet because my power was out and didn't bother working/thinking on this in the meantime with only my little laptop screen, knowing I'd be without daylight in less than an hour if it didn't come back on soon.

You can recreate a new question easily enough, sure. If this is urgent, especially, otherwise I know someone else will most likely show up in this question probably by tonight or tomorrow if you give it some time. I'm still not sure how to best go about it without a macro and I really need to focus on making some money for the time being. Sorry about that.
0
 
jen RadAuthor Commented:
Thanks...will wait!

no problem
0
 
Saurabh Singh TeotiaCommented:
I'm assuming this is what you are looking for...

I added this Formula in Column-F

=IF(B2="",A2,IF(VLOOKUP(INDEX(A$1:A1,MATCH(B2,A$1:A1,0),1),A$1:B1,2,0)=0,B2,VLOOKUP(INDEX(A$1:A1,MATCH(B2,A$1:A1,0),1),A$1:B1,2,0)))

Open in new window


As shown in the before sheet..and then sorted by this column as shown in After sheet which does what you are looking for...

Once the data is sorted you can delete this column...

Saurabh...
MyOriginalRecordset.xlsx
0
 
jen RadAuthor Commented:
Hi Saurabh,
                         Thank you very much for the solution. It is working very perfect. Except that I have a small issue with my dataset. Sometimes...the oldOrderNo is not present in the excel sheet because of this data issue am getting an error...i.e...I believe the lookup doesn't find the oldOrderNo in excel......then it is returning an error.

Is there a way...to fix this like...if the oldOrderNo is not present in the excel in column A...then it returns the column A OrderNo instead of throwing this error.

I have attached the excelsheet with the data issue I currently have for your reference.

Thank a lot for your help!!!


OldOrderNo-NotPresentinExcel.jpgMyOriginalRecordset-SaurabhSolution.xlsx
0
 
jen RadAuthor Commented:
Will try this & let you know

Thank you  very much...Saurabh!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.