Solved XL: Copy and Paste when change occurs in row

March 15, 2015 at 17:34:34
Specs: Windows XP
I'm doing a scheduling spreadsheet. I'm looking for a code to use that when I change the location of one employee for one day, that employee's name gets changed on the second sheet where all the locations are broken down. For example, Sheet one had all employees down the first row and then the days of the week across the columns with an assignment each day. Sheet 2 will have each assignment and what employee is doing that. I want that second sheet to be populated based on what I put on the first, and then change as I make changes....

See More: XL: Copy and Paste when change occurs in row

Report •


✔ Best Answer
March 18, 2015 at 16:47:13
I've uploaded a version of the workbook that I think will work for you. I've included a number of notes within the workbook to help explain how it works. It would be too confusing to try to explain it all in a post.

The main solution is based on a technique used to "eliminate" duplicates by creating unique values so that functions like MATCH and other lookup functions can find the 2nd, 3rd, etc. occurrence of value.

By dragging this formula down a Helper Column...

=A1&COUNTIF($A$1:A1,A1)

You can turn this...

        A             
1      Tom
2      Tom
3      Tom

...into this:

        A        B          
1      Tom      Tom1
2      Tom      Tom2
3      Tom      Tom3

You can then use various lookup functions on Column B, since Column B now contains unique values. That should solve this issue:

"Right now the formula populates each cell with the first person it comes to with that assignment."

You will see that this technique was incorporated into your workbook on both sheets to create MATCHing values for the MATCH function to use.

Try the version found here:

http://www31.zippyshare.com/v/2I03q...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 15, 2015 at 19:15:20
I'm sorry...employees are down the first column, not row. And in case it wasn't apparent, it's in excel.

Report •

#2
March 16, 2015 at 08:45:05
It's not clear to me what you mean by "...that employee's name gets changed..."

I assume that you don't really want to change an employee's name...that would require some sort of legal action. ;-)

You gave us a general idea of what Sheet 1 looks like....

     A        B       C       D       E      F   
1   Name     Mon    Tues     Wed     Thu     Fri
2   Joe       X       Y       Z       W       U
3   Tom       Y       X       W       U       Z
4   Sue       Z       W       U       X       Y 

But you haven't told us what Sheet 2 looks like or the process you use to make changes.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
March 16, 2015 at 11:48:39
I'm sorry...ok so yea..sheet one is what you have...employee name down first column and then their hours/location in each cell next to their names for each day (i.e. 6x Post B).

The second sheet can be whatever will make it work!! haha...I'm looking to use the first sheet as the work book and then I want the second sheet to show the locations and hours to see what needs to be filled in. For example...hours/locations down the first column with the days of the week across the tops like the first sheet? Then I want it to populate with the employee names based on what was filled in on sheet one

A B C D E F
1 Locations Mon Tues Wed Thu Fri
2 Post B Joe Tom Sue Bill Mike
3 6x Bill Sue Mike Joe Tom
4 14x
5 Post C
6 10x
7 16x


Report •

Related Solutions

#4
March 16, 2015 at 11:58:17
Please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
March 16, 2015 at 12:02:31
I also don't understand your Post B, 6x, etc. mainly because I don't know where those values are on Sheet 1. Do those values replace my X, Y & Z's?

Are 6x and Post B both locations, yet different locations?

Keep in mind that we can't see your spreadsheets from where we are sitting so you have to supply a lot of details so that we can understand what you are working with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 16, 2015 at 12:11:00
 

          A          B           C          D         E            F 

1      Locations    Mon       Tues         Wed      Thu           Fri

2     Post B        

3        6x         Bill        Sue      Mike       Joe         Tom

4       14x         Joe         Tom        Sue      Bill        Mike

5   Post C

6       10x

7       16x 

message edited by jhova16


Report •

#7
March 16, 2015 at 12:25:32
Yea sorry..new to this! ...so yes, replacing your xyz's with time and location for each employee...so the first sheet looks like this..


 

          A          B           C          D         E            F 

1    Locations     Mon         Tues         Wed      Thu           Fri

2     Bill     6x Post B     6x Post C   6x Post A 

3     Sue      14x Post C   16x Post A     

4     Mike         

5     Joe

So now I want each individual's assignment input on sheet 1 to be displayed on sheet 2 broken down by assignment and time. Also, when I change say Cell B2 from "6x Post B" to "6x Post C", I would like that change to change in the next sheet as well. Hope I'm being clear, it's hard to explain haha


Report •

#8
March 16, 2015 at 20:26:11
First, another posting tip...

Please don't add extra line spaces in your example data. It makes it harder for us to use your example data after we paste it into Excel since we have to delete all the extra rows before we can test anything.

OK, so we're starting with this in Sheet1...

 
       A          B             C            D        E      F
1    Names       Mon           Tues         Wed      Thu    Fri
2     Bill     6x Post B     6x Post C    6x Post A 
3     Sue     14x Post C    16x Post A     
4     Mike         
5     Joe

...and this in Sheet 2:

 
          A          B       C       D       E      F
1      Locations    Mon     Tues    Wed     Thu    Fri
2      Post B        
3        6x         
4       14x       
5      Post C
6       10x
7       16x 

The first thing I noticed is that we don't have any matching data to compare. In other words...

6x Post B

...does not match...

  Post B
    6x

So, the first thing we'll do is add a Helper Column to create matches. You can put the Helper Column anywhere you want, you can even hide it if you'd like. For the sake of this explanation, I'll put it in Column G.

 
          A          B       C       D       E      F      G
1      Locations    Mon     Tues    Wed     Thu    Fri
2      Post B        
3        6x                                              6x Post B      
4       14x                                             14x Post B   
5      Post C
6       10x                                             10x Post C
7       16x                                             16x Post C

In Sheet2!B3 enter the following formula and drag it across to Column F (Fri) and down to the bottom of your data (F7 in your example). Note, at this point you should get Names where matches exist and #N/A errors where they don't. We'll fix that later, but I want you to see the simple formula first, because that is what is doing all of the work. The "wrapper" we'll add later is just for cosmetics.

So, in Sheet2!B3, enter this:

=INDEX(Sheet1!$A$2:$A$5,MATCH($G3,Sheet1!B$2:B$5,0))

Here is how this is supposed to work:

The MATCH portion will try to find the values from Column G in the daily columns on Sheet1. If it finds a value, it will use the row number that gets returned as the row_num argument for the INDEX function. Keep in mind that the "row" that MATCH returns is not the spreadsheet Row, but the relative row of the array used in the formula. In other words, Row 2 is row 1 of the array B2:B5 since it is the first row in that array.

OK, assuming that worked, let's pretty it up a bit.

Replace the formula in Sheet2!B3 with the following. What it does is check to see if the MATCH formula returns a #N/A error and if it does, the IF returns an empty cell. If the MATCH formula finds a match, then the INDEX portion can do its thing.

=IF(ISNA(MATCH($G3,Sheet1!B$2:B$5,0)),"",
INDEX(Sheet1!$A$2:$A$5,MATCH($G3,Sheet1!B$2:B$5,0)))

Let us know if this works or if you have any other questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
March 17, 2015 at 09:31:11
First part worked!!

message edited by jhova16


Report •

#10
March 17, 2015 at 09:56:30
Ok everything looks good except for one thing (my fault I failed to mention). On Sheet 2, there can be multiple people with the same assignment. Right now the formula populates each cell with the first person it comes to with that assignment. So as you see below, Bill and Tom are both assigned to 6x Post B on the same day...sorry for the omission

 

          A          B           C          D         E            F 

1      Locations    Mon       Tues         Wed      Thu           Fri
2     Post B        
3        6x         Bill        Sue        Mike      Joe         Tom
4                   Tom        Mike        Bill
5       14x         Joe         Tom        Sue      Bill        Mike
6   Post C
7       10x
8
9       16x 


Report •

#11
March 17, 2015 at 12:25:22
You have just raised the stakes considerably. First off, multiple names are going to be a problem for any formula. A formula in any given cell has no idea what any other formula has done, so it has no way of knowing that some other formula has already found "Bill" and that it should ignore it.

In addition, you now show 2 rows for 6x and 10x. Can I assume that at some other time you might have 1 row for either of those and 2 rows for e.g. 14x? If ever-changing scenarios like that are possible, you can throw any formula method out of the window.

It's possible that a macro might work, but until you provide all of the details and possible scenarios, it would be a waste of my time to try writing any code. It may even turn out that without some consistency to the output you are looking for, even a macro solution might not exist.

As I said in an earlier response:

Keep in mind that we can't see your spreadsheets from where we are sitting so you have to supply a lot of details so that we can understand what you are working with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
March 17, 2015 at 13:03:57
There was absolutely no waste of your time as! So far, what you have provided is def. helpful.

So yea, basically every different location can have a different number of employees scheduled at different times. There may be one person 6x Post A on one day and then two people the next. There also may always be only one person 17x Post B and never any more. It doesn't really matter though if every tour has the same amount of rows assigned to it and some will just be blank, so be it. The first sheet will also have some cells throughout the work week with RDO in it to identify a day off as it is a two week schedule. It doesn't really matter if that doesn't carry over to the next sheet though because a blank cell will just be obvious as being as day off and its only a location schedule anyway so assignments outside of those locations is not relevant

Trying to throw everything in here.

I wish I can show you a copy of the spreadsheet because I'm trying to add everything in when I respond.

message edited by jhova16


Report •

#13
March 17, 2015 at 14:09:16
You should be able to upload the file to zippyshare.com and post the link back here. No registration required. Just make the workbook as generic as possible with no real names or other personally identifiable material.

zippyshare is used by members of computing.net as a recommended place to upload logs so that those who are trying clean up someone computer can see the logs. If the adware/virus cleansing experts feel that the site is safe, I trust them. YMMV

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
March 17, 2015 at 14:47:55
I'm restricted from that website here....so i'll do it later on from a different location. Thank you so much for your time here, its much appreciated!!

Report •

#15
March 17, 2015 at 17:29:13
Thank you again for your help.

Here is the link:

http://www9.zippyshare.com/v/6tikMj...


Report •

#16
March 17, 2015 at 19:10:02
re: There was absolutely no waste of your time as! So far, what you have provided is def. helpful.

I don't see how it was helpful...it doesn't do what you need it to do.

I am seriously confused about so many things...

The Schedule sheet contains a Worksheet_Change macro referencing a Named Range "Codes", yet I can't seem to find a Codes range in the workbook. When I look at the Name Manager, there are 4 Named Ranges that all have #REF! errors associated with them. What is going on there?

re: The first sheet will also have some cells throughout the work week with RDO in it to identify a day off as it is a two week schedule.

Your Data Validation list doesn't include a choice of RDO, so how are you putting RDO in any of those cells?

re: So yea, basically every different location can have a different number of employees scheduled at different times. There may be one person 6x Post A on one day and then two people the next. There also may always be only one person 17x Post B and never any more. .

Do the current number of rows for each location on the Post sheet mean anything? In other words are these always constant?

Up to 3 people at 6x Post B
Up to 2 people at 4x Post C
Up to 1 person at 17x Post B
Up to 1 person, 16x Post E through J
etc.

Why is there no "x" next to the 10 in Post A & B on the Post sheet?

Is there some significance to the fact that the "c" and "d" are lower case in your Schedule drop downs but not on the Post sheet?

Is there a reason there are locations(?) on the Post page that don't appear in the drop down? e.g. 10x Post C, 10x Post D, 10x Post J?

Are you aware that you have a couple of entries in Schedule!B108:B143 that have a trailing space? Many functions, including MATCH, will not find those values unless the lookup_value also have that trailing space.

Please explain these items.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#17
March 17, 2015 at 19:53:06
It may not do what I need to do, but the formula alone has taught me something new...but anyway....so...

I have no idea what the Macro is about. I inherited a template that I completely reworked in terms of aesthetics. Any Macro that is there is not needed or in use (as far as I know). The term Codes might be referring to what we are now calling locations that somebody built something from before.

I didnt fully add all non location in my data validation yet because I wanted to get the formula and everything working first.

The current number of rows for each location is the "guestimated" amount of people that could be assigned to each location. I dont have a definitive answer yet as to the count for each location/tour so I just put in a bunch. If it being uniform up to the max (lets say up to 4 people for each) makes it easy, then that'll work

the no x next to 10 is just a typo.

no significance to the fact that the "c" and "d" are lower case on one and not on the other.

There is no reason certain assignments don't appear on the drop down, that's a mistake. All the assignments on the Post sheet should be in the drop down.

I was not aware that there was a trailing space in some entries

I think I fixed everything other than the macro issue ..check the new version below..

http://www47.zippyshare.com/v/tl6sA...


Report •

#18
March 18, 2015 at 11:01:20
re: I think I fixed everything other than the macro issue ..check the new version below..

As far as I can ell, nothing was fixed. There are still trailing spaces after 12x Post I on the Schedule sheet, there are still missing 10x entries in Schedule!B108:B143, there are still lower case letters and the "x" is still missing on Post sheet for a couple of the 10's.

Maybe you uploaded the original workbook, not the one you fixed. In any case, I think I know what you are trying to do, so if those errors impact/prevent a solution, I'll let you know.

I'll work on something this afternoon, but I won't be able to upload it until this evening.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#19
March 18, 2015 at 14:11:33
Thank you! Its much appreciated!

Report •

#20
March 18, 2015 at 16:47:13
✔ Best Answer
I've uploaded a version of the workbook that I think will work for you. I've included a number of notes within the workbook to help explain how it works. It would be too confusing to try to explain it all in a post.

The main solution is based on a technique used to "eliminate" duplicates by creating unique values so that functions like MATCH and other lookup functions can find the 2nd, 3rd, etc. occurrence of value.

By dragging this formula down a Helper Column...

=A1&COUNTIF($A$1:A1,A1)

You can turn this...

        A             
1      Tom
2      Tom
3      Tom

...into this:

        A        B          
1      Tom      Tom1
2      Tom      Tom2
3      Tom      Tom3

You can then use various lookup functions on Column B, since Column B now contains unique values. That should solve this issue:

"Right now the formula populates each cell with the first person it comes to with that assignment."

You will see that this technique was incorporated into your workbook on both sheets to create MATCHing values for the MATCH function to use.

Try the version found here:

http://www31.zippyshare.com/v/2I03q...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#21
March 19, 2015 at 16:40:14
Oh man...this is outstanding. Been messing around with it all day and got everything just how you made it to work. A million thank you's are not enough!!!!

Report •

#22
March 19, 2015 at 20:04:23
No problem. Once I had all of the details, it was sort of fun.

I was introduced to the concept of Helper Columns many years ago and it really is a technique worth keeping in your back pocket. Sometimes it takes 2 or more intermediate steps to get the final result and Helper Columns give you a place to store those mid-stream results.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question