Pulling Data off Web - Need Function Help

P

patfergie44

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

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

Data
Number
Number

Data2
Number
Number

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

Your help would be GREATLY appreciated!

Pa
 
P

patfergie44

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
 
B

Biff

Ok........

Each time you pull data does each set contain the exact same number of
entries?
Data
Number
Number
Click to expand...

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
 
P

patfergie44

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
 
B

Biff

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:p[email protected]...
 
P

patfergie44

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
 
P

patfergie44

Hey Biff,

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

Pa
 
Top