Find data and copy and paste

[DavidH56]

Hi,

I would like help locating the Code listed in column B (example of
code.."BG47")from the olddata sheet, copy the date located in column F and
paste it into the currentdata sheet into column J where the same Code from
column B from olddata sheet is found in column B of the currentdata sheet
keeping the same date format. I'd then like to have a formula put into
column K2 to last row showinf the difference in days between column J and F.
Finally I'd like to bold rows on currentdata sheet that have column B data
ends with numbers 50 or 00 and sort ascending 1st by column E dates if it
exists and 2nd by column D which always exists.

Thanks so much in advance.

 

[Bernie Deitrick]

I would like help locating the Code listed in column B (example of

code.."BG47")from the olddata sheet, copy the date located in column F and
paste it into the currentdata sheet into column J where the same Code from
column B from olddata sheet is found in column B of the currentdata sheet
keeping the same date format.

In column J, use a formula like

=VLOOKUP(B2,'OldData Sheet'!B:F,5,False)

and format that cell as a date. Then copy the formula down to match your data in column B.

I'd then like to have a formula put into
column K2 to last row showinf the difference in days between column J and F.

In K2, use the formula

=ABS(J2-F2)

and copy down to match your data.

Finally I'd like to bold rows on currentdata sheet that have column B data
ends with numbers 50 or 00

Select the cells that you want to format that way, then use Conditional Formatting with the custom
formula

=OR(RIGHT($B2,2)="00",RIGHT($B2,2)="50")

and sort ascending 1st by column E dates if it
exists and 2nd by column D which always exists.

Select the table and do the sort.


HTH,
Bernie
MS Excel MVP

 

[DavidH56]

Thanks for your response Bernie,
I'd tried the formula and copying down with the fill handle, but only one
date showed in all cells. Also I'd like to get these in vba if possible.

Thanks again.
--
By persisting in your path, though you forfeit the little, you gain the
great.

I would like help locating the Code listed in column B (example of
code.."BG47")from the olddata sheet, copy the date located in column F and
paste it into the currentdata sheet into column J where the same Code from
column B from olddata sheet is found in column B of the currentdata sheet
keeping the same date format.

In column J, use a formula like

=VLOOKUP(B2,'OldData Sheet'!B:F,5,False)

and format that cell as a date. Then copy the formula down to match your data in column B.

I'd then like to have a formula put into
column K2 to last row showinf the difference in days between column J and F.

In K2, use the formula

=ABS(J2-F2)

and copy down to match your data.

Finally I'd like to bold rows on currentdata sheet that have column B data
ends with numbers 50 or 00

Select the cells that you want to format that way, then use Conditional Formatting with the custom
formula

=OR(RIGHT($B2,2)="00",RIGHT($B2,2)="50")

and sort ascending 1st by column E dates if it
exists and 2nd by column D which always exists.

Select the table and do the sort.


HTH,
Bernie
MS Excel MVP