Find and dates data across sheets

[Computerised10]

Hi all,

i have a workbook which consists of 2 sheets. in each sheet i have a unique ID and the date next to it such as

sheet 1

98531	11/10/2014
86521b	8/8/2014
66363d	1/1/2014

<tbody>
</tbody>

sheet 2

86521b	10/8/2014
98531	15/10/2014
66363d	6/1/2014

<tbody>
</tbody>

what i need to do is first find the data from sheet 2 which matches sheet 1. then minus sheet two date from sheet 1 to give me the amount of days difference.

any help will be much appreciated

 

[MARK858]

Possibly try something like the code below (assumes that your id's are in column A and dates are in column B on both sheets)

Sub XXX()
    Dim c As Range, y As Long

    With Sheets("Sheet2")

        For Each c In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
            y = Sheets("Sheet1").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet1").Cells(1, 1), LookIn:=xlFormulas, _
                                                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)

            c.Offset(, 2) = CLng(y) - CLng(c.Offset(, 1))

        Next
    End With

End Sub

 

[Computerised10]

Hi

y = Sheets("Sheet1").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet1").Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)

comes back with an error.

 

[MARK858]

"An error" doesn't mean anything to me. What does the error actually state exactly.

 

[Computerised10]

Hey Mark

it would help if i put some data in there. worked a treat. thanks for the help mate

 

[MARK858]

it would help if i put some data in there

Yep, might help :wink:
Happy you got there.

 

[Computerised10]

hey

i thought if i had the basic formula i would be able to apply it to sheet3 and sheet4, but i cant .

so what i need done is the same thing but to sheet3 and sheet2 but output data to column d as sheet 1 outputs to column c. i hope that makes sense.

a	b	c	d
8554c	11/10/14	7	data from sheet3 here
8666d	12/6/14	6	data from sheet3 here <tbody> </tbody>

<tbody>
</tbody>

 

[MARK858]

OK, lets try and get this clear.
Are you subtracting column B Sheet3 from column B Sheet2? and you are putting the output to what sheet in column D?

Basically fill in the question marks below.

subtracting Sheet? column? from Sheet? column?, output to Sheet? column?

 

[Computerised10]

ok its a sequence.

first i subtract columnB Sheet 2 from columnB Sheet 1 out put to columnC sheet 1
second i subtract ColumnB sheet 3 from columnb sheet 2 out put to columnD sheet 1
third i subtract columnB Sheet 4 from CoumnB sheet3 out put to columnE sheet 1

 

[MARK858]

Untested...

Sub xxx2()
    Dim c As Range, y As Long, x As Long

    With Sheets("Sheet1")

        For Each c In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
            y = Sheets("Sheet2").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet2").Cells(1, 1), LookIn:=xlFormulas, _
                                                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)

            c.Offset(, 2) = CLng(y) - CLng(c.Offset(, 1))

            y = Sheets("Sheet3").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet3").Cells(1, 1), LookIn:=xlFormulas, _
                                                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)

            x = Sheets("Sheet2").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet2").Cells(1, 1), LookIn:=xlFormulas, _
                                                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
            c.Offset(, 3) = CLng(x) - CLng(y)

            y = Sheets("Sheet4").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet4").Cells(1, 1), LookIn:=xlFormulas, _
                                                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)

            x = Sheets("Sheet3").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet3").Cells(1, 1), LookIn:=xlFormulas, _
                                                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
            c.Offset(, 4) = CLng(x) - CLng(y)

        Next

    End With

End Sub