find and then high lite row under find

good morning, i am trying to do the following:

My Excel file has over 3000 total rows, in that file i have a row called "owner" and under that row is a row with peoples full name. i would like to select all the rows under the "owner" row. i don't need the owner row selected only the name row to copy to a new column.
is this possible?

Thanks in advance

So, do you mean that you want to copy cells A2, A4, A6 etc. into a different column (e.g. C) with no gaps, so those cells appear in C1, C2, C3 and so on?

Pete

Yep. that's what i want

Okay, you can put this formula in C1:

=INDEX(A:A,ROWS($1:1)*2)

then copy down as far as you need to.

Hope this helps.

Pete

This does not seem to be working.

it is only putting one entry of "owner" in C1
i don't see any of the other rows in column C

Check out the attached file.

Hope this helps.

Pete

OK, maybe its not working on my end because my file is not like yours, my file sample is following:

Owner:
Charles Arthur
Address:
123 Main,
anywhere, AL 10133
Phone:
(123) 456- 7890
Email:
[email protected]
Owner:
David stock
Address:
222 Oak St.
Salty river, IA 54332
Phone:
(222) 345-2345
Email:
[email protected]

I can't tell from that if the name and address is in one cell or in several cells, so it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

ok, file attached. its a small sample of the file

I guess i should of said that i am really after is to convert all the data in column A into separate columns. i thought i could figure it out but i cant.

hai i have a clarification
do you want the details below each owner to come in separate columns
or in the same column without that owner...

Will you always have 9 rows covering a cell containing "Owner:" and the last line of the address (as in your example), or is the length of the address variable?

Do you just want to bring across the name, i.e. from the cell immediately below one containing "Owner:"?

Pete

hai see whether it helps

Pete, i think some of the rows may have more than 9 rows.
Yes, I would like the details below each "owner"to come in separate columns

Ah, right - I've just seen your clarifications in Post #10 and Post #14. It is a bit more difficult with a variable number of rows for each record, and I can only work with what you have supplied, so you can put the value 1 in cell B1 and this formula in B2:

=IF(A2="Owner:",MAX(B$1:B1)+1,"-")

and copy down to the bottom of your data.

Then you can put headings in cells D1:H1 and these formulae in the cells stated:

D2: =IFERROR(INDEX($A:$A,MATCH(ROWS($1:1),$B:$B,0)+COLUMNS($B:B)),"")

E2: =IFERROR(INDEX($A:$A,MATCH(ROWS($1:1),$B:$B,0)+COLUMNS($B:D)),"")

F2: =IFERROR(INDEX($A:$A,MATCH(ROWS($1:1),$B:$B,0)+COLUMNS($B:E)),"")

G2: =IFERROR(""&INDEX($A:$A,MATCH(ROWS($1:1),$B:$B,0)+COLUMNS($B:G)),"")

H2: =IFERROR(INDEX($A:$A,MATCH(ROWS($1:1),$B:$B,0)+COLUMNS($B:I)),"")

Then you can copy these down as far as you need to, as can be seen in the attached file.

Hope this helps.

Pete

Pete, i just checked my RAW file and they are all spaced 9 rows apart. does that help?

Yes, the solution I gave you in Post #15 should work directly with that data - it's probably easier for you to just copy/paste your column A data into column A of my file, then copy the formulae down as far as required.

Hope this helps.

Pete

Pete, you are the man. that works perfect.

Thanks Mate!!!!

Well, that's good to hear - glad you got it working.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

One more thing Pete. How do I copy and past the information from the new columns created into a new XLS file?

OK i got it.

I did a "Paste Values" only

Great to hear.

Pete