Excel-VBA, Copy data from sheet1 to sheet# without duplicates if data is already on sheet#

Hello,

I apologize as I am not very proficient with VBA in the slightest. I spent nearly 3 full business days searching, reading, and viewing other posts, VBA tutorials and examples before finally deciding to make this post because I just can't find the things I need. I can find bits and pieces but then they aren't compatible or work together so I have to start all over....

What I have currently....

I use a lot of conditional formatting to make organization and later-macro use easier for me (it was the best solution I could make with my current skills). So, there's 6 statuses in Col-A, which are....
D = Deported - Will turn the corresponding row Yellow
M = Missing - Will turn the corresponding row Black
N = Not Ready - Will turn the corresponding row Red
P = Pending - Will turn the corresponding row Purple
X = Invalid - Will turn the corresponding row Green
Y = Yes/Ready - Will turn the corresponding row Blue

I have a "MAIN" sheet of data that has multiple categories of data, the best example I can give is....

Line 1 = (Set header buttons, row locked so users can auto-click to go directly to the month they need in the sheet)
Line 2 = JAN (used solely as a header)
Line 3 = text boxes explaining the colour coding system for each Status type.
Line 4 = Status / Last form / Date / State / ID # / Last name / First name / Pending status / Level / Last date / Days late / Region# / City
Line 5 = Y(N)(D)(M)(P)(X) / 7-19-24 / 3-1-25 / DE / 1234 / Doe / John / Pending OOS / 3 / 8-1-24 / 4 / 1 / NULL
Line 6 = Y(N)(D)(M)(P)(X) / 9-19-24 / 4-1-25 / NE / 567 / Doe / John / Pending OOS / 3 / 8-1-24 / 4 / 1 / NULL
....and so on and so on before getting to next month like this....

For Line 6/251, normally it would ONLY be one of the letters, but I put all of them and the ()'s to show you all what 'might' be in that column.

Line 247 = AUG (used solely as a header)
Line 248 = text boxes explaining the colour coding system for each Status type.
Line 249 = Status / Last form / Date / State / ID # / Last name / First name / Pending status / Level / Last date / Days late / Region# / City
Line 250 = Y(N)(D)(M)(P)(X) / 6-19-24 / 2-1-25 / WA / 976 / Regal / Karen / Pending OOS / 3 / 5-1-24 / 1 / 1 / NULL
Line 251 = Y(N)(D)(M)(P)(X) / 8-19-24 / 1-1-25 / VA / 14426 / Wolf / Jay / Pending OOS / 3 / 7-1-24 / 2 / 1 / NULL
....and so on and so on, repeating for the entire calendar year....

I then have "WORKING", "MISSING", "READY", and "DEPORTED" sheets that need the data from the "MAIN"sheet(above).

All 4 of these sheets have 'almost' identical layouts as the main sheet except they do NOT include Month separations, they go like this....
Line 1 = text boxes explaining the colour coding system for each Status type.
Line 2 = Status / Last form / Date / State / ID # / Last name / First name / Pending status / Level / Last date / Days late / Region# / City
Line 3 = Y(N)(D)(M)(P)(X) / 7-19-24 / 3-1-25 / ICE / 1234 / Doe / John / Pending OOS / 3 / 8-1-24 / 4 / 1 / NULL
Line 4 = Y(N)(D)(M)(P)(X) / 9-19-24 / 4-1-25 / NE / 567 / Doe / John / Pending OOS / 3 / 8-1-24 / 4 / 1 / NULL
....Continuing like this until I reset the calendar year, or it's simply blank because there's no more data available yet....

What I'm currently using VBA-wise;

Please Login or Register  to view this content.

My CURRENT set-up above is a macro that lets me run a search on ALL my columns in "MAIN" sheet based on their status type in Col-A, which then copies the full row of that line of data into the correct Status sheet... So if Line 5 is status Y and Line 11 is Status D and Line 13 is status Y.... When I run the macro, it will copy Line 5 & 13 to the "Ready" sheet, but it will copy Line 11 to the "Deported" sheet. -- the same would happen for any lines with M being copied to "Missing" and so on...

What I NEED/WANT....

• I need to be able to continue to fill in data on the MAIN sheet (this is the source material for data) ... Once I have the data input.
• I then need to be able to easily and quickly sort through ALL of it (easily 100-400+ lines of data) and have each ROW with the specific status data copied and sorted onto the other Data sheets.
• BUT -- I need to run this macro multiple times in a day/week as I get new data spontaneously or throughout the work-day, and it creates 'duplicates' every single time it's run.
• I want it to check the Status sheets to make sure the data it's about to copy over isn't already on the sheet. I also do not want it to over-write any data that is on the sheet.
• Lastly but extremely annoying/tedious, is EACH sheet has "conditional formatting" to maintain the colour coding system. However, every time I copy data over, whether it's manual, or by the macro I use above... It ends up completely destroying, editing, or mass-duplicating the "Conditional Formatting Rules" on each sheet, so I have to keep going back in and manually fixing the formatting formulae each and every time. I would like the data to copy over and use the SHEETS conditional formatting instead of trying to make it's own or copying the previous sheets rules.

In the examples above the; ""Line 4 = Status / Last form / Date / State / ID # / Last name / First name / Pending status / Level / Last date / Days late / Region# / City "" << This is what's used for sorting on all the sheets, and are ordered by Column, so A / B / C / D / etc-etc....

• Column A is Status, which is used to determine what row needs to be copied to the right sheet.
• Column E is ID#, which is used to determine if there's a duplicate (because each row of data will always have a unique ID# and there will 'never' be another ID# with the same #, no matter what.
• All the other Columns are important data needed for the Row to have the necessary information, but aren't viable for sorting or coding because multiple ID#'s can have the same date, the same names, same status types, and so on... The only thing that will ever be 'unique' across all rows, is the ID#.

So, to wrap everything up in a hopeful TLDR? I have 5 sheets (planning on making a 6th for Invalids). MAIN, Working, Missing, Deported, and Ready.
I input data into the MAIN sheet, once I have assigned a status to each row of data in the MAIN sheet. I then need a code/method to search through every single row of data by Column A-Status type, and it will take every status type and then copy that line of data's ENTIRE row to the right sheet, so Status M will be copied to Missing, Status Y will be copied to Ready, Status D will be copied to Deported, and so on. However, BEFORE it copies the data to those sheets, it needs to check and make sure the data it's about to copy from MAIN is "not already in there" to avoid making duplicate entries, which can be done by making sure Column E-ID# isn't already present on the sheet it's copying to.
I need to be able to update the MAIN sheet multiple times, and periodically re-run the macro to update the sub-sheets after adding new data, while KEEPING all of the old data I already copied from previous uses of the macro.

.....I hope I gave a detailed enough example of everything, and I really did try my best to learn and figure things out on my own. I'm not sure if what I want to have/do is even possible in Excel at this moment... But I found VBA's for doing the formatting copying, I found some for copying and avoiding duplicates, I found others for sorting the data... But I never found something that did a mix of all those things like I need above, and when I try to mix the ones I did find it colossally breaks everything.... So, I'd just really appreciate it if someone smarter could help me out here, I swear I'm not being lazy, I genuinely tried to understand this. :(

This one reply is no longer valid since I ended up uploading a censored file anyways, ^{Also before anyone asks, unfortunately due to the nature of the data involved I can't post the workbook/sheets directly, unless I spend some time censoring every last line of data... WHICH -- if y'all tell me will be the best way for you to help me then heck, I guess I will set some hours aside to make a new workbook with all the same formats, rules, and code, and then censor all the data to upload it and get what I need.} That is how desperate I am at this point for someone to help me.

Thanks in advance to all of you!

Here is a formula-based approach (assuming that the 2025 version supports the SORT and FILTER functions).
1. Put the MAIN sheet into standard record format taking advantage of the Excel table features to filter by month instead of using hyperlinks.
2. Pull the desired information into the other four sheets using formulas like: =FILTER(Table1,Table1[Status]="m")
Note that for the WORKING sheet the formula reads: =SORT(FILTER(Table1,(Table1[Status]="n")+(Table1[Status]="p")),1)
I imagine that some modifications may be needed but will wait to see if the idea of using a formula-based approach is acceptable before proceeding.
Let us know if you have any questions.

I've tried using it a good bit and so far it definitely seems to cover the 'sorting' requirement I need, and I especially love that it re-sorts when changes are made... Such as making someone M/N and then changing them to Y, it auto-updates and removes/adds them to the right sheet(s).

I think my biggest two issues now are.... Duplicates, and Conditional Formatting formulaes breaking.

1 - I added the next 4 months' worth of data/entries into the list to test it out, and sometimes there's duplicate entries for the same data/entry on different months... I need it to keep the 'first' entry while removing the duplicates. So for an example...
Line 3 / "S | date | date | state | 110469 | LN | FN | S2 | T | DD | LD | D | R | C | | JUNE"
random other entries
Line 55 / "S | date | date | state | 110469 | LN | FN | S2 | T | DD | LD | D | R | C | | SEPTEMBER "
more other data
Line 85 / "S | date | date | state | 110469 | LN | FN | S2 | T | DD | LD | D | R | C | | DEC"

I need it to somehow figure out there's a duplicate of the same user/data entry, and only put the 'first' entry of it in the final/correct sheet. So in this example, Say the data ends up being a "Y/ready", it would port over to the Ready sheet but it would put all 3 entries in the Ready sheet. I want it to only put the JUNE entry and to discard (or sort) the duplicates elsewhere.
Attachment 892261
As you can see sometimes there's no duplicates and other times, there's a LOT of duplicates.... When importing them over the course of an entire year it's difficult to find them all initially (which is the biggest reason I set up the conditional formatting to let me know there's a duplicate "somewhere" but then I have to hunt them down and figure out if they've already been done or not... Etc-etc. It's incredibly time consuming. I also don't think my duplicate/remover-macro works with the table so I'm at a loss now on that. I genuinely like this auto-sorting table, but if it can't handle duplicates then I have to send a very cluttered sheet to our final verification person. (My final sheets working/missing/deported/ready - all get sent to someone for a final push in our main-main system that's way above my pay grade. I need to send them all the individual users without the duplicates). But when I try to remove the duplicates from the final sheet it re-adds them cause they're on the main sheet.

(I only knew how to make this work via the macro, but you managed to give me a surprisingly amazing non-macro sheet which I'm still struggling to figure out how it works so I can use it on other sheets/future but thank you none-the-less!)

.... As for the 2nd issue;

2 - Everytime I port over or input the new data from our assignment list (a sheet of data/entries that I don't have access to, a list is sent to me every week/month that has all the info I'll be using). I copy the list over/in and it breaks every single page worth of conditional formatting. So I have to constantly go back in and re-make the formulaes for each page to keep all my formatting. Is there anyway to keep this from happening? Cause the conditional formatting will never change and it should 'always' be....
Attachment 892259

------------------

If I need to reupload the example sheet with the new sets of data/duplicates/issues I can, but I'd like to think I could follow direction enough to fix it long as I'm given guidance. (Because I'd feel bad just constantly giving y'all the workbook and to make it work for me x-x).

Possible idea? -- I have no idea if this is something feasible but... For the whole duplicate issue, since the MAIN sheet has to keep 'all' entries, including duplicates, but the sorting/final sheets need to ONLY keep the 'oldest' entry while deleting/not including the newer entries... If that's too difficult or not feasible to make, then maybe would it be possible to have a completely other sheet dedicated for 'Duplicates'? So, the first/oldest entry will go to the sheet it needs to, but then any extra/duplicate entries will go to a Duplicate mass-dump sheet that just keeps all the newer entries on it?
- - - I know the easiest method would be to simply remove the duplicates from the main sheet, but sadly that's not possible and my work requires 'all' entries, be in the main sheet, because the duplicates are for different months and we need to keep the stats of which month had how many x/y/z's for it, duplicate or not.

NOTE: Also, as per my initial post, the 'ONLY' way to tell if something is duplicate, is if the SID# (Column D) is the same. (unrelated but extra detail->) This is also how data entry errors are also caught cause sometimes people will give me the wrong status or name or county or state, but the SID# lets me know they put the wrong data in it for someone else.

Selecting attachments 892261 and 892259 results in the following error message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
It would be better to upload an example workbook.
That said I feel that the following addresses the duplicates issue:
1. Add a column to the table on the Main sheet: =COUNTIFS(INDEX([SID'#],1):[@[SID'#]],[@[SID'#]])=1
2. Change the formulas in the other sheets to read similarly to: =DROP(FILTER(Table1,(Table1[Original]=TRUE)*(Table1[Status]="m")),,-1)
Let us know if you have any questions.

Clear/Pending column Added to the Main sheet.
Formulas for Working and Missing sheets is modified to sort by state: =SORT(DROP(SORT(FILTER(Table1,(Table1[Original]=TRUE)*((Table1[Status]="n")+(Table1[Status]="p"))),1),,-2),4,S1)
Formulas for Deported and Ready sheets modified to sort by clear or pending: =SORT(DROP(FILTER(Table1,(Table1[Original]=TRUE)*(Table1[Status]="d")),,-1),S6,S1)
Note that on the Deported and Ready sheets sort criteria can be selected using the drop down in cell S6.
Note that on each sheet, except Main, the sort order can be selected using the drop down in cell S1.
Note that sorting the Main sheet by SID# does have some affect the output on the other sheets.
Let us know if you have any questions.

Using formulas, I don't know of a way to put clear/pending only on the Deported/Ready sheets and still have them stay with the correct row when the Main sheet is sorted/appended.
(This is a topic that asked about a lot on many forums including EF.)
I know too little about VBA to say whether or not it could be done that way.
I will look and see if there is a way to do this without breaking the formulas using Power Query.

Unfortunately I couldn't find a way to use Power Query to get the result you want.
I have asked other contributors to take a look and see if any of them have an idea of how to achieve the desired result.