Pulling Data off Web - Need Function Help

**patfergie44** · 06-19-2006, 10:04 PM

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

Biff · 06-19-2006, 10:45 PM

Hi!

You need to explain this more thoroughly:

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

Biff

"patfergie44" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Biff · 06-19-2006, 10:50 PM

Hi!

You need to explain this more thoroughly:

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

Biff

"patfergie44" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Biff · 06-19-2006, 10:55 PM

Hi!

You need to explain this more thoroughly:

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

Biff

"patfergie44" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

**patfergie44** · 06-19-2006, 11:04 PM

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

Biff · 06-20-2006, 12:20 AM

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

**patfergie44** · 06-20-2006, 07:00 AM

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

Biff · 06-20-2006, 02:50 PM

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

**patfergie44** · 06-20-2006, 07:40 PM

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:

[email protected]

Thanks again!

Pat

Biff · 06-20-2006, 10:00 PM

Ok......

The "NA" changes things!

The formula now becomes more complex.

Biff

"patfergie44" <[email protected]>
wrote in message
news:[email protected]...
>
> 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:
>
> [email protected]
>
> 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
>

**patfergie44** · 06-21-2006, 07:12 AM

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

Biff · 06-21-2006, 10:30 PM

Sent a sample file.

Biff

"patfergie44" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>