Matching values & returning on the same row

I have a workbook with a worksheet of data. I tried to use a formula to
accomplish my goals, but it's not working exactly the way I wanted. I was
wondering if there is a macro that could do the work a little more
efficiently and user friendly.

On Worksheet1 ("Sheet1") I have data in A1:AI400. Columns A:Q is considered
one set of data and Columns S:AI is my second set of data. Column R is empty
as a spacer. There are dates in columns A & S. There is corresponding data
in each row to go with the date. See below:

"Sheet1"
Col A B C D : Q R (empty) S T U V :
AI
01Jan04 1 3 2 4 04Jan04 2 3 2 1
02Jan04 2 2 2 3 05Jan04 1 2 3 4
03Jan04 4 3 2 1 06Jan04 3 1 2
1
04Jan04 3 1 1 2 07Jan04 2 4 1 2

What I want to do is on "Sheet3" (I'm using Sheet2 for something else) look
at Sheet1 and each date in Column A, compare it to Column S. If it finds a
match in Column S, write the date and corresponding information for that date
on Sheet3.

So based on my example above and doing what I want, I should see on Row 3 is:

"Sheet3" (There are headers in Rows 1 & 2, the returned data should start
on Row 3)
Col A B C D : Q R (empty) S T U V :
AI
Header Row
1...................................................................................
Header Row
2...................................................................................
04Jan04 3 1 1 2 04Jan04 2 3 2 1

It is important that the dates that match return on the same row. I'm not
even sure what kind of macro would do this so I'm stumped at where to start.
Any input or code would be greatly appreciated.

Thanks,

Sharon

p.s. Here's the formula I was using, but it doesn't return the dates on the
same rows in Sheet3 and it still returns all the data, not just the matching
ones. Based on my example above, the following formula would return my first
set of data on row 4 and my second set of data on row 1.

I have headers in rows 2 & 3 on Sheet3.

"Sheet3"
Col A B C D : Q R (empty) S T U V :
AI
Header Row
1...................................................................................
Header Row
2...................................................................................
1 3 2 4 04Jan04 2 3
2 1
2 2 2 3
1 2 3 4
4 3 2 1
3 1 2 1
04Jan04 3 1 1 2 2 4
1 2

For example: the formula in cell A3 is:
=IF(ISNA(VLOOKUP(Sheet1!A1,Sheet1!$S$1:$AI$400,1,FALSE)),"",VLOOKUP(Sheet1!A1,Sheet1!$S$1:$AI$400,1,FALSE))

Here is a procedure that does what you asked... I Hope... Give it a try...

Public Sub CopyRows()
Dim wksToSearch As Worksheet
Dim wksToPaste As Worksheet
Dim rngPaste As Range
Dim rngToCheck As Range
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngFound As Range

Set wksToSearch = Sheets("Sheet1")
Set wksToPaste = Sheets("Sheet3")
Set rngPaste = wksToPaste.Range("A2")
Set rngToCheck = Range(wksToSearch.Range("A65535").End(xlUp),
wksToSearch.Range("A2"))
Set rngToSearch = Range(wksToSearch.Range("S65535").End(xlUp),
wksToSearch.Range("S2"))

For Each rngCurrent In rngToCheck
Set rngFound = rngToSearch.Find(rngCurrent.Value, , xlFormulas)
If Not rngFound Is Nothing Then
Range(rngCurrent, rngCurrent.Offset(0, 16)).Copy rngPaste
Range(rngFound, rngFound.Offset(0, 16)).Copy rngPaste.Offset(0,
18)
Set rngPaste = rngPaste.Offset(1, 0)

End If
Next rngCurrent

End Sub

HTH

"Sharon" wrote:

> I have a workbook with a worksheet of data. I tried to use a formula to
> accomplish my goals, but it's not working exactly the way I wanted. I was
> wondering if there is a macro that could do the work a little more
> efficiently and user friendly.
>
> On Worksheet1 ("Sheet1") I have data in A1:AI400. Columns A:Q is considered
> one set of data and Columns S:AI is my second set of data. Column R is empty
> as a spacer. There are dates in columns A & S. There is corresponding data
> in each row to go with the date. See below:
>
> "Sheet1"
> Col A B C D : Q R (empty) S T U V :
> AI
> 01Jan04 1 3 2 4 04Jan04 2 3 2 1
> 02Jan04 2 2 2 3 05Jan04 1 2 3 4
> 03Jan04 4 3 2 1 06Jan04 3 1 2
> 1
> 04Jan04 3 1 1 2 07Jan04 2 4 1 2
>
> What I want to do is on "Sheet3" (I'm using Sheet2 for something else) look
> at Sheet1 and each date in Column A, compare it to Column S. If it finds a
> match in Column S, write the date and corresponding information for that date
> on Sheet3.
>
> So based on my example above and doing what I want, I should see on Row 3 is:
>
> "Sheet3" (There are headers in Rows 1 & 2, the returned data should start
> on Row 3)
> Col A B C D : Q R (empty) S T U V :
> AI
> Header Row
> 1...................................................................................
> Header Row
> 2...................................................................................
> 04Jan04 3 1 1 2 04Jan04 2 3 2 1
>
> It is important that the dates that match return on the same row. I'm not
> even sure what kind of macro would do this so I'm stumped at where to start.
> Any input or code would be greatly appreciated.
>
> Thanks,
>
> Sharon
>
> p.s. Here's the formula I was using, but it doesn't return the dates on the
> same rows in Sheet3 and it still returns all the data, not just the matching
> ones. Based on my example above, the following formula would return my first
> set of data on row 4 and my second set of data on row 1.
>
> I have headers in rows 2 & 3 on Sheet3.
>
> "Sheet3"
> Col A B C D : Q R (empty) S T U V :
> AI
> Header Row
> 1...................................................................................
> Header Row
> 2...................................................................................
> 1 3 2 4 04Jan04 2 3
> 2 1
> 2 2 2 3
> 1 2 3 4
> 4 3 2 1
> 3 1 2 1
> 04Jan04 3 1 1 2 2 4
> 1 2
>
> For example: the formula in cell A3 is:
> =IF(ISNA(VLOOKUP(Sheet1!A1,Sheet1!$S$1:$AI$400,1,FALSE)),"",VLOOKUP(Sheet1!A1,Sheet1!$S$1:$AI$400,1,FALSE))

So far as I can tell, this works. I just made a minor change to return the
pasted data on Row 3.

Thank you so much!

Sharon

"Jim Thomlinson" wrote:

> Here is a procedure that does what you asked... I Hope... Give it a try...
>
> Public Sub CopyRows()
> Dim wksToSearch As Worksheet
> Dim wksToPaste As Worksheet
> Dim rngPaste As Range
> Dim rngToCheck As Range
> Dim rngToSearch As Range
> Dim rngCurrent As Range
> Dim rngFound As Range
>
> Set wksToSearch = Sheets("Sheet1")
> Set wksToPaste = Sheets("Sheet3")
> Set rngPaste = wksToPaste.Range("A2")
> Set rngToCheck = Range(wksToSearch.Range("A65535").End(xlUp),
> wksToSearch.Range("A2"))
> Set rngToSearch = Range(wksToSearch.Range("S65535").End(xlUp),
> wksToSearch.Range("S2"))
>
> For Each rngCurrent In rngToCheck
> Set rngFound = rngToSearch.Find(rngCurrent.Value, , xlFormulas)
> If Not rngFound Is Nothing Then
> Range(rngCurrent, rngCurrent.Offset(0, 16)).Copy rngPaste
> Range(rngFound, rngFound.Offset(0, 16)).Copy rngPaste.Offset(0,
> 18)
> Set rngPaste = rngPaste.Offset(1, 0)
>
> End If
> Next rngCurrent
>
> End Sub
>
> HTH
>
> "Sharon" wrote:
>
> > I have a workbook with a worksheet of data. I tried to use a formula to
> > accomplish my goals, but it's not working exactly the way I wanted. I was
> > wondering if there is a macro that could do the work a little more
> > efficiently and user friendly.
> >
> > On Worksheet1 ("Sheet1") I have data in A1:AI400. Columns A:Q is considered
> > one set of data and Columns S:AI is my second set of data. Column R is empty
> > as a spacer. There are dates in columns A & S. There is corresponding data
> > in each row to go with the date. See below:
> >
> > "Sheet1"
> > Col A B C D : Q R (empty) S T U V :
> > AI
> > 01Jan04 1 3 2 4 04Jan04 2 3 2 1
> > 02Jan04 2 2 2 3 05Jan04 1 2 3 4
> > 03Jan04 4 3 2 1 06Jan04 3 1 2
> > 1
> > 04Jan04 3 1 1 2 07Jan04 2 4 1 2
> >
> > What I want to do is on "Sheet3" (I'm using Sheet2 for something else) look
> > at Sheet1 and each date in Column A, compare it to Column S. If it finds a
> > match in Column S, write the date and corresponding information for that date
> > on Sheet3.
> >
> > So based on my example above and doing what I want, I should see on Row 3 is:
> >
> > "Sheet3" (There are headers in Rows 1 & 2, the returned data should start
> > on Row 3)
> > Col A B C D : Q R (empty) S T U V :
> > AI
> > Header Row
> > 1...................................................................................
> > Header Row
> > 2...................................................................................
> > 04Jan04 3 1 1 2 04Jan04 2 3 2 1
> >
> > It is important that the dates that match return on the same row. I'm not
> > even sure what kind of macro would do this so I'm stumped at where to start.
> > Any input or code would be greatly appreciated.
> >
> > Thanks,
> >
> > Sharon
> >
> > p.s. Here's the formula I was using, but it doesn't return the dates on the
> > same rows in Sheet3 and it still returns all the data, not just the matching
> > ones. Based on my example above, the following formula would return my first
> > set of data on row 4 and my second set of data on row 1.
> >
> > I have headers in rows 2 & 3 on Sheet3.
> >
> > "Sheet3"
> > Col A B C D : Q R (empty) S T U V :
> > AI
> > Header Row
> > 1...................................................................................
> > Header Row
> > 2...................................................................................
> > 1 3 2 4 04Jan04 2 3
> > 2 1
> > 2 2 2 3
> > 1 2 3 4
> > 4 3 2 1
> > 3 1 2 1
> > 04Jan04 3 1 1 2 2 4
> > 1 2
> >
> > For example: the formula in cell A3 is:
> > =IF(ISNA(VLOOKUP(Sheet1!A1,Sheet1!$S$1:$AI$400,1,FALSE)),"",VLOOKUP(Sheet1!A1,Sheet1!$S$1:$AI$400,1,FALSE))