# 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 ???

MyCurrentRecordset.xlsx
MySortedRecordset.xlsx
###### Who is Participating?

Commented:
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)))
``````

Saurabh...
MyOriginalRecordset-SaurabhSolution.xlsx
0

Commented:
Yes. Here it is.
0

Commented:
I don't think the file came through. Once more..
MyCurrentRecordset.xlsx
0

Commented:

0

Author Commented:

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

Author Commented:
0

Commented:
Oh, well you can add a hidden column and sort on that.

=IF(ISBLANK(B2),A2,B2)
0

Commented:
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

Author 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

Author 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

Author Commented:
oh forgot to mention...
I also added this formula to columnB.....=IF(ISBLANK(B2),A2,B2)
0

Commented:
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

Commented:
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

Commented:
And hide column B like I explained above.
0

Commented:
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

Commented:
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

Author Commented:
0

Author 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

Commented:
0

Author 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

Commented:
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

Author 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

Commented:
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

Author Commented:
" 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

Author Commented:
You think Macro in excel can do this kind of sorting???
0

Commented:
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

Commented:
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

Commented:
Or, someone is going to show up in this tread with a macro that will just do the whole thing for you!
0

Author 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

Commented:
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

Author Commented:
Thanks...will wait!

no problem
0

Commented:
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)))
``````

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

Author 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!!!

MyOriginalRecordset-SaurabhSolution.xlsx
0

Author 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.