Here is the background info: I have 5 sheets in a WB, each containing nearly identical tables. The tables are set up in the classic
manner. The row headers cover 10 columns (StoreName, StoreNum, Add1,StoreMgr, etc.,etc.). The same store may be listed multiple
times but one of the items would be different (Different StoreMgr usually. Sometimes it is address slightly changed, like Street
instead of St. or something like that). The Column Headers, once you get past the stuff just mentioned, are product numbers (i.e.
M6466 or WA5955-2). Each of the 5 sheets will mostly list the same stores in mostly the same order, but each could be a little
different than the others. For instance, they might each have approximately 1025 rows, but no 2 of them having the same number of
rows. Also, 2 sheets could have the same # of rows, but not all the exact same stores. Each sheet will have all different product
#s. They don't load them all into one sheet because of the stupid 256 column limit. The 1st 1 or 2 characters in the prod #
represent the type of product. M = Mens, WA = Womens Accessories, etc. The data in the table will all be integers less than 100.
There are no blanks in the data but there are lots of zeros.

The Task: I create a sheet for each prod type and then list the stores and prod #s for that prod type, and I must make sure that the
same store has the correct integer value for each prod #, matching the original data from the source sheets.

What I've done: I have (via code) inserted new sheets for each prod type; named each sheet, inserted all of the column headers as
appropriate for each sheet.

The problem: I need to load the stores and the data. I don't need code, I can handle that. I'm interested in the best approach for
matching the data with the right store and prod #, keeping in mind that several of the prod #s on each sheet came from a different
source sheet, so I can't load entire rows or entire columns of data at a time because they wouldn't line up correctly all the way
down or all the way across. I'm thinking of making use of the intersection function within a loop or 2.

Any good ideas?
--
RMC,CPA

My first think on this problem might work something like this. Build a
string array ("strPC") long enough to hold all of the product codes from your
column headers across all 5 worksheets. That should be somewhere around
246+4*256 or 1270 product codes. Then, build a two dimensional array as
aryVALUES(3000,1270) as integer, taking into account the size of your
computer and the maximum array limits you would expect. Here, I'm presuming
no more than 3000 transactions per time period and the 1,270 product code
columns. We would index this array with each row representing a unique store
and each column as the quantity of product sold. (If the content of any
single transaction is always less than 100, you might consider making
"aryVALUES as Byte").
I'd then write code to scan the header columns to retrieve the product
codes and place them into strPC (stands for string "Product Code"). Once
done, you can write VBA code that would process each row of each worksheet,
scan across that row for any non-zero value, and pull the product code from
that column's header row. With the product code now known, scan strPC for a
match and use the index into strPC as an index into aryVALUES(intThisRow,
intPC_Index). Either sum in the transaction count (from the source
worksheet) or the count of products or whatever you're looking for. When
worksheet 1 is complete, move on to worksheet 2, 3, etc.
Once all done, strPC has the product codes that you can use to fetch
the product type and aryVALUES has the content. Sort, select, filter,
manipulate, whatever, to move the information to your target worksheets. If
you need to know the store ID as well as the product code and product type,
add another array to keep track of which store is contributing to the content
of each line of aryVALUES. As in, aryStores(3000) as String and move the
store name there as aryVALUES is being filled from the source worksheets.
Or, something like that.

Steve in Ohio

"R. Choate" wrote:

Here is the background info: I have 5 sheets in a WB, each containing nearly identical tables. The tables are set up in the classic
manner. The row headers cover 10 columns (StoreName, StoreNum, Add1,StoreMgr, etc.,etc.). The same store may be listed multiple
times but one of the items would be different (Different StoreMgr usually. Sometimes it is address slightly changed, like Street
instead of St. or something like that). The Column Headers, once you get past the stuff just mentioned, are product numbers (i.e.
M6466 or WA5955-2). Each of the 5 sheets will mostly list the same stores in mostly the same order, but each could be a little
different than the others. For instance, they might each have approximately 1025 rows, but no 2 of them having the same number of
rows. Also, 2 sheets could have the same # of rows, but not all the exact same stores. Each sheet will have all different product
#s. They don't load them all into one sheet because of the stupid 256 column limit. The 1st 1 or 2 characters in the prod #
represent the type of product. M = Mens, WA = Womens Accessories, etc. The data in the table will all be integers less than 100.
There are no blanks in the data but there are lots of zeros.

The Task: I create a sheet for each prod type and then list the stores and prod #s for that prod type, and I must make sure that the
same store has the correct integer value for each prod #, matching the original data from the source sheets.

What I've done: I have (via code) inserted new sheets for each prod type; named each sheet, inserted all of the column headers as
appropriate for each sheet.

The problem: I need to load the stores and the data. I don't need code, I can handle that. I'm interested in the best approach for
matching the data with the right store and prod #, keeping in mind that several of the prod #s on each sheet came from a different
source sheet, so I can't load entire rows or entire columns of data at a time because they wouldn't line up correctly all the way
down or all the way across. I'm thinking of making use of the intersection function within a loop or 2.

Any good ideas?
--
RMC,CPA

I would build a single sheet with

10 columns of Store Data, product type, product code, integer

each sheet should produce 246 columns * 1025 rows of data = 251,904
for 5 sheets, 1,259,520

but you said there are a lot of zeros, so as you build the rows, you could
skip any data that contain zero.

The assumption is that you will produce less than 65535 rows of data. If
that is not the case, you could spread the data over several sheets by using
the product type to break it out (product types starting with A - H go on
one sheet, etc).

Once you have these new intermediate data tables, you can do your analysis
using Pivot Tables.

--
Regards,
Tom Ogilvy



"R. Choate" wrote in message
...
Here is the background info: I have 5 sheets in a WB, each containing
nearly identical tables. The tables are set up in the classic
manner. The row headers cover 10 columns (StoreName, StoreNum,
Add1,StoreMgr, etc.,etc.). The same store may be listed multiple
times but one of the items would be different (Different StoreMgr usually.
Sometimes it is address slightly changed, like Street
instead of St. or something like that). The Column Headers, once you get
past the stuff just mentioned, are product numbers (i.e.
M6466 or WA5955-2). Each of the 5 sheets will mostly list the same stores
in mostly the same order, but each could be a little
different than the others. For instance, they might each have
approximately 1025 rows, but no 2 of them having the same number of
rows. Also, 2 sheets could have the same # of rows, but not all the exact
same stores. Each sheet will have all different product
#s. They don't load them all into one sheet because of the stupid 256
column limit. The 1st 1 or 2 characters in the prod #
represent the type of product. M = Mens, WA = Womens Accessories, etc. The
data in the table will all be integers less than 100.
There are no blanks in the data but there are lots of zeros.

The Task: I create a sheet for each prod type and then list the stores and
prod #s for that prod type, and I must make sure that the
same store has the correct integer value for each prod #, matching the
original data from the source sheets.

What I've done: I have (via code) inserted new sheets for each prod type;
named each sheet, inserted all of the column headers as
appropriate for each sheet.

The problem: I need to load the stores and the data. I don't need code, I
can handle that. I'm interested in the best approach for
matching the data with the right store and prod #, keeping in mind that
several of the prod #s on each sheet came from a different
source sheet, so I can't load entire rows or entire columns of data at a
time because they wouldn't line up correctly all the way
down or all the way across. I'm thinking of making use of the intersection
function within a loop or 2.

Any good ideas?
--
RMC,CPA

Thanks guys,

I'll study your suggestions some more and see if these will handle my problem. They both sound like good ideas that are pretty
similar and I'll just hope that I can make it work. Thanks again for the help.

Richard
--
RMC,CPA


"R. Choate" wrote in message ...
Here is the background info: I have 5 sheets in a WB, each containing nearly identical tables. The tables are set up in the classic
manner. The row headers cover 10 columns (StoreName, StoreNum, Add1,StoreMgr, etc.,etc.). The same store may be listed multiple
times but one of the items would be different (Different StoreMgr usually. Sometimes it is address slightly changed, like Street
instead of St. or something like that). The Column Headers, once you get past the stuff just mentioned, are product numbers (i.e.
M6466 or WA5955-2). Each of the 5 sheets will mostly list the same stores in mostly the same order, but each could be a little
different than the others. For instance, they might each have approximately 1025 rows, but no 2 of them having the same number of
rows. Also, 2 sheets could have the same # of rows, but not all the exact same stores. Each sheet will have all different product
#s. They don't load them all into one sheet because of the stupid 256 column limit. The 1st 1 or 2 characters in the prod #
represent the type of product. M = Mens, WA = Womens Accessories, etc. The data in the table will all be integers less than 100.
There are no blanks in the data but there are lots of zeros.

The Task: I create a sheet for each prod type and then list the stores and prod #s for that prod type, and I must make sure that the
same store has the correct integer value for each prod #, matching the original data from the source sheets.

What I've done: I have (via code) inserted new sheets for each prod type; named each sheet, inserted all of the column headers as
appropriate for each sheet.

The problem: I need to load the stores and the data. I don't need code, I can handle that. I'm interested in the best approach for
matching the data with the right store and prod #, keeping in mind that several of the prod #s on each sheet came from a different
source sheet, so I can't load entire rows or entire columns of data at a time because they wouldn't line up correctly all the way
down or all the way across. I'm thinking of making use of the intersection function within a loop or 2.

Any good ideas?
--
RMC,CPA