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.

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.

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?

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

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

notcontain 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 copiesthatEmployee 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 456To 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.

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.

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.

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.

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?

re: "Formula is saved and I have assigned a button"The code I provided is

nota 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.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History