asked on

macro with filter function to sort data with two variables

In sheet 1 you will see that I add a number of rows to the database at certain times. I need a macro that sorts the sheet by currency pair (column H)) and then by date(column Q).
Then it should copy the cell (J,lastrow) to the relevant cell in sheet 2.
I tried to do this using Vlookup but that didn't work as there are many rows with no data, so I thought a macro would be better.
The macro should then loop through the six currency pairs.

Thanks,
Chris
ExampleOfDatabase.xlsx

Frank White

Is all the sorting part of the goal (i.e. you want the data presented that way for ease of navigation/reading), or is the actual goal only to get the latest currency rate for each currency pair?

There are ways to get the latter without doing all the sorting, which is why I'm asking whether you want both or if you only thought sorting was necessary to get to that value.

CC10

ASKER

No the sorting is not relevant. All I am trying to get are the order rates in column J. In the example for EURUSD it would be J129.

If I were to add a set of new rows, if the order rate has been changed, it would show up with a new date. That is why the criteria should be the currency pair (EURUSD) and the latest date, and that should be linked either with a formula or copied with a macro to Sheet 2, B3 (for the EURUSD) and so on...

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

I say with a very big smile, doing this with formulas is a bit more efficient. In Sheet 3 I give you a table of output where you can enter new pairs in column A and copy down the formulas.

The formula (with Pair in Column A for the lookups):

[A2]=INDEX(Sheet1!A$1:A$5000,MATCH($A2&MAX(IF(INDEX(Sheet1!$H$1:$Q$5000,,1)=$A2,Sheet1!$Q$1:$Q$5000,0)),Sheet1!$H$1:$H$5000&Sheet1!$Q$1:$Q$5000,0))

Confirmed with CTRL+SHIFT+ENTER and copied down/across as needed to replicate the source sheet, but just for the pair/latest date combination. Works for the first 5000 rows of data so adjust as necessary. This is an array formula so after hitting the CTRL+SHIFT+ENTER, you'll see curly braces around the formula.

See revised workbook. this table is on sheet 3.

Cheers,

Dave
ExampleOfDatabase-r3.xlsm

CC10

ASKER

Perfect! Thanks very much for your help.

Chris

CC10

ASKER

Yes, the formulas work just as well.

I have one question, when I copied the VB script into my original workbook and try to run the macro, I get a compile error: User -defined type not defined at
Dim myDict As Dictionary

dlmille

You'd have to add the reference library.

Instead, change line 13 to:

Dim myDict as Object

And you should be good to go.

Dave

CC10

ASKER

Thanks again, all works now. Really very helpful.

dlmille

Remember, you have inifinite points, so ask as many questions as you need.

Cheers,

Dave