Massive number comparison across multiple columns and multiple files

[kwbMitel]

Excel 2007

I'm working on a number puzzle and due to the scope of the puzzle I have needed to spead my data across multiple files (67 in all, each in excess of 20MB)

I am now at the point where I need to find all values that are duplicated out of about 500,000 numbers.

My data is organized by columns and the data that needs to be compared is in every 3rd column in every spreadsheet. My largest file with the most colums is around 700 columns wide.

The filenames are consistant where only the last 3 characters are different representing what data is contained therein.

Some columns have up to 70,000 rows of data.

Yes, this is what I do for fun. You can stop laughing now.

I want to know if there is a formula that I can use to compare my data or is I should resign myself to copying and pasting all of my data into another spreadsheet?

**********************************************
What's most important is that you realise ... There is no spoon.

 

[SkipVought]

Hi,

All totaled, do you have more than 1M rows? If not, then WHY NOT have ALL your data in one sheet? 67 files! That is insane!

Consolidating your data is a job for VBA, and it can be accomplished. Furthermore, why not stick it ALL in an Access table?

Once in ONE TABLE, you will be able to use Excel's plethora of features to analyze your heart out.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

Not all files have 70,000 rows, the least of which have only 1000 rows. 1 Million rows is a reasonable approximation.

Why not have all your data in one sheet? I got tired of waiting minutes for each file to save and once in a while the application was crashing during save and losing data and causing me to restart. Every cell has interrelated formulas.

Whay not put it in an access table? I'm not versed in Access.

Using VBA to consolodate data - It would take me longer to write the program than it would take me to copy and paste.

That is insane! yup, that's me using the tools I know in the most inappropriate manner.

I guess I made this bed, time to lie in it.



**********************************************
What's most important is that you realise ... There is no spoon.

 

[Gruuuu]

Trying to link or retrieve data from all those files will be impossible. Your best bet is to get those numbers consolidated and separated from the formulas.

 

[Andrzejek]

I am with Skip.

You have a nail (all of your files) to drive thru the wall (all your logic), and you need to do it many times, over and over again. All you know is how to use a screwdriver (VBA in Excel), so you drive the nails thru the wall with the screwdriver.

Imagine what you can do with the hammer…. (database, that is)

I would learn how to use a hammer.

Have fun.

---- Andy

 

[kwbMitel]

I hear you guys. Believe me when I say that if I knew at the beginning of this adventure what I was in for I might not have started. I definitely would have planned better. I've never had a challenge as big as this as far as generating and retaining specific numbers is concerned.

I knew the answer when I asked it but I've been surprised before. Thanks anyway. I might let you know how it turns out in a week or two.

**********************************************
What's most important is that you realise ... There is no spoon.