Auto Fill

[Dedrie]

I read through all the posts, but couldn't find a solution that worked for
this.

I have a report that's pulled out of another system and converted into XL.
The column - A - "EMPLOYEE ID" is what I need to be able to sort on however
the report doesn't list it for every row associated with that employee, just
the first row that is associated. Is there a way by using macro or perhaps
vlookup or an if statement to automatically fill in the employee ID number
until it encounters the next non blank line?

The spreadsheet looks like:

A B C D
Employee ID Deduction Type Deduction Amount Total YTD
1001 BMH $12 $48
CDP $10 $50
ABC $100 $200
XYZ $5 $5
2001 BMH $12 $48
CDP $10 $50
ABC $100 $200
XYZ $5 $50
3001 BMH $12 $48
CDP $10 $50
ABC $100 $200
XYZ $5 $50

So ideally it would fill in 1001 in column A until it gets to 2001 and then
would fill in 2001 until it gets to 3001 and so on.

Thanks in advance for the assistance!!

[JAdamJ]

go to column E and enter in row 2 (assuming that's where the first employee
begins)
=a2
then in row 3 enter
=if(isblank(a3),e2,a3)
and copy this formula down.
then you can paste the results as values back into column A

"Dedrie" wrote:

> I read through all the posts, but couldn't find a solution that worked for
> this.
>
> I have a report that's pulled out of another system and converted into XL.
> The column - A - "EMPLOYEE ID" is what I need to be able to sort on however
> the report doesn't list it for every row associated with that employee, just
> the first row that is associated. Is there a way by using macro or perhaps
> vlookup or an if statement to automatically fill in the employee ID number
> until it encounters the next non blank line?
>
> The spreadsheet looks like:
>
> A B C D
> Employee ID Deduction Type Deduction Amount Total YTD
> 1001 BMH $12 $48
> CDP $10 $50
> ABC $100 $200
> XYZ $5 $5
> 2001 BMH $12 $48
> CDP $10 $50
> ABC $100 $200
> XYZ $5 $50
> 3001 BMH $12 $48
> CDP $10 $50
> ABC $100 $200
> XYZ $5 $50
>
> So ideally it would fill in 1001 in column A until it gets to 2001 and then
> would fill in 2001 until it gets to 3001 and so on.
>
> Thanks in advance for the assistance!!

[Dedrie]

Thanks -- that worked.

I have a similar report, but instead of leaving blanks it lists the employee
name under the employee number such as:

Employee ID Deduction Type Deduction Amount Total YTD
> > 1001 BMH $12 $48
> > James CDP $10 $50
> > ABC $100 $200
> > XYZ $5 $5
> > 2001 BMH $12 $48
> > John CDP $10 $50
> > ABC $100 $200
> > XYZ $5 $50
> > 3001 BMH $12 $48
> > Sue CDP $10 $50
> > ABC $100 $200
> > XYZ $5 $50

Do you know how I could modify if statement you provided to fill in it as
well but in a different column.

I tried using the statement you provided but because the row below is
non-blank I can't figure it out.

Thanks again!

"JAdamJ" wrote:

> go to column E and enter in row 2 (assuming that's where the first employee
> begins)
> =a2
> then in row 3 enter
> =if(isblank(a3),e2,a3)
> and copy this formula down.
> then you can paste the results as values back into column A
>
> "Dedrie" wrote:
>
> > I read through all the posts, but couldn't find a solution that worked for
> > this.
> >
> > I have a report that's pulled out of another system and converted into XL.
> > The column - A - "EMPLOYEE ID" is what I need to be able to sort on however
> > the report doesn't list it for every row associated with that employee, just
> > the first row that is associated. Is there a way by using macro or perhaps
> > vlookup or an if statement to automatically fill in the employee ID number
> > until it encounters the next non blank line?
> >
> > The spreadsheet looks like:
> >
> > A B C D
> > Employee ID Deduction Type Deduction Amount Total YTD
> > 1001 BMH $12 $48
> > CDP $10 $50
> > ABC $100 $200
> > XYZ $5 $5
> > 2001 BMH $12 $48
> > CDP $10 $50
> > ABC $100 $200
> > XYZ $5 $50
> > 3001 BMH $12 $48
> > CDP $10 $50
> > ABC $100 $200
> > XYZ $5 $50
> >
> > So ideally it would fill in 1001 in column A until it gets to 2001 and then
> > would fill in 2001 until it gets to 3001 and so on.
> >
> > Thanks in advance for the assistance!!

[JAdamJ]

I don't have time to go into the exact details right now, but if the employee
ID is truely a number (not text) then you could similarly use the
isnumber(value) function and refer to the cell below the number for the name.
Let me know if you still need help and I can be more specific later.

"Dedrie" wrote:

> Thanks -- that worked.
>
> I have a similar report, but instead of leaving blanks it lists the employee
> name under the employee number such as:
>
> Employee ID Deduction Type Deduction Amount Total YTD
> > > 1001 BMH $12 $48
> > > James CDP $10 $50
> > > ABC $100 $200
> > > XYZ $5 $5
> > > 2001 BMH $12 $48
> > > John CDP $10 $50
> > > ABC $100 $200
> > > XYZ $5 $50
> > > 3001 BMH $12 $48
> > > Sue CDP $10 $50
> > > ABC $100 $200
> > > XYZ $5 $50
>
> Do you know how I could modify if statement you provided to fill in it as
> well but in a different column.
>
> I tried using the statement you provided but because the row below is
> non-blank I can't figure it out.
>
> Thanks again!
>
> "JAdamJ" wrote:
>
> > go to column E and enter in row 2 (assuming that's where the first employee
> > begins)
> > =a2
> > then in row 3 enter
> > =if(isblank(a3),e2,a3)
> > and copy this formula down.
> > then you can paste the results as values back into column A
> >
> > "Dedrie" wrote:
> >
> > > I read through all the posts, but couldn't find a solution that worked for
> > > this.
> > >
> > > I have a report that's pulled out of another system and converted into XL.
> > > The column - A - "EMPLOYEE ID" is what I need to be able to sort on however
> > > the report doesn't list it for every row associated with that employee, just
> > > the first row that is associated. Is there a way by using macro or perhaps
> > > vlookup or an if statement to automatically fill in the employee ID number
> > > until it encounters the next non blank line?
> > >
> > > The spreadsheet looks like:
> > >
> > > A B C D
> > > Employee ID Deduction Type Deduction Amount Total YTD
> > > 1001 BMH $12 $48
> > > CDP $10 $50
> > > ABC $100 $200
> > > XYZ $5 $5
> > > 2001 BMH $12 $48
> > > CDP $10 $50
> > > ABC $100 $200
> > > XYZ $5 $50
> > > 3001 BMH $12 $48
> > > CDP $10 $50
> > > ABC $100 $200
> > > XYZ $5 $50
> > >
> > > So ideally it would fill in 1001 in column A until it gets to 2001 and then
> > > would fill in 2001 until it gets to 3001 and so on.
> > >
> > > Thanks in advance for the assistance!!