Update Master Table

I'm new to Excel Macros and have hit a brick wall on what I would consider
to be my primary workhorse macro! I'm embarrassed to admit that I haven't
mastered coding for movement of data around the spreadsheet much beyond
the macro recorder. I need something tight, efficient, and (instructively commented)!

I've got a spreadsheet consisting of a Master table with 42 rows, (2 rows
per record), and 14 columns of information. To the left of that is another
table consisting of live data from the process. It has anywhere from 1 to
24 rows with 10 columns representing Current/Live/Pre-processed information,
pulled in from the company web location.

Both tables start with an indexing column of 2 character alphanumeric IDs.
They are as follows: C2, D3, D4, E3, E4, F3, F4, G3, G4, H3, H4, I2, I3,
I4, J2, J3, J4, K1, K2, K3, K4.

MASTER TABLE Index Column is B4 to B44 (2 rows per record, in A->Z order)
UPDATE TABLE Index Column is Y4 to Y24 (Could be from 1 to 21 rows (Max),
1 per record, normally in A-Z order.

What I need to do is Key on the Master index column for a particular ID,
against the UPDATE index column. When there's a match I need to take the
updated values from the UPDATE table and assign them to the appropriate
cells locations in the MASTER table.

Example:
Starting with ID "C2" in the MASTER table, check to see if "C2" exists in
the UPDATE Table Index. If there's a match the following cell value
transfers must occur. Z4 -> E4, AB4 -> J4, AC4 -> J5, AD4 -> H4,
AE4 -> K4 and AG4 -> I4. If "C2 is not found in the UPDATE Table then skip
to next ID, (D3).

If ID D3 is found, another cell value transfer must occur. Z5 -> E6,
AB5 -> J6, AC5 -> J7, AD5 -> H6, AE5 -> K6, and AG5 -> I6. If "D3 has no
updates then skip to next ID and so on. Continue checking IDs and updating
until all IDs have been checked.


I'm having difficulty crafting a macro to search 2 columns against one nother,
specify the correct ActiveCell, than offset appropriately to place the desired
value in the correct location.

Any help would be greatly Appreciated!

Nick Danger

I think the following code will do what you want.
Please see the attached workbook for the context in which I have tested it.

The 'data values' in the Update table can be anything you like, and also the table can be any size. In the example the data values are all set to be the reference of the cell that they are in, purely as it made it easier for me to track that the the right data was going to the right places.
Similarly, to test it you can remove one of the Id's from the update table, then run the Update_Data macro and note that your 'Master Table' then has a gap in it for that ID as no data was available.
There is also an extra macro included in the worksheet which simply serves to clear all the old entries from the 'Master Table', again to allow clearer visibility when repeatedly testing the macro.
Let me know how you get on, and whether this is what you wanted, (or if I am way off the mark!)

Please Login or Register  to view this content.

Ok, I'll make the change.
You said:

they have a value from 0 to 5. This represents the background color to apply.

To apply where and to what?

Attached is the modified macro to cope with the 'colour' numbers, and also the code is below, (note red lines are old lines removed, blue lines are new code added).
If you tell me what is supposed to happen with the colour number, (and what it refers to, is it a ColorIndex?) then I can add that in.

Please Login or Register  to view this content.