Help needed!

Topic

New Reply

I have an excel workbook that I need some help with. The spreadsheet contains payroll information for biweekly hourly employees and includes columns for name, hours, rate, etc… The system that I import this report from creates a row for each week that an employee works during the two week pay period. What I would like to do is add a column that looks at the name in the row above, and if it is the same as the name in the current row, adds the numbers in the hours column for the two rows. I have tried using If Then statements to look at the names, but I also want the cell in the total hours for week 1 to be blank if there is a week 2.

I have attached a sample of the imported data, with dummy names attached.

I’m sure there is a simple soultion that I am overlooking, but any help I can get would be appreciated

Reply | Quote

Replies

Hi Greg,

to avoid misunderstandings,
you should provide a solution displaying the expected results
(without the formula, just the results).

For example do you want “Doe, John” to be considered as the same name as “Doe, Jane” or not?
If not, I do not see any case allowing to sum hours regarding your constraints.
If yes, here is my small contribution:
Assuming that the name is the text string at the left of the comma in each cell of column A,
type the following formula in row 2 then copy down…
column H [ =FIND(“,”,A2) ] indicates the place of the comma
column I [ =LEFT(A2,H2-1) ] extracts the name (digits before the comma)
column J [ =IF(C2=2,”week 2″,IF(I1=I2,E1+E2,”name different”)) ] spells your constraints when the sum is not allowed or the expected sum when calculation is allowed

Of course, you may combine all these formulas in one.

Reply | Quote

Put the formula below in H2 and then fill it down the column. It should do what you are asking.

=IF(A2"",IF(A2=A1,E2+E1,IF(A2=A3,"",E2)),"")

Reply | Quote

Sorry Greg,

Looking at Legare solution, I realised I made a mistake displaying the sum in week 1 row when there was no week 2
instead of not displaying the sum in row week 1 when a week 2 exists!!

Apologizes

=IF(C3=2,”existing week 2″,IF(J1=J2,E1+E2,”name different”))
is the corrected formula, assuming the name is in column J.

As far as I understand, Legare formula also displays “the same row hours value” when the name is displayed once.

Reply | Quote

Sorry, I do not understand your message. I don’t understand why your formula (which row does this formula go in?). Why is it checking the value of C3 and displaying “existing week 2” if that value is 2. It also checks for J1=J2, but in the sheet you uploaded, there is nothing in column J.

My formula gives the hours from column E if the previous row is a different name, the sum of the hours if the previous row is the same, and leaves the cell blank if the next row has the same name (the sum will appear in the next row). Isn’t that what you wanted?

Reply | Quote

Hi Logare,
sorry if I’ve have been unclear and sorry also for my english,

– My formula is going in row 2 (the first row of data)

– Regarding the last constraint required by greg (gvanhook), i.e.: “I also want the cell in the total hours for week 1 to be blank if there is a week 2.”,
the formula looks the next row (3) checking if there is a week 2.
If C3=2, it means that there is a week 2, so I do’nt display result;
for testing purpose, I display the warning message “existing week 2”
In a final working formula, I would just display a blank result “”.

– The reference to column J is connected to my first post.
I extracted the true name in column J, as I wondered what was the name to check:
all the string in column A,
or
only the “last name” (just the first digits before the comma).

I hope this makes sense.
Thanks

Reply | Quote

Unfortunately, your check for C3=2 does not work. It will fail if the next row is for a different person but for week 2. I check to see if the name is different, which I think is all that is required. You would have to check both for a differnent name and week 2.

Although Greg never answered, I think that you have to check the whole name, not just the part after the comma. Doe, Jane and Doe, John are obviously different people and I see no reason why you would add their times together,

Reply | Quote

I think that we share the following two hypothesis:
– Week 2 data are always in the row immediately below week 1 row.
– It does not exist more than one week 1 and one week 2 for each name series.

I think we differ on the following one:
– I assumed that a week 2 could not exist without a week 1?

If the sample file is not truncated, row 2 proves that my hypothesis was wrong

In the case that a week 2 can exist without week 1,
you’re perfectly right as my formula becomes wrong,
when it goes in week 2 of an employee and that both,
– the row before, is the week 1 of this employee,
– the next row, is the week 2 of another one.
=IF(and(A2=A3,C3=2),”existing week 2″,IF(A1=A2,E1+E2,”name different”)) should fit.

Regarding the first Greg constraint,
“we have to display data only if the row above contains the same name”,
this constraint is not reflected in your formula as you can display the figure for a week without checking the name above.

But may be, you guessed right what Greg truly wanted, in contradiction to the constraint he spelt…

Reply | Quote

Did you try my formula? If you do, I think that you will find that it does, in fact, check the row above and the data as requested by Greg.

Reply | Quote

Your formula
=IF(A2″”,IF(A2=A1,E2+E1,IF(A2=A3,””,E2)),””)
– checks the row above (A1) to allow the display of the sum (E2+E1)
(which is required by Greg),
– but also displays the same row unique value (E2) even if the name above is different
(which is not specified by Greg) )

This display is an added feature to Greg’s requirement, but as I said, may be you guessed right what he truly wants.

In other words, when there is a week 2 without week 1,
Greg requirement “add numbers” can’t apply as the value is unique.
Your approach was to display this value.
My approach was to stick to the requirement and to show no value.

Reply | Quote

Thank you all for the help. Lagare’s formula worked for me, although when I cut and past it, it did strange things to the row height. I printed it out, and typed it in, and it worked perfectly.

Thanks, again.

I have a second issue with the raw data that I receive. The data also comes with a coulumn for account number that I did not include with the sample. Is there a way to have Excel insert 2 blank rows when the account number changes?

Thanks,

Reply | Quote

If the account numbers are in column A, then the following VBA code should do that:

Dim I As Long
    For I = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1 To 2 Step -1
        If Worksheets("Sheet1").Range("A1").Offset(I, 0)  "" And _
          Worksheets("Sheet1").Range("A1").Offset(I, 0)  Worksheets("Sheet1").Range("A1").Offset(I - 1, 0) Then
            Worksheets("Sheet1").Range("A1").Offset(I, 0).EntireRow.Insert (xlDown)
            Worksheets("Sheet1").Range("A1").Offset(I, 0).EntireRow.Insert (xlDown)
        End If
    Next I

Reply | Quote

Legare’s formula assumes that there isn’t ever a week 3, that is three rows where A1=A2=A3.

It would need to get more complex if there could be unlimited numbers of repeat rows.

StuartR

Reply | Quote