Solved Trying to copy a value from 1 column to anoth

November 15, 2011 at 19:32:06
Specs: Windows 7
In one column I have a value (employee number) listed once on a single row. I need that value relisted in another column for several rows until another employee number is listed on that original column. When another employee number is listed on the first column I will need that corresponding number relisted on multiple rows until another employee number is listed.

The catch is in this particular column that lists the employee number there are other values listed in other rows like text or asterisks. The criteria of the employee number is either 3 or 4 digits.


See More: Trying to copy a value from 1 column to anoth

Report •

#1
November 16, 2011 at 18:03:18
✔ Best Answer
Assuming that none of the values between Employee Numbers are numbers, this will replicate the Employee Numbers found in Column A and place them in Column B per your specifications:

Sub ReplicateEmployeeNum()
On Error Resume Next
'Determine length of data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through data
  For nxtRw = 1 To lastRw
'If Column A contains a number, count how many rows until the next number
    If IsNumeric(Cells(nxtRw, 1)) Then
     numRw = nxtRw
      Do Until IsNumeric(Cells(nxtRw + 1, 1))
       nxtRw = nxtRw + 1
       cnt = cnt + 1
      Loop
    End If
'Replicate the Employee Numbers based on the count of non-numeric cells
  For empNum = numRw To numRw + cnt
    Cells(empNum, 2) = Cells(numRw, 1)
  Next
'Reset row counter
   cnt = 0
 Next
End Sub

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


Report •

#2
November 17, 2011 at 19:19:51
The number of rows before the next "employee number" are not set. One employee may have 3 rows and the next may have 10 rows. The only way to distinguish each new set of rows is the common employee number (either 3 digits or 4 digits).
And, I'm sorry but I don't understand the form of your response in relation to an excel formula ("=countif(a:a,b:b,sheet1.." or "sumifs(a:a,b:b,sheet1..").
Can you help me simplify the solution?

Report •

#3
November 17, 2011 at 19:25:28
Sub ReplicateEmployeeNum()
On Error Resume Next
'Determine length of data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through data
  For nxtRw = 1 To lastRw
'If Column A contains a number, count how many rows until the next number
    If IsNumeric(Cells(nxtRw, 1)) Then
     numRw = nxtRw
      Do Until IsNumeric(Cells(nxtRw + 1, 1))
       nxtRw = nxtRw + 1
       cnt = cnt + 1
      Loop
    End If
'Replicate the Employee Numbers based on the count of non-numeric cells
  For empNum = numRw To numRw + cnt
    Cells(empNum, 2) = Cells(numRw, 1)
  Next
'Reset row counter
   cnt = 0
 Next
End Sub


Report •

Related Solutions

#4
November 18, 2011 at 00:45:15
If you didn't understand my response, why did you mark it as the "Best Answer"?

Why did you repost the code from my response?

re: "I don't understand the form of your response in relation to an excel formula"

What you are trying to do cannot be done with an Excel formula because there is no set number of rows between the Employee Numbers. There is no formula that can count the rows and determine how many times to repeat the employee number.

What I suggested is known as a "macro" which is a set of VBA (Visual Basic for Applications) instructions (code) that can be run against the data in your spreadsheet.

As per the comments embedded in the code, the macro will read the data in each cell in Column A and when it finds an Employee Number, it will then count how many cells below that number do not contain a Employee Number. It then copies that Employee Number the same number of times into Column B. It then searches for the next Employee Number, counts the non-Employee Number cells and copies that Employee Number into Column B. The code continues searching for Employee Numbers, counting rows and copying Employee Numbers until it reaches the bottom of the data in Column A.

You should end up with something like this, which is what I believe you are looking for:

	A	B
1	123	123
2	a	123
3	b	123
4	*	123
5	d	123
6	1111	1111
7	a	1111
8	456	456
9	a	456
10	b	456
11	c	456

To use the code, press Alt-F11 to open the VBA editor.
Paste the code I offered into the pane that opens and Run it.

I highly suggest that you try this in a backup copy of your workbook since macros can not be undone.

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


Report •

#5
November 18, 2011 at 17:12:50
I tested the macro and it worked perfectly. Thank you.
But is there a way to save the macro within the workbook so it will work continuously and automatic as I save new workbooks from the original, weekly? I'm not going to be the one using this data each week so I am trying to simplify the process as much as possible.

Report •

#6
November 18, 2011 at 18:09:41
The code should save with the workbook. Why do you think it isn't?

It won't run automatically, but it can be assigned to a button to make it easier to run.

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


Report •

#7
November 20, 2011 at 16:50:22
How do you assign it to a button? I think it is saved but a button would simplify enough.

Can you tell me what parts of that macro I need to change to change the reporting columns?

And, I'm having a hard time figuring out a sumif formula that works with text, ie match numbers in one column (a) with numbers in another column (b) and report back Text in another column (c). Can you help? The sumif formula that I use works perfect with all numbers but text won't report back.


Report •

#8
November 20, 2011 at 19:49:13
Formula is saved and I have assigned a button. Macro good to go!

Now I am trying to figure out the text "sumif" formula.
And, the next and final task for this spreadsheet is a "date search count" formula.

My current formula that "counts shifts" is =COUNTIFS('Aloha Import'!E:E,'ACIS Export'!B:B,'Aloha Import'!R:R,'ACIS Export'!A:A,'Aloha Import'!J:J,N193)

This formula works perfectly, however, now "counted shifts" must be restricted by a duplicate value in a column (Column D in "Aloha Import") which is the specified "date" column that lists dates for each shift (which is displayed on a row in "Aloha Import").

So basically I need to add an extra criteria for the countifs formula above that does not "count" if a specific column value is reported within an already specified column "range" (E:E and R:R).

Can anyone help?


Report •

#9
November 21, 2011 at 01:21:54
re: "Formula is saved and I have assigned a button"

The code I provided is not a formula. It is a macro. A formula is placed in an Excel cell, a macro is placed in the VBA editor.

re: "Now I am trying to figure out the text "sumif" formula"

If you have a question that is not related to the subject of this thread, please post your question in a new thread with a relevant subject line.

Posting unrelated questions in one thread makes for sloppy archives. In addition, those that are not following this thread because of the current subject line will not see your question and you'll lose out on their possible help.

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


Report •

Ask Question