Data Merging

[Sasha 99]

Hi Guys ,
I have a workbook consist of 71 worksheet ,

In the first column of each worksheet i have a unique serial number that duplicate for all rows in the same sheet

In the second column of each worksheet i have a date which take from like 12/31/2017

Now i need to delete all rows from all worksheet that contain these dates in second column
"3/15/2017" "3/27/2017" "5/17/2017" "9/13/2017" "12/5/2017"

<< The previous part have been solved successfully >>

As i said before each worksheet has unique serial number in first column EXCEPT the last worksheet "sheet 71"

"sheet 71" contain a mix of all unique numbers that exist in all other worksheet , now i would like to distribute the content of "sheet 71" to other sheets depending on Unique Serial Number

So matched Unique Serial Number will be the key factor for distibutions and merging data , How can i do that in a quick way ?

Thanks

[scottiex]

is this what you want to do?

https://www.extendoffice.com/documen...on-column.html

[Sasha 99]

Not exactly , this VBA code << SPLIT >> the row into new worksheet based on a certain column , However
what i want is to << MERGE >> these rows with already existing worksheet based on the value of first column

Let's take an example to clarify the idea

If the Unique Number in "sheet 1" is 78902 , then i must look for and select all rows in "sheet 71" that contain 78902 and move these rows from "sheet 71" to "sheet 1"

If the Unique Number in "sheet 2" is 120546 , then i must look for and select all rows in "sheet 71" that contain 120546 and move these rows from "sheet 71" to "sheet 2"

and so on .....

Note :: For all worksheet , Column A is assigned to record Unique Number which duplicate for all rows in the same worksheet with no change , in addition to that Unique Number cannot be the same for two different worksheet

[kev_]

run in attached workbook with {CTRL} t

How code works
- sheet "References" contains an index of unique references
(my test references are different to the ones you specified)
- data in sheet 71 is filtered by each reference & data copied to relevant sheet
- during testing the deleted rows are copied to another sheet (can re-use as test data)
- only 5 "data" sheets in demo worksheet
- results of my test are in blue font on sheets 1 to 5

I have added procedure CreateSheetList to help you create the list of relevant sheets to add each reference against

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Sasha 99]

That's so generous and kind from you Kev , I tested the code and it work properly
Thank you very much