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

    For example


    Name Address Phone # zip
    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


    Name Address Zip Phone #
    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



    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


    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!

Register Yourself Login