Keyword search and copy cell below to new sheet...

Please forgive my convoluted description...

4 times per week, I receive 40 pages of data. Which I am supposed to copy and paste bits to build a matrix of sorts. To try to automate I dumped the data into excel and tried to isolate the cells that contain the information I need. Unfortunatly the data "header" is repeated 40 times and contains 40 different values so this was more work than just copy/paste. The solution is way beyond my skill level...

Any help would be greatly appreciated.

1. I need a vba code that will search for a series of 10 different key words in order and copy the contents of 1 cell directly below the keyword cell to a single row of a new sheet. Please note it is important that the data be grabbed as a series.

2. also the last keyword in each series will contain 10 content cells, rather than 1 cell, directly below the keyword cell.

3. loop to the "end",when there are 35 blank rows that means the end of the data.

Note: The data does not extend past column z or past row 1500. Don't know if that helps...

ANY help here is greatly appreciated!

I have attached a picture to help with what I'm trying desperatly to describe.

For Example:

below are the column/row locations for a 2 Keyword Series:
A4 = Go (Keyword #1)
A3 = Green
W3 = Stop (Keyword #2)
W4 = Red

C8 = Go (Keyword #1)
C9 = blue
A14 = Stop (Keyword #2)
A15 = Yellow

B26 = Go (Keyword #1)
B27 = 40
G24 = Stop (Keyword #2)
G25 = Moo

Here is the end result on a new sheet in the workbook:
A B C D...
Row 1: Green Red
Row 2: blue Yellow
Row 3: 40 Moo

Your picture didn't attach. But please attach a workbook instead, a picture is no use.

I can't seem to load from work. Until tonight when I get home maybe this will help.

A B C D E...
1 Joe Single Wayne 04/16/2010
2 Bill Divorced Pascer 08/09/2009
3 Jill Single Crowns 05/07/2008
4
SHEET 2 RESULTS

________________________________________________________________________

A B C D E...
1 Group 1
2 Name:
3 Joe Married:
4 Date: Single
5 04/16/2010
6 County
7 Wayne
8 ________________________________________________________
9 Group 2
10 Name: Date:
11 Bill 08/09/2009
12
13 County Married:
14 Pascer Divorced
15_________________________________________________________________
16 Group 3
17 Name:
18 Married: Jill Date:
19 Single County: 05/07/2008
20 Crowns
SHEET 1 STARTING DATA

I know it's been a while, always seem to be doing 40 things at once.

I cannot seem to attach any documents. is there anyone who understands the layout I provided?

What is the problem with attaching? Just mock-up a small sample. I doubt people will want to spend their time trying to interpret your data.

I have sent a note to the admin of this site. The "attach" button of the Post Reply doesn't work, on my work PC and home PC. I click it and nothing happens. I can email the sheet to anyone who would be willing to help.

If you PM me I will send you my email and I will try to post the workbook.

Jen - received, and successfully uploaded.

Based on your first attachment, it looks straightforward to write some code to extract the results you want. But I'm not clear how the second one fits in. If this is your actual data, what are the items to search for?

I tried to make it easy, but just muddied the water. So, just ignore the second file. Thanks Stephen!

This works for your first attachment. It's based on the colours which may or may not be a problem for your actual data.

Please Login or Register  to view this content.

Stephen,

Thank you so much for your effort on my behalf! It works like a dream! You have just saved me hours upon hours of the MOST tedious work...

If you ever have a graphic photo/art need, let me know. I owe you one!

Jennifer