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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Searching for specific name on main sheet then copying row data associated with name (https://www.excelbanter.com/excel-programming/447872-searching-specific-name-main-sheet-then-copying-row-data-associated-name.html)

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

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

Searching for specific name on main sheet then copying row dataassociated with name

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

Quote:

Originally Posted by Ben McClave (Post 1608161)

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!

Searching for specific name on main sheet then copying row dataassociated with name

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.

Code:

John Doe         100        1000        200        300

John Doe 300        3000        400        500

John Doe         500        5000        600        700

Code:

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

Searching for specific name on main sheet then copying row dataassociated with name

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

Quote:

Originally Posted by Ben McClave (Post 1608200)

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!

Searching for specific name on main sheet then copying row dataassociated with name

On Tuesday, December 25, 2012 2:58:47 PM UTC-8, Patrick Johnson wrote:
Ben McClave;1608200 Wrote:

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

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!

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

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

--

Patrick Johnson

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.

Code:

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

Searching for specific name on main sheet then copying row dataassociated with name

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 is that 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