Copy rows from one sheet to another based on match

    Hi forum


    I've been away from Excel for a while and need some assistance to clear my brain freeze.


    I have a workbook with a sheet containing employee data and want to copy the Employee details from Column B to F, where the Employee ID matches the Employee Number on Sheet1.


    In my example, Sheet 1 Employee Number matches Employee ID on Employees Sheet and therefore Marga Walkinshaw data is pasted in to the relevant columns.... and so on for the remainder.


    Obviously this is mock data and I have thousands of rows in Sheet1 to fill in so requires a more efficient solution than just copy and pasting...

    Also, FWIW, the original Employee ID / Employee Number comprises of alphanumerical characters and is not a true number.


    Any help greatly appreciated.


    Smudge

    Hello Smudge,


    From your comments, would think the best solution for you is to use Filter ...


    What I do not understand is the fact you seem to have 5 identical rows in sheet1 ... ;)


    Could you indicate your final objective ... since a relational database is very often the ideal situation ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim

    Each row is a unique action that the employee undertakes at different times on different dates, hence what appears to be identical rows - but are not. The employee may make several actions at different times during a given day

    The employee data that I want to copy is fake and not the real data - the proper data is more pertinent to the the area, locality and department that the employee works in... which I cannot publish here.

    My overall aim is to report on employee 'performed actions' by area / locality / department etc... and ultimately break it down using slicers to and the only way I can see of doing this is by combining the report of employees actions against the data supplied by employee details, which are provided by two disparate systems in CSV format and so using the employee ID as the identifier, find out where the employee works, I can report how many ' actions' were performed by which area / locality / department etc...

    The table will then be used to populate pivot tables / drive a dashboard etc.


    Hope that makes sense.


    Many thanks

    Hi again,


    My interpretation of your explanations is that there is no need to copy data between your sheets, especially given the probable actual number of records you are dealing with.


    In addition, you appear to already operate with a clean workbook, with identified tabs holding data and the so crucial field Employee ID to create your relations.


    With your objective of a final analytical Pivot Table, you can use Power Query to combine all your data from your different tables and then create a pivot table from the combined data.


    For inspiration, see a good example from Debra Dalgleish :


    Power Query Combine Tables


    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim


    Thanks for the rapid response (as usual) ;)

    Having taken some time to watch and understand the video you directed me to and how Power Query can be used to transform data, I thought that this would be the ideal answer as it was inspiring... but having mocked up a recreation of the data to suit my requirements it seems it's not the way forward for me - unless I'm missing something.


    So, to summarise my initial problem (which I probably didn't convey very well). I want to match employees in one table (which is a record of certain actions that the employee performs during the day, over weeks / months etc) against a list of employees and their work details (Area / Team / Unit etc..) provided to me by HR which would allow me to create a report which shows how each Area / Team / Unit are performing based on the data supplied in the initial report of employee activities. (I've changed my mock up data file to reflect this rather than relying on the generic information that I originally supplied). The employee ID is the common identifier in both tables and is the obvious choice to use to get the required data.


    Having recreated the video as a test environment, all I seem to have done is duplicated two tabs of data into four and then added another two to produce a pivot table, which at first sight seems fairly unintelligible as it would appear that the report I want to produce is based on 'blanks'!

    If I filter out the blanks, then my data disappears along with it!


    This is why in my request, I thought the way forward was to match the employee ID with the one supplied by HR and then have some magic formula that copies the relevant HR details onto the end of each row of the employee activity?

    The result being I have all the relevant data on one sheet to pivot and chart to my hearts content instead of six... and no 'blanks' to obfuscate the pivot tables...

    (I think If I gave my boss that to look at, I wouldn't be posting on here any longer... as I'd soon be out of a job LOL).


    Any words of wisdom please?


    Many thanks

    Hi again,


    Sorry for the confusion ...

    My initial objective was to make your life easier ...:(


    Let me see what can be done with your approach to generate the Pivot Table you need to design .... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi again,


    A quick macro ... to be tested in the Activities worksheet


    I do not how many records you are actually dealing with ... (Copy could take a moment...for a large number of records) ;)


    Despite this inconvenient, hope this will help

    Glad to hear you have managed to keep your job :) 8) :thumbup:


    Thanks a lot for your Thanks AND for the Like ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    So, each time I press the 'Copy Macro' button, the number of columns being copied over from the Employees sheet into my table in the Activities Sheet, are duplicated.


    I'm guessing it's because of the line in the macro:

    sh1Col = wsh1.UsedRange.Columns.Count + 1


    So I understand, on first run, sh1Col will equal column F as the starting reference for the employee details to be copied to as the used range is A to E is occupied by data. (Which is correct)

    But then next time the macro is run, say after more data is added (or not) to the Activities sheet, Columns F, G, H, and I are occupied with the data that was copied to them from the previous run, therefore making the starting point to be copied in the .UsedRange.Columns.Count + 1 as Column J and then subsequently, next time, it's column N,... R and so on.


    How do I prevent this so my table stays within the bounds of Columns A to I ?


    Thanks in advance


    Smudge

    Hi,


    Revised macro below :

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

    Keep on making your boss happy ;) :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login