Compare Lists & Copy Data

[itsnick]

Hello,

I need some help with a macro that is working in a very large data base, Im attaching a dummy workbook to assist.

I have two list one with just Z#'s and one with Z#'s and information, I need this macro to reference the list of numbers and if they have a match to copy them. It gets a little complicated because the first 8 digits are unique and there is a possibility the last two change.

IE
Z1234567800
Z1234567801
Z1234567802
Z1234567803

The first is like the master record and the subsequent #'s are attached to it changing the last two digits. The list will only contain the the Z# ending in 00 but I need to pull all the Z#'s that are associated with the first 8 digits.

I tried using an auto filter macro with a bunch of And operators but it didnt work

Please let me know if I can awnser any questions that will help with this.

Thank you

[Leith Ross]

Hello itsnick,

Where do you want the information copied to - a new worksheet? Is the entire row of the database to copied if there is match?

[itsnick]

Hello itsnick,

Where do you want the information copied to - a new worksheet? Is the entire row of the database to copied if there is match?

Im sorry I missed that it totally slipped my mind, yes it can be a new sheet or an existing sheet. Yes the entire row should be copied.

Thank you for the reply

[Leith Ross]

Hello itsnick,

Here is a preliminary workbook. The results that match are copied onto the "Results" worksheet. There is a command button on this sheet to run the macro. Here is the macro...

Please Login or Register  to view this content.

[itsnick]

Leith,

Thank you for taking the time to help with this it is much appreciated. I have some limited understanding of VB/excel macros, when entering actual data the results where sorted by number is there anyway to keep the original sort ? I didnt notice any sorting commands in the macro it self so I wasnt sure if this was the result of creating scripting dictionary.

Thank you again

[Leith Ross]

Hello itsnick,

The macro does not sort the data. The dictionary is loaded with the data from the List worksheet. The data is copied based the list the order's first 8 digits from "A1" down.. Did you want the sorted in a particular order?

[itsnick]

Hello itsnick,

The macro does not sort the data. The dictionary is loaded with the data from the List worksheet. The data is copied based the list the order's first 8 digits from "A1" down.. Did you want the sorted in a particular order?

Leith,

Is it possible to keep the original order on the list when its copied over, if not I understand.
Id also like to know if you can recommend some over view reading so I understand exactly how the macro works. So far ive just been searching the different parts of it via google.

Thanks again.

[Leith Ross]

Hello itsnick,

The macro should not be changing the order. It follows the order of List worksheet. The operation is quite simple.

1. The Dictionary is loaded with the Z numbers from the worksheet "List"
2. All the values from column "A" on the "Database" are placed in a Range object variable.
3. The "Results" worksheet is cleared.
4. Each cell in this range is then tested using the Regular Expression object "RegExp" and if it is a Z number then the last 2 digits are replaced with "00". The entire row from the "Database" worksheet is then copied to the "Results" worksheet.
5. The Regular Expression and Dictionary objects are then set to 'Nothing" to free the object references and memory.
6. The macro is done.

[itsnick]

Hello itsnick,

The macro should not be changing the order. It follows the order of List worksheet. The operation is quite simple.

1. The Dictionary is loaded with the Z numbers from the worksheet "List"
2. All the values from column "A" on the "Database" are placed in a Range object variable.
3. The "Results" worksheet is cleared.
4. Each cell in this range is then tested using the Regular Expression object "RegExp" and if it is a Z number then the last 2 digits are replaced with "00". The entire row from the "Database" worksheet is then copied to the "Results" worksheet.
5. The Regular Expression and Dictionary objects are then set to 'Nothing" to free the object references and memory.
6. The macro is done.

Leith,

Awesome explanation I understand now, basically regardless of how random the numbers on the list are they results will generate in whatever order they appear in the database which is what I wasn't getting. I also now have a much better understanding of the macro.


With regards to Regular expression how do you set the pattern ?( Im marking the thread solved if you would rather answer these questions via PM)

RegExp.Pattern = "(\w\d{8})(\d{2})"


Finally is there a way for me to view those objects. ( Im not trying to be a pain, just trying to learn from someone who obviously knows their stuff !

[Leith Ross]

Hello itsnick,

The Regular Expression pattern looks for 2 distinct patterns that follow each other. The first group of the pattern: (\w\d{8}) searches for a word class followed by 8 digits. A word class is defined as [a-zA-Z0-9_] (lower and uppercase letters, digits 0 through 9, and the underscore character). This will match the Z number and the first 8 digits. The second group: (\d{2}) looks for two digits immediately following a match of the first group. The parentheses indicate a group and each group is saved.

The groups are numbered starting with 1. In the Replace method, it references each saved group by the dollar sign then the number, like "$1". The Z and the first 8 digits are kept and the last 2 digits are replaced with "00". This allows the numbers ending in 01- 99 to matched against the list. If no matches were found then the original string is returned.