How to Combine Data Regions

[Orlanzo]

Hi all,

I have a worksheet which holds the results of two database queries. I want
to combine the regions programmatically saving the results to a new sheet. I
cannot create a single query to retrieve the combined data.

The initial worksheet looks similar to the following illustration:
Sheet 1:
Region 1:
Header1a Header1b Header1c ... Header1(n)
Row1a Row1b Row1c ... Row1(n)
Row1a Row1b Row1c ... Row1(n)
.... ... ... ... ...
.... ... ... ... ...
Row(N)a Row(N)b Row(N)c ... Row(N)(n)

Region 2:
Header2a Header2b Header2c ... Header2(n)
Row1a Row1b Row1c ... Row1(n)
Row1a Row1b Row1c ... Row1(n)
.... ... ... ... ...
.... ... ... ... ...
Row(N)a Row(N)b Row(N)c ... Row(N)(n)

The regions are separated by one or more blank lines. I'm going to remove
the sorting, grouping, and subtotals being applied to each region. Finally,
I want to grab the data combining it onto a new sheet. The columns and
formatting will alight between the regions. I'm not sure how to pick up the
data.

[Bernie Deitrick]

Orlanzo,

How many Headers are in common?
Is there just one key in common?
Are there key values present in one set that aren't present in the other
set?

HTH,
Bernie
MS Excel MVP


"Orlanzo" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I have a worksheet which holds the results of two database queries. I want
> to combine the regions programmatically saving the results to a new sheet.
> I
> cannot create a single query to retrieve the combined data.
>
> The initial worksheet looks similar to the following illustration:
> Sheet 1:
> Region 1:
> Header1a Header1b Header1c ... Header1(n)
> Row1a Row1b Row1c ... Row1(n)
> Row1a Row1b Row1c ... Row1(n)
> ... ... ... ... ...
> ... ... ... ... ...
> Row(N)a Row(N)b Row(N)c ... Row(N)(n)
>
> Region 2:
> Header2a Header2b Header2c ... Header2(n)
> Row1a Row1b Row1c ... Row1(n)
> Row1a Row1b Row1c ... Row1(n)
> ... ... ... ... ...
> ... ... ... ... ...
> Row(N)a Row(N)b Row(N)c ... Row(N)(n)
>
> The regions are separated by one or more blank lines. I'm going to remove
> the sorting, grouping, and subtotals being applied to each region.
> Finally,
> I want to grab the data combining it onto a new sheet. The columns and
> formatting will alight between the regions. I'm not sure how to pick up
> the
> data.

[Orlanzo]

Hello Bernie,

The headers in both sections aer identical. The only difference between the
two sets is the source of the data. Of course, the numbers in each set will
be different. I simply need to union them together.

I'm automating excel from another application (Access) and need to combine
the sets before importing the information into a database application.

Thanks for your help!

Orlanzo

"Bernie Deitrick" wrote:

> Orlanzo,
>
> How many Headers are in common?
> Is there just one key in common?
> Are there key values present in one set that aren't present in the other
> set?
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Orlanzo" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> >
> > I have a worksheet which holds the results of two database queries. I want
> > to combine the regions programmatically saving the results to a new sheet.
> > I
> > cannot create a single query to retrieve the combined data.
> >
> > The initial worksheet looks similar to the following illustration:
> > Sheet 1:
> > Region 1:
> > Header1a Header1b Header1c ... Header1(n)
> > Row1a Row1b Row1c ... Row1(n)
> > Row1a Row1b Row1c ... Row1(n)
> > ... ... ... ... ...
> > ... ... ... ... ...
> > Row(N)a Row(N)b Row(N)c ... Row(N)(n)
> >
> > Region 2:
> > Header2a Header2b Header2c ... Header2(n)
> > Row1a Row1b Row1c ... Row1(n)
> > Row1a Row1b Row1c ... Row1(n)
> > ... ... ... ... ...
> > ... ... ... ... ...
> > Row(N)a Row(N)b Row(N)c ... Row(N)(n)
> >
> > The regions are separated by one or more blank lines. I'm going to remove
> > the sorting, grouping, and subtotals being applied to each region.
> > Finally,
> > I want to grab the data combining it onto a new sheet. The columns and
> > formatting will alight between the regions. I'm not sure how to pick up
> > the
> > data.
>
>
>

[Orlanzo]

For completeness....

What I finally did was to remove all subtotals and grouping. Next, I wrote
the necessary sheets to a csv file and processed the file as required prior
to using the data as required.

Orlanzo

"Orlanzo" wrote:

> Hello Bernie,
>
> The headers in both sections aer identical. The only difference between the
> two sets is the source of the data. Of course, the numbers in each set will
> be different. I simply need to union them together.
>
> I'm automating excel from another application (Access) and need to combine
> the sets before importing the information into a database application.
>
> Thanks for your help!
>
> Orlanzo
>
> "Bernie Deitrick" wrote:
>
> > Orlanzo,
> >
> > How many Headers are in common?
> > Is there just one key in common?
> > Are there key values present in one set that aren't present in the other
> > set?
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Orlanzo" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi all,
> > >
> > > I have a worksheet which holds the results of two database queries. I want
> > > to combine the regions programmatically saving the results to a new sheet.
> > > I
> > > cannot create a single query to retrieve the combined data.
> > >
> > > The initial worksheet looks similar to the following illustration:
> > > Sheet 1:
> > > Region 1:
> > > Header1a Header1b Header1c ... Header1(n)
> > > Row1a Row1b Row1c ... Row1(n)
> > > Row1a Row1b Row1c ... Row1(n)
> > > ... ... ... ... ...
> > > ... ... ... ... ...
> > > Row(N)a Row(N)b Row(N)c ... Row(N)(n)
> > >
> > > Region 2:
> > > Header2a Header2b Header2c ... Header2(n)
> > > Row1a Row1b Row1c ... Row1(n)
> > > Row1a Row1b Row1c ... Row1(n)
> > > ... ... ... ... ...
> > > ... ... ... ... ...
> > > Row(N)a Row(N)b Row(N)c ... Row(N)(n)
> > >
> > > The regions are separated by one or more blank lines. I'm going to remove
> > > the sorting, grouping, and subtotals being applied to each region.
> > > Finally,
> > > I want to grab the data combining it onto a new sheet. The columns and
> > > formatting will alight between the regions. I'm not sure how to pick up
> > > the
> > > data.
> >
> >
> >