# Find Text String And Report Back Data In Unknown Number Of Rows

• For example

Danielle 4561
Danielle 9852
Danielle 22
Danielle 69
Joe 895
Joe 28
John 9821
John 1114
John 698

Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?

Thanks SO MUCH!

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

Do you want a macro or just use formulas?

You can count the number of rows for Joe (or anyone else) with =COUNTIF(A1:A9,"Joe*")

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

In cell B1 enter Joe

In cell C1 enter =IF(ROW()>COUNTIF(\$A\$1:\$A\$9,\$B\$1&"*"),"",MIN(ROW(),COUNTIF(\$A\$1:\$A\$9,\$B\$1&"*"))) and copy down 5 or 6 rows

In cell D1 enter =IF(C1="","",OFFSET(INDIRECT("A"&MATCH(\$B\$1&"*",\$A\$1:\$A\$9,0)),C1-1,0)) and copy down

Now you can change B1 to different names.

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

Thanks a bunch Brian. I'll do it right now and hopefully it'll work out! :cool:

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

The thing is, I won't be searching for the names manually. There are roughly 15000 unique "names" I have to lookup from one report into another and report back all relevant rows. I need a lookup with a countif (for the uncertain # of rows) and be able to report back into the first report all the relevant data from the second. I don't know if you'll be able to understand all that. I barely do!

THANKS SO MUCH you're saving my butt BIG TIME!

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

What do you mean by "report back"? What will be placed in the result report, the actual data or a count for each name, or what? It sounds as if you will need to use VBA, because lookups only give you the first item found.

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

REALLY? Nuts....I guess I do need a macro.

By "report back" I mean the following;

Report one

Name
Danielle
Joe
John
Mel
Cassie
(etc. 15,000 names or so but all are unique)

Report 2

Danielle
Danielle
Danielle
Danielle
Joe
Joe
John
John
John
John
Mel
Cassie
Cassie
(etc. 15,000 names or so but all are unique)

First I need to find each unique name (can't do it manually as there are too many) in report 2 from report 1. Then I need to take all data from all relevant rows (amt of data in rows differs everytime) from Report 2 and have it show up in Report 1 next to the name.

Get it? Oh this is never going to get done! Thanks for all your help guys! You are the best!

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

Over to the VBA guys, I think...

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

From your sample above, why not just do a sort on the names, this will group them together like you have them in the "report" two output you've shown.

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

I agree with JF. Simple is best.

• Re: Find Text String And Report Back Data In Unknown Number Of Rows

Seems like a job for autofilter or an advanced filter.

For the autofilter method.
1. Sort the data by name.

2. Create a new column to right or left or wherever. For simplicity, let's say you have Name in column A and Address in column B, all with titles starting in row 1. Then in C1 let's call this Unique. In C2, add this formula:
=NOT(A2=A1)

3. Copy the formula down.

4. Now select the title cells and press menu Data > Filter > AutoFilter.

5. Select the dropdown arrow and select True.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!