Automatically consolidate table entries and reformat

[Seraph122]

Hi everyone,

Can someone help me build a macro that automates the following process? I've attached a workbook that has an example. Here is a quick outline of what I'm trying to accomplish. Once someone pastes into the "Input" worksheet, and the macro takes the following steps to get to the "Output" worksheet. The data in the Input tab in columns 2, 3, and 8 are irrelevant for this so I just highlighted them as red to ignore.

1) Deletes the rows that say "Delete" in Column 4
2) Column 1 becomes Column 1 in the Output Tab with a new 4 digit number format "###0"
3) Column 5's names "Cat" and "Dog" get converted and changed to "Lion" and "Wolf", respectively
4) Column 6 in the Input tab becomes the Date column with a new format "yyyy-mm-dd" with a text string "APPEND" added onto it.
5) The values for the Number, Animal, and Date become consolidated - so there is only ONE entry per Number/Animal/Date combination. I know this manually would require SUMIFs, but I'm not sure how to incorporate this in VBA.

Of course I'd like to have these change (e.g. the headers of the Output tab, the Cat/Dog/Lion/Wolf names, and the "APPEND" text string.

Could someone assist me with this? It would be greatly appreciated

Thanks!

[Marcol]

What is the point of this?

Does this formula generated table look anything like your expected result, your explanation isn't clear, as to what you need in the last column.

[Seraph122]

Sorry - should have it made it more clear - the Output worksheet is my expected result of the macro. The Input worksheet is what is being modified. The output tab is basically a shorter version of the input tab - it only has 4 columns, and it has fewer rows because it doesn't have the "DELETE" rows, and it consolidates the Store-Animal-Date combinations by using sum-ifs.

It has to be a macro because this will be a consistent activity with much bigger files. I just created the example data to get the point across. I hope this makes sense, let me know how i can clarify it if needed. Thanks for your assistance!

[OllieB]

Have a look at the attached workbook. It should offer a basis from your requirements.

[Seraph122]

Hi Ollie - Is there a macro in this workbook? I can't see it for some reason

[OllieB]

Yes there is. Inside worksheet Input as you wanted the code to be triggered by a change to the data on the worksheet

[Seraph122]

Hi Ollie - sorry about the confusion. I didn't need the code to be triggered by a change in the input - it can just be activated from the macro menu afterward. It doesn't have to be real time. Is that a simple change?

Also I think the output has more or less entries than what i originally had in the Output tab. The Output entries should add up to the same sum as the "Don't Delete" entries in the Input tab.

Does that make sense? Sorry again for the confusion, I appreciate your help greatly!

[OllieB]

a. move the routine to a module and rename the routine to anything you want to and change the designation to Public

b. Not sure what you mean. If we look at 0001 the sum shown on the output worksheet is 2977 which is composed from 1202 and 1775 - I think that is correct

ignore worksheet output (2) as this is just a copy of the original worksheet

OR - should the Delete rows be deleted before they are counted?? in that case, the delete code should be moved up in the code

[OllieB]

If my last question is to be answered yes, try the attached workbook

[Seraph122]

Hi Ollie, it looks like your last post was great, this is really close to what I need.

A few quick questions - I'm trying to change the names:

So "dog" in the Input should become "wolf" in the output, and "cat" should become "lion" in the output worksheet.

How do I change these?

So right now,

Cat -> Lion, but I want it to be "123" -> "321"
Dog -> Wolf, but I want it to be "567" -> "765"

Whenever I try to swap the names in the code and run it, it tells me there is a type mismatch error in the strKeyValue lines.

Thanks for your help OllieB!

[OllieB]

I can't tell what you are doing, or what you are changing at which place in the code. I have introduced a function for the replacement of values from Cat to Lion, 123 to 321 etc. The function is called ReplaceName and that is the only place you need to make changes for that functionality.

Try the attached worbook

[Seraph122]

OllieB - This is GREAT!!! It is just what I needed! You are awesome and generous!!

[OllieB]

Seraph122, you are welcome. Please remember to click the star icon to show your appreciation