Sorting data (dates) in one sheet and finding the sorted data in another sheet?

Hi all,

I have a situation where I have developed a spreadsheet containing some data I'm interested in sorting based on certain criteria. This data is related to another separate sheet and these sheets can't be combined so that's not a solution. Both columns have date as a variable so I'm basically interested in finding dates found in sheet one in sheet two.

Often, when sorting, it may be that the dates are wide apart, so picking out the dates manually in sheet number two is cumbersome and time consuming.

Is there a solution for this? I'm posting in the VBA forum, but maybe it could be solved without.

Sorting.png

Thanks in advance for any help.


Elijah

Hi and thanks!

Do I just copy and paste that into the VBA editor in "SheetOne"?

I did that and I'm getting an error which shows like this in the debugger:

Error.png

Actually, I think the error said something else at first, but it was that same yellow line that show up in debug.

Thanks in advance for any help!

Also, might this be done without VBA or is VBA the only way to go for this?

Hey, xladept! No worries about the rep. I'd give you more if I could, but seems like I'm restricted to do that for now.

The reason I'm asking if it could be done without VBA is because that might be simpler for my situation. Maybe the sorted range of Date could be copy and pasted into the other worksheet and the dates could be matched somehow.

It seems like the macro is pretty close to accomplish what I want to do. The only problem is that it includes other dates also. Meaning that if I sort based on 3 dates in SheetOne, it returns those 3 dates in SheetTwo, but also the rest of the list below:

AlmostThere.png

Would it be possible to have SheetTwo display only the sorted dates from SheetOne?

Also - would it be possible to be able to refresh SheetTwo independently or would I need to refresh SheetOne and then run the macro?

Many thanks again, xladept! I really appreciate the help.

Or maybe it didn't work as I thought it did.

If I'm not mistaken, it seems like this macro simply prints the content from Sheet One in Sheet Two? I created a random variable now that I just filled with LL, OO, LL, OO, etc.

Based on what I see here, it seems like SheetTwo simply is printed with SheetOne data.

OrMaybeNot.png

This wasn't quite what I wanted.

Basically, what I want is perform a sort in Sheet Two with identical dates to those in Sheet One based on the sorting of dates I did there...

There should be no change in the data in Sheet Two as this is/should be different data. It's just the dates/day that's similar.

Hi,

As it is now, it seems like this macro simply inserts the data from SheetOne in SheetTwo.

What I really want is to have a sort performed in SheetTwo on the column Date such that sorted dates in SheetTwo are equal to sorted dates in SheetOne. The data in column H will however not be the same as in SheetOne.

I hope that made sense.

xladept,

Amazing! I'm literally smiling right now.

Exactly what I wanted. My final table will look a bit different, but I hope and think I'll be able to implement the rest on my own.

Thanks a lot!

xladept (or others inclined to answer),

I was hoping I could ask for further help on a very related issue.

I have a workbook with a Main Sheet with stock market data where each row represents a date/day.

I would like to create a macro such that I can

1) Filter data in Main Sheet

2) View the Following days in a similar sheet (Following Days) in the same workbook

Let's say I filter the date 18th of March and 26th of March in Main Sheet.

I would then like to check dates 19th of March and 27th of March in Following Days sheet.

For practical purposes, it might be that I want to see what happens after a day with negative price change of a certain size.

3) Additionally, it might be interesting to also check Preceding days, i.e., 17th and 25th of March in another sheet.

But the important thing is the next day.

Is this something that could be done in VBA without too much trouble?

Would greatly appreciate any help on the matter.

Best regards and happy Easter!

Also, to clarify. Finding just two dates would be easy. But in practice, I will probably have 100s of days where I want to check the 'next' day. It would be great if this could be filtered automatically.

xladept,

Amazing. As always!

Thanks a lot!

There does however seem to be a problem with Monday and Fridays. I could perhaps have clarified this further initially.

The data set contains only Monday to Friday and excludes Saturday/Friday.

1. If I filter Mondays, the sheet Preceding Days will be empty (instead of returning Fridays).

2. If I filter Fridays, the sheet Preceding Days contains Thursdays, BUT, Following Days is empty.

Would there be a way to fix this?

Thanks in advance!

Elijah

Amazing, xladept!

Seems to work great! I tried giving you reputation, but not allowed to do so yet.

Thank you so much!

Best regards.

Hello,

I ran into some issues using this code. Excel simply crashes when I try to execute. What may the cause be when a macro crashes? I'm used to a macro not running and returning an error. But crashing seems less common.

Thanks in advance.

Elijah

Originally Posted by xladept

Is there a message accompanying the crash? What line is highlighted when it crashes?

Hi,

Sadly, no. Normally, I'm used to the 'subscript out of range' or similar. But in this instance I it seems like Excel is 'turning on the engine and starting a huge operation. It simply freezes (not responding) and I have to 'kill it' using task manager.

I tried sending you a private message, but not sure if you received it. I'm reluctant to post my full sheets online as they contain sensitive information, but I could alternatively post screenshots and the VBA code, if you have the time and interest to take a look.

Thanks in advance.

Elijah

Originally Posted by xladept

I guess a screenshot might give me a clue. How different is the new book?

Great. In my mind, the sheets should be similar. There might be a few extra columns here and there. But I would assume that the location of Date is what matters? And that one should be the same, I think.

I'm posting three screenshots. The first two work fine. The third crashes completely. I just tried again for the nth time. I'll post the code for each also.

Thanks very much in advance.

PS: Sorry about the censoring. A bit careful when posting online for the entire world. Wouldn't be so careful in private.

Originally Posted by xladept

The only apparent difference is that the last sheet starts in row 5 instead of row 6.:confused

I suppose that could cause the program to encounter a null row?

Hi,

Does not both "MacroSwings" sheet and "MainLegPattern" sheet have the header at row 6?

The filter for "Instrument" and "MacroSwings" is working. It's the "MainLegPattern" which does not...

They look similar to me.

Hi,

Thanks a lot for the new macro!

I just tried it.

It ran for a second, but then I got the freezing again. However, this time Excel managed to 'snap out of it' and I got this error. See picture below showing both the error message and editor:

Uten navn.png

Best regards.

Elijah

Originally Posted by xladept

Maybe:

Please Login or Register  to view this content.

The next time try clicking debug and hovering over r - then you can see where the problem is

Aha.

But this seems to refer to the bottom row?

As far as I can tell, nothing. It does not even seem to exist.

It's below the bottom row. No worries, no rush on my end and no expectations. Just very happy this could be resolved. Have fun at work.

Uten navn.png

This was very helpful!

Hey guys,

Thanks so much! I'm out the door now, so will need to comment later. I first tried 'xladept's code and it seemed to work great. I wanted to test some more, but then my sheet crashed. I don't think that's related to the macro as the sheet is a bit unstable regardless of macros. Comments on that would also be appreciated, but I think it's probably because it's very big...

I'll comment later.

Thanks.

Elijah

Originally Posted by xladept

Glad the new code worked (at least once). There should be no limit on the number of rows that can be filtered

Thanks for all the reps!

The rep is well deserved. You should have gotten more, but I'm not able to give more it seems.

Many thanks again for all help! I just tried it and it seems to run smoothly. The crashing of sheet happens at times independent of the macro and needs to be adressed as a separate issue. But I suppose it's generally because it's a bit big with multiple sheets referencing each other.

Have a great weekend!

I'm not sure if it's appropriate to ask in this thread, but seeing that you're quite proficient in VBA - you wouldn't happen to have any ideas on this particular problem?

Basically, the problem was solved by Jindon in his version of Excel (2013). However, it does not work properly in Office 365 which I'm using...

Basically, I'm looking for a macro which lets me filter values based on a +/- boundary above/below a selected cell value.

Best regards and thanks.

Originally Posted by xladept

Hi Eijah,

This is actually a filter rather than a sort - let me know if this works for you:

Please Login or Register  to view this content.

Hello again,

How would this look if I wanted to filter the same dates/rows from one sheet (Sheet2) in another (Sheet1) in the same workbook? Can I use the same code?

Please Login or Register  to view this content.

I tried implementing it by simply setting w1 and w2 with the same workbook name. Is that okay? Regardless, I couldn't quite get it to work. It seems like it's filtering column A instead...

Any tips are welcome.

PS: A great addition would be if when filtering Excel also opens Sheet 1. But not sure if that's possible to accomplish.

Thanks in advance.

Elijah

Originally Posted by xladept

Hi Elijah,

This works:

And - Thanks for the rep!

Indeed it does, xladept! You never disappoint.

I also added:

ActiveWorkbook.Sheets("Sheet1").Activate

at the end (between End With and End Sub) to have the macro go to Sheet 1 after filtering.

It seems to work.

Do you agree on that code?

Elijah

Originally Posted by xladept

That code is fine. But you could be more succint:

Please Login or Register  to view this content.

And thanks for the rep!

Worked great also!

You're of course very welcome. That's the least I could do.

Thanks again!

Hello again and merry Christmas!

So, I managed to accomplish this successfully the last time: Filter Sheet 2 based on filter or content in Sheet 1. Using the code in post #41. I do this in sheets that look a bit different than the ones attached above.

Anyway, today, I tried to 'reverse' this macro in order to Filter Sheet 1 based on filter or content in Sheet 2. I only switched the w1/w2 name. This approach works perfectly on the sheet attached above, but when I try to 'reverse' the macro for my current sheets using the same logic, I get this error:

Uten navn.png

Any advice on this without me attaching the workbook?

Best regards and thanks in advance.

Elijah

Thanks anyway!

I don't really know code, so I can't comment meaningfully, but I'm also baffled since I don't understand how it should make a difference?

I also tried to set up the macro between two separate workbooks, but I get the same error.

Very odd...

Originally Posted by xladept

It works on the last sample you sent me. Can you send me the sample that doesn't work??

Hi,

My workbook is rather large and have multiple sheets, so I decided that I would delete all the other sheets before sending you a copy. This is both to reduce file size and also since it contains sensitive information.

Guess what? After removing all those other sheets, the macro now works 'both ways'.

No other changes made...

Now I'm really puzzled?

Elijah

Replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

did the trick.