Extracting data from multiple worksheets (in Excel 2000)

  • Thread starter Thread starter gary
  • Start date Start date
G

gary

I have a "MASTER" worksheet containing UNIQUE labels in
cells A1 thru A3000.

I have 18 worksheets, each containing labels in cells A1
thru A65000 (for a total of 1.2 million cells).

The labels in the "MASTER" worksheet occur MULTIPLE times
in the 18 worksheets and the 18 worksheets also contain
labels that are NOT in the "MASTER" worksheet.

How can I extract, from the 18 worksheets, ONLY the cells
whose labels match those in the "MASTER" worksheet?
 
Hi Gary,

this might work if you only have column labels and no row labels and
think you will have to do this per sheet so 18 times...:

With pivot tables you can easily select only the data from the column
you want to use...

You do that like this: Data\Pivottable, from there you follow th
wizard and you'll only have to select the data on your sheet.
Then by dragging the right labels in the pivottable you create th
desired data set.

Hope it is what you are looking for,
Good luck!

Robb
 
Sorry Gary I overlooked the fact that you have that much column labels
ofcourse last mentionned idea wouldn't make any sense.. Hope sopmeon
else can help you..
 
Hi, Frank,

CPEARSON's formula says:

=if(countif($A$1:$A$10,B1)>0,B1,"")

which assumes that the lists are in A1:A10 and B1:B10.

but he says to change B1 and $A$10:$A$10 to fit my lists
which are in A1:A3586 and B1:B64288.

How do I change CPEARSON's formula?

Gary
 
Hi
put the following formula in C1:
=if(countif($A$1:$A$3586,B1)>0,B1,"")
and copy this down
In column C you'll see all common entries. Now you may copy this column
insert it as values (goto 'Edit - Paste Special' to do this) and filter
the non blank rows
 
Frank,

Do I copy the formula from C1 to C3586 or from C1 to
C64288 or from C1 to ????.

Gary
 
Frank,

I pasted the formula from C1 thru C64288 but it's taking
FOREVER to execute the action. Can anything be done to
speed up the process?

(I have 18 worksheets to do and each one has 65,000 rows.
I don't have a lifetime to complete this project).

Gary
 
Hi
if you have that much of data you may consider the following:
1. Switch to a database :-)
2. Turn of autocalculation ('Tools - Options - Calculation) and perform
a manual re-calculation after entering ALL formulas
 
Frank,

First, I did your "turn off autocalculation" suggestion
but, when I performed the manual re-calculation, it still
took an eternity.

I then imported the file into Paradox. It ALMOST worked
OK. I first had to split the 1.2 million row file into
two files. Then, after extracted the matching rows, they
were not in the correct sequence (most rows look like they
are sorted in reverse order, but not always).

I'll add sequence numbers and try it again. If the rows
get out of order again, at least I can sort the rows using
the sequence numbers to get them in the right order.

Gary
 
Back
Top