PDA

View Full Version : VBA to collate information from 3 workbooks into one Ranked Table using filter



zaincmt
07-22-2015, 05:12 AM
Hi All,

I am a bit of a newbie when it comes to VBA so I am hoping you can assist me in this fun little project.
To give you an Overview basically I need to create a Table which Ranks Areas of the business based on information from 3 separate spreadsheets.

The table I wish to create will look something like this - Called "U1SE - Matrix.xls":

13976

13977


My Filter data / Macro button will run from "U1SE - Branches.xlsm"

13975



Then I need to pull data from these 3 files to create the ranked table "U1SE - Matrix.xls"


13972
13973
13974




TEST RES report.xls - Filter / VBA required
13974

In the 1st file ("TEST RES report.xls") I need to filter the data using the filter/macro workbook "U1SE - Branches.xlsm". Then the column I need to filter is "E" and the data that needs to be pulled into the ranked table is Column "F".
Column "F" should then be placed into the created table "U1SE - Matrix.xls" (the top image) in Column "C" I then need to put a ranked number in Column "D" with the highest % number in Column "C" is Ranked the lowest. Obviously if there are two numbers the same then they should both get the same ranked number.



TEST DOWN (With Date Parameters).xls - Filter / VBA required
13973

In the 2nd file ("TEST DOWN (With Date Parameters).xls") I need to filter the data using the filter/macro workbook "U1SE - Branches.xlsm". Then the column I need to filter is "A" and the data that needs to be pulled into the ranked table will be a Count of how many duplicate occurrences there are in just column "A".
The duplicate occurrence count from Column "A" should then be placed into the created table "U1SE - Matrix.xls" (the top image) in Column "E" I then need to put a ranked number in Column "F" with the lowest number in Column "E" is Ranked the lowest. Obviously if there are two numbers the same then they should both get the same ranked number.


TEST CF 13-07-2015.xls - Filter / VBA required
13972

In the 3rd file ("TEST CF 13-07-2015.xls") I need to filter the data using the filter/macro workbook "U1SE - Branches.xlsm". Then the data I need to filter is in the worksheet "Branch" and column "A". The data that needs to be pulled into the ranked table will be from column "G"
Column "G" should then be placed into the created table "U1SE - Matrix.xls" (the top image) in Column "G" I then need to put a ranked number in Column "H" with the highest number in Column "G" is Ranked the lowest. Obviously if there are two numbers the same then they should both get the same ranked number.

So the file table that is created should look like the file / below image. I will be grateful for all help. I do have some VBA code in my filter/macro sheet but I can't get anything ive searched for to work.


13977