Hello, and hopefully this thread will post, as I have tried 3x to ask this question and the forum will not post my question.

Basically what I have is a google form asking for a name, John Doe, and 5 groups of questions in which it dumps the input into a sheet. This sheet is called "Main".

John Doe Question 1 Answer Question 2 Answer etc.

I would like to know how I can have the sheet search column A for "John Doe" and then when it finds it, copy the row associated with John Doe to another sheet called "John Doe". I tried the
=IF(Main!A:A="John Doe","John Doe","") and it returns nothing.

Could I use a macro of some sort to look at column A search for a specific name and then have it copy the row it found the name on and then paste it to another sheet? I did have some success, however, it pasted to the same row it found John Doe. For example, if john doe is in row 1,5,8,20 on the new sheet it pasted john doe in 1,5,8,20 and left all other blank. I would like it to paste in sequential rows on the new sheet.

I realize I am asking a lot and I thank anyone who can help point me in the right direction. I think i have bit off more than I can chew, but I like doing this so all help is needed and very much appreciated!!!

Patrick,

I think that the macro below will do the trick with one small change to your sample workbook. The change I made was to put all the headers in row 1 and start the data in row 2 of the Main tab. With these changes, the following code should work out.

Ben

Sub CopyName()
Dim wsMain As Worksheet 'Main Worksheet
Dim wsFound As Worksheet 'John Doe Worksheet
Dim wsOther As Worksheet 'Not John Worksheet
Dim strName As String 'Name to search

Set wsMain = Sheet1 'Sheets ("Main")
Set wsFound = Sheet2 'Sheets ("John Doe")
Set wsOther = Sheet3 'Sheets ("Not John")

strName = "John Doe"
strName = InputBox("Please enter a name", "Name?", strName)

'Inserts criteria on Main sheet, then uses advanced filter to populate
'other sheets. Afterwards, criteria row is deleted.
'Assumes all headers started in row 1 and data starts in row 2
With wsMain
.Range("1:4").Insert
.Range("A1").Value = .Range("A5").Value
.Range("A2").Value = strName
wsFound.Range("A1:E1").Value = .Range("A5:E5").Value
wsOther.Range("A1").Value = .Range("A5").Value
.Range("A5").CurrentRegion.AdvancedFilter xlFilterCopy, .Range("A1:A2"), _
wsFound.Range("A1:E1"), False
.Range("A2").Value = "'<" & strName
.Range("A5").CurrentRegion.AdvancedFilter xlFilterCopy, .Range("A1:A2"), _
wsOther.Range("A1"), False
.Range("1:4").Delete
End With

Set wsMain = Nothing
Set wsFound = Nothing
Set wsOther = Nothing

End Sub

Apparently my knowledge on macros is nil.

I am sure it works for someone with some macro knowledge. I however found a formula that does what I want it to do except it doubles each entry which is annoying.

The formula is

=vlookup($A$1,Main!$Ax:$E$8,COLUMN(),FALSE)

$A$1 is the A1 cell where i have the name I want to have posted to the sheet
Main is the main book with raw data
$Ax is the A Column x number of rows I select to search
$E$8 is the length and width of data i want to be copied
COLUMN I have no idea what this is for
FALSE searches for John Doe only.

Attached is the new xls sheet with three different ways of putting "$Ax:$E$8" trying to have it copy the row ONCE. and you'll see what i mean by it copying it down twice. thank you again for your help!

I forgot to mention, I used the drag option to fill in the formula to all the cells. Would this play a part? Would i have to individually create the formula in each cell?


THANK YOU!

On Friday, December 21, 2012 7:32:03 PM UTC-8, Patrick Johnson wrote:
Ben McClave;1608161 Wrote:

Patrick,



I think that the macro below will do the trick with one small change to

your sample workbook. The change I made was to put all the headers in

row 1 and start the data in row 2 of the Main tab. With these changes,

the following code should work out.



Ben



Sub CopyName()

Dim wsMain As Worksheet 'Main Worksheet

Dim wsFound As Worksheet 'John Doe Worksheet

Dim wsOther As Worksheet 'Not John Worksheet

Dim strName As String 'Name to search



Set wsMain = Sheet1 'Sheets ("Main")

Set wsFound = Sheet2 'Sheets ("John Doe")

Set wsOther = Sheet3 'Sheets ("Not John")



strName = "John Doe"

strName = InputBox("Please enter a name", "Name?", strName)



'Inserts criteria on Main sheet, then uses advanced filter to populate

'other sheets. Afterwards, criteria row is deleted.

'Assumes all headers started in row 1 and data starts in row 2

With wsMain

.Range("1:4").Insert

.Range("A1").Value = .Range("A5").Value

.Range("A2").Value = strName

wsFound.Range("A1:E1").Value = .Range("A5:E5").Value

wsOther.Range("A1").Value = .Range("A5").Value

.Range("A5").CurrentRegion.AdvancedFilter xlFilterCopy,

.Range("A1:A2"), _

wsFound.Range("A1:E1"), False

.Range("A2").Value = "'<" & strName

.Range("A5").CurrentRegion.AdvancedFilter xlFilterCopy,

.Range("A1:A2"), _

wsOther.Range("A1"), False

.Range("1:4").Delete

End With



Set wsMain = Nothing

Set wsFound = Nothing

Set wsOther = Nothing



End Sub



Apparently my knowledge on macros is nil.



I am sure it works for someone with some macro knowledge. I however

found a formula that does what I want it to do except it doubles each

entry which is annoying.



The formula is



=vlookup($A$1,Main!$Ax:$E$8,COLUMN(),FALSE)



$A$1 is the A1 cell where i have the name I want to have posted to the

sheet

Main is the main book with raw data

$Ax is the A Column x number of rows I select to search

$E$8 is the length and width of data i want to be copied

COLUMN I have no idea what this is for

FALSE searches for John Doe only.



Attached is the new xls sheet with three different ways of putting

"$Ax:$E$8" trying to have it copy the row ONCE. and you'll see what i

mean by it copying it down twice. thank you again for your help!





+-------------------------------------------------------------------+

|Filename: Book2.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=713|

+-------------------------------------------------------------------+







--

Patrick Johnson

Hi Patrick,
I'd be suprised if Ben's code doesn't do what you want. However, I put this code in the worksheet Main vb editor. I selected A3:E8 on worksheet Main and in the name box named it DataA. Next I entered John Doe in A1 of worksheet Main. I assigned the sub to a button on worksheet Main. Click the button and the following "John Doe info" is transfered to F2:J4 on John Doe worksheet.

Good luck.

Regards,
Howard

Patrick,

To do this without a macro you could use an Index/Match function rather than a vlookup. To get the John Doe entries on your John Doe tab, enter this formula in cell A2 (it must be entered as an array formula via CTRL+SHIFT+ENTER):

=INDEX(Main!$A$1:$E$100,SMALL(IF(Main!$A$1:$A$100= $A$1,ROW(Main!$A$1:$A$100)),ROW()-1), COLUMN())

Then, copy it over and down.

For the page of non-John Doe lines, you could adapt the formula to:

=INDEX(Main!$A$1:$E$100,SMALL(IF(Main!$A$1:$A$100< $A$1,ROW(Main!$A$1:$A$100)),ROW()-1), 1)

Both of these formulas assume that "John Doe" is located in cell $A$1.

Using formulas instead of macros presents a new set of issues, though. First, the array formulas can really slow down your workbook if the ranges are too big. For example, using "Main!$A:$E" rather than limiting it to 100 rows or so will consume a lot of resources. Also, using formulas like this could cause problems as the data set expands. If you don't copy the formula down the page enough times, you may not capture every instance of the "John Doe" data.

Best of luck,

Ben