Searching for specific name on main sheet then copying row data associated with name

[Patrick Johnson]

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

Basically what I have is a google form asking for a name, John Doe, an
5 groups of questions in which it dumps the input into a sheet. Thi
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 "Joh
Doe" and then when it finds it, copy the row associated with John Doe t
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
specific name and then have it copy the row it found the name on an
then paste it to another sheet? I did have some success, however, i
pasted to the same row it found John Doe. For example, if john doe i
in row 1,5,8,20 on the new sheet it pasted john doe in 1,5,8,20 an
left all other blank. I would like it to paste in sequential rows o
the new sheet.

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

+-------------------------------------------------------------------
|Filename: Book2.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=711
+-------------------------------------------------------------------

 

[Ben McClave]

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

 

[Patrick Johnson]

Patrick,

I think that the macro below will do the trick with one small change t
your sample workbook. The change I made was to put all the headers i
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 howeve
found a formula that does what I want it to do except it doubles eac
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 th
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 puttin
"$Ax:$E$8" trying to have it copy the row ONCE. and you'll see what
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
+-------------------------------------------------------------------

 

[Howard]

Ben McClave;1608161 Wrote:





























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|

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

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 Mainand 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.

John Doe	 100	1000	200	300
John Doe 300	3000	400	500
John Doe	 500	5000	600	700

Option Explicit

Sub DataA()
Dim i As Variant
Dim DataA As Range
Dim c As Range
Application.ScreenUpdating = False
i = Range("A1").Value
For Each c In Range("DataA")
If c.Value = i Then
c.Resize(1, 5).Copy
Sheets("John Doe").Range("F10").End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

Good luck.

Regards,
Howard

 

[Ben McClave]

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 aretoo 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

 

[Patrick Johnson]

Patrick,

To do this without a macro you could use an Index/Match function rathe
than a vlookup. To get the John Doe entries on your John Doe tab, ente
this formula in cell A2 (it must be entered as an array formula vi
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 th
ranges are too big. For example, using "Main!$A:$E" rather tha
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 no
capture every instance of the "John Doe" data.

Best of luck,

Ben

That formula worked great! Now my question is I filled my entire shee
with this but now I have 1,000 boxes with the #ref! error. Is there
way I can tweak the formula to have it just return a blank if there i
no input values?

Thank you

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

 

[Howard]

That formula worked great! Now my question is I filled my entire sheet

with this but now I have 1,000 boxes with the #ref! error. Is there a

way I can tweak the formula to have it just return a blank if there is

no input values?



Thank you!





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

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

Hi Patrick,

I'm sticking to my guns and a macro to move the John Doe info to the John Doe sheet. Try this one more time:
Copy and paste this code in the worksheet Main vb editior. To get to the vb editor right click the Main sheet tab and click on View code. Paste the code in the large white space.

Back on the Main worksheet select A3:A8 and in the Name Box type in Data and hit Enter.

Now run the code and the John Doe info (three rows of info) are transfered to the John Doe sheet F2:J4. We can change the final destination once you get this to work.

If you need help on how to run the code, there are a few ways. Alt + F8 and click on Run is one.

Post back if you get hung up.

Option Explicit

Sub Data()
Dim i As Variant
Dim Data As Range
Dim c As Range
Application.ScreenUpdating = False
i = Range("A1").Value
For Each c In Range("Data")
If c.Value = i Then
c.Resize(1, 5).Copy
Sheets("John Doe").Range("F10").End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

Howard

 

[Ben McClave]

Patrick,

I agree with Howard on this one. Macros are a better solution. That said,you could use the IFERROR function to capture the #ref! errors. The syntax is =IFERROR(<your formula here>, ""). The downside to this approach isthat all errors will return an empty string. This means that you may be ignoring errors that wouldn't otherwise want to ignore (such as #name? or #div/0!).

You could use an IF statement to isolate the #ref! error, such as IF(ERROR.TYPE(<your formula here>) = 4, "", <your formula here>). But this would add another layer of complexity and could slow your workbook down even further.

In my opinion, it is worth playing with the macro option(s) first before resorting to the formulas.

Ben