I have searched extensively for an answer to this but can't find
anything.

I am pulling data off the web and it puts data down the column in the
following manner:

Data
Number
Number

Data2
Number
Number

I need to extract each set of Data to another worksheet. The problem
is that it will be anywhere from 1 row each to 10 rows each. In each
instance, there is a blank between the two sets of Data. I've looked
at ISBLANK, LOOKUP, SEARCH, etc. but nothing I've tried has worked
out.

Your help would be GREATLY appreciated!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile: http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Hi!

You need to explain this more thoroughly:

I need to extract each set of Data to another worksheet.

Biff

"patfergie44"
wrote in message
...

I have searched extensively for an answer to this but can't find
anything.

I am pulling data off the web and it puts data down the column in the
following manner:

Data
Number
Number

Data2
Number
Number

I need to extract each set of Data to another worksheet. The problem
is that it will be anywhere from 1 row each to 10 rows each. In each
instance, there is a blank between the two sets of Data. I've looked
at ISBLANK, LOOKUP, SEARCH, etc. but nothing I've tried has worked
out.

Your help would be GREATLY appreciated!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile:
http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Hi Biff,

When I pull data off the web, it comes in as this one time:

Data
Number
Number

Data2
Number
Number

And this the next time:

Data
Number
Number
Number
Number

Data2
Number
Number
Number
Number

What I want to do is extract this information and place it into another
worksheet as:

Data Number Number
Data2 Number Number

And the next time as:

Data Number Number Number Number
Data2 Number Number Number Number

It changes each time up to as many as 10 rows for each of the Data I'm
trying to extract. There is always a blank cell between the last
number of the first set of Data and the start of Data2.

Hopefully I've explained this correctly.

Thanks for your help!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile: http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Ok........

Each time you pull data does each set contain the exact same number of
entries?

Data
Number
Number

What is the EXACT location (cell address) of the first set of data starting
with the word Data?

Biff

"patfergie44"
wrote in message
...

Hi Biff,

When I pull data off the web, it comes in as this one time:

Data
Number
Number

Data2
Number
Number

And this the next time:

Data
Number
Number
Number
Number

Data2
Number
Number
Number
Number

What I want to do is extract this information and place it into another
worksheet as:

Data Number Number
Data2 Number Number

And the next time as:

Data Number Number Number Number
Data2 Number Number Number Number

It changes each time up to as many as 10 rows for each of the Data I'm
trying to extract. There is always a blank cell between the last
number of the first set of Data and the start of Data2.

Hopefully I've explained this correctly.

Thanks for your help!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile:
http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Hi Biff,

When I pull data from the web, it populates the worksheet. The Heading
for the first Data is in cell B1. The numbers then begin in cell B2 and
go down. In column A are dates beginning at A2. Depending on how many
dates are in the web page depends on how many rows of data I get.
There is always a blank cell before the next set of Data2 is displayed.
So, I never know where that will be placed on the worksheet.
Basically, what I need to do is:

Beginning at cell B1, I want to place that cell into another worksheet
along with everything below it, up to but excluding the blank cell (I
won't know how many cells since it will change each time I run the web
query). Then, right after the blank cell (it might be B5, B6, B7,etc),
I want to place that Data into another worksheet. Hopefully this
explains it better.

Thank you so much for all your help!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile: http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Ok.......this is easy but somewhat complicated at the same time.

This formula will do what you want: (however*)

=IF(COLUMNS($A:A)<=MATCH("*",$B$2:$B$15,0)-1,INDEX($B:$B,(ROWS($1:1)-1)*MATCH("*",$B$2:$B$15,0)+COLUMNS($A:B)-1),"")

however* = The most important thing about this is finding how many entries
are in a set. To do this we need to find the first empty cell between sets.
Since you said there can be from 1 to 10 entries that means the first empty
cell should be somewhere in the range of B2:B15. I'm assuming that the
"numbers" pulled are really numbers and not TEXT, otherwise this formula
won't work properly.

Since you may have up to 10 entries per set you need to copy the formula
across to at least 10 cells. Then, copy down as needed. Add sheet names as
appropriate.

I can put together a sample file if you'd like. Just let me know where to
send it.

Biff

"patfergie44"
wrote in message
...

Hi Biff,

When I pull data from the web, it populates the worksheet. The Heading
for the first Data is in cell B1. The numbers then begin in cell B2 and
go down. In column A are dates beginning at A2. Depending on how many
dates are in the web page depends on how many rows of data I get.
There is always a blank cell before the next set of Data2 is displayed.
So, I never know where that will be placed on the worksheet.
Basically, what I need to do is:

Beginning at cell B1, I want to place that cell into another worksheet
along with everything below it, up to but excluding the blank cell (I
won't know how many cells since it will change each time I run the web
query). Then, right after the blank cell (it might be B5, B6, B7,etc),
I want to place that Data into another worksheet. Hopefully this
explains it better.

Thank you so much for all your help!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile:
http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Hi Biff,

Thank you again for all your help. I haven't tried the formula yet
because, at times, there is the dreaded "NA" in the cells. Generally,
there are only numbers. If you could put together a file, that would
be AWESOME. I can't thank you enough for your time in helping me out.
My email address is:



Thanks again!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile: http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Ok......

The "NA" changes things!

The formula now becomes more complex.

Biff

"patfergie44"
wrote in message
...

Hi Biff,

Thank you again for all your help. I haven't tried the formula yet
because, at times, there is the dreaded "NA" in the cells. Generally,
there are only numbers. If you could put together a file, that would
be AWESOME. I can't thank you enough for your time in helping me out.
My email address is:



Thanks again!

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile:
http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Hey Biff,

If there's any way you think you can make this work, that would be
great. I appreciate all your help in working on this. I'll wait to
hear back from you.

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile: http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472

Sent a sample file.

Biff

"patfergie44"
wrote in message
...

Hey Biff,

If there's any way you think you can make this work, that would be
great. I appreciate all your help in working on this. I'll wait to
hear back from you.

Pat


--
patfergie44
------------------------------------------------------------------------
patfergie44's Profile:
http://www.excelforum.com/member.php...o&userid=35581
View this thread: http://www.excelforum.com/showthread...hreadid=553472