Track changes btw two sheets and list data

[jw01]

All

I have two sheets (current data vs. prior data) in the attached file.

In my "summary", I have two tables C:J columns show "newly added" and columns L:S is "master listing".

In columns C:J -> I want the macro to copy/paste info from "current data" sheet. I need it to list the name of each employee only once, with their ID# etc - FYI: I want it to list only "new" individuals that are not listed on the master sheet, therefore, "newly" added from the current data sheet. I have provided the column reference in row 5 in the summary sheet, that are to be extracted from the current data tab.

In columns L:S -> I want to show "master" list that consolidates both current and prior data tabs, and copy/paste the data onto the respective columns.

Is this possible? pls help...thank you.

[jw01]

any thoughts on this guys?

[antoka05]

You could try to use this code... I hope that it's what you need:

Please Login or Register  to view this content.

Regards,
Antonio

[jw01]

You could try to use this code... I hope that it's what you need:

Please Login or Register  to view this content.

Regards,
Antonio

Hi Antonio

This works great for the most part.

A couple of modifications:
a) is it possible to apply a filter to entire row 11 + have number formatting for columns H, I, P, Q?
b) master listing (columns L:S) should show entries that are in current and prior data (consolidated); whereas newly added (columns C:I) should show data that is unique/new/ in current data tab, and not in prior data

is that possible?

[antoka05]

I changed the code, I hope that now it's fine for you:

Please Login or Register  to view this content.

Regards,
Anronio

[jw01]

I changed the code, I hope that now it's fine for you:

Please Login or Register  to view this content.

Regards,
Anronio

Hi Antonio

This works super wow - your amazing.

That said, I was filtering through to validate some data and noticed, in the "master listing" table, it's replicating the name twice b/c it exist on the current data and prior data tab.
-For example, if you run the macro, and filter column M by the name "burns, paul" ->it will list this name twice with the same value.
--> I would like the macro to show the name only once. I realize it exist on both sheets, that entry with that value should show up only once, make sense?

Pls let me know if this is do-able. I truly appreciate your awesome help! this is fantastic

[Arkadi]

jw01,

Please try not to quote the whole previous post unless you need to quote a specific piece for context? It makes it much easier to read. Thanks

See if this small modification resolves your issue? I've added a second dictionary that will check whether an item from current data was previously written on the right table, to avoid placing it twice:

Please Login or Register  to view this content.

[jw01]

Hi Arkadi

Oh wow - superstar! thanks so much for modifying this

Question -> column G and column P are blanks, so I want to remove them. Im trying to follow the code, but not sure exactly...if I remove column G and P, what line will I need to edit in the macro so the data adjust accordingly?

thanks again for your support/help!

[Arkadi]

The parts of the code that say "(drow, x)" (or drow2,x) where x is a number... those are the column references. Just adjust the number for the value to end up in the correct column.

Also, in the sorting section, you will have to change some columns... C will be fine, but L will become K (and N become M) I assume, and also the
".SetRange Range("c11:j" & dRow)"
and .SetRange Range("L11:S" & dRow2) will have to be adjusted... to c11:i and k11:q respectively

[jw01]

Awesome...thanks so much

[Arkadi]

My pleasure! Antonio did all the heavy lifting though

[Arkadi]

All sorted out jw01? I know your deadline is coming up...

[jw01]

Hey guys

one more question - i placed a formula in columns W:Z

a) When I run the macro, it deletes the formula I have in there. Any way i can get it to not delete?
b) can I put another macro that when I click it "clears the content from row 12 down (columns C:S)?

Thanks.

[Arkadi]

right now this line:

Please Login or Register  to view this content.

removes the existing data (but does it across the entire row from 12 until the end of the sheet).
If you change it to:

Please Login or Register  to view this content.

then only those columns (C to S) will be clared. If you deleted 2 columns then maybe it should be Q, not S, but that's your call. This will keep the formulas in W:Z

As for a macro to just clear the data, make a new sub, put in this line of code:

Please Login or Register  to view this content.

and assign it to a button or shape.

[jw01]

fantastic great thanks for the outstanding support to both Arkadi and Antonio

[jw01]

Hi gents

I was wondering if I can get your assistance once more.

I was wondering if the following can be added:
a) delete column G11, P11, Y11 (I would do this, but then the macro gets unaligned)

b) I added a new table columns U:AB
- essentially, I want this table to show the individuals name and ID that "already" exist on both sheets, however, have a new "matter #". The matter # is column "J" on the current data and prior data sheet. The matter # is our book of business ID. So we want to see from all the people, which existing ones had new "matter #" added. I have manually added in the example in columns U:AB and highlighted in green to show as an example.


Is this possible? I have attached an updated workbook. pls help. thank you as always!

[jw01]

any thoughts on this guys? thxs

[Arkadi]

is the code supposed to delete the columns, or you just want us to remove them manually and then adjust code for the new columns?

[Arkadi]

Give this a shot:

Please Login or Register  to view this content.

[jw01]

HI Arkadi

you are the greatest

cheers a million. I will try this out and let you know but your an angel gracias

[Arkadi]

Hope it works out
Thanks for the kind words.
De nada!