occurance in more than on work book

  • Thread starter Thread starter MAANI
  • Start date Start date
M

MAANI

I have like 100 workbooks,each one has column H with numbers in this format
####-##-###-####,I want to find the top 10 numbers occuring in all these
workbooks. Note that come cells in column H are blanks.
 
100 workbooks, eh? You didn't mention which worksheet these figures are in,
for each workbook; is that because there's only one worksheet in each
workbook or because you meant you have like 100 worksheets? I'll take you
literally on that, and pretend each workbook has a particular sheet you want.
I'm going to ignore the format issue for now (because I'm not it is one),
and I'll pretend for the purposes of this idea I have that by "top" you mean
"greatest" (ie 100 is greater than 15). With that in mind, how about this:

1) Set up a central workbook, or if you already have one then set up a sheet
in that workbook, consisting of workbook path-and-file names and the
worksheet name, one to each row. We'll pretend they're in column A, all
looking like this:

Row 1: ="'[C:\Users\Manni\Locators\Forthwith 2006-11.xls]Sheet1!'"
Row 2: ="[C:\Users\Manni\Locators\Forthwith 2006-11.xls]Sheet3!'"
Row 3: ="[C:\Users\Manni\Locators\Forthwith 2006-11.xls]LastSheet!'"

....and so on. Now, let's further assume that each of these worksheets has a
single header row and that each is sorted on column H in descending order, so
that from here all you need is H2:H11 in each of the workbook/worksheets
named above. I suggest you do it like this: In columns B through K (that's
ten columns, one for each of the top ten values in a given worksheet) pull
the values from that worksheet using the INDIRECT function:

=INDIRECT(A1&"R"&COLUMN()&"C8",FALSE)

Look what's happening here: In column B, the COLUMN() function returns a 2
(because B is column 2); in that column you want to pull the value from row 2
of that workbook, so now you have "R2C8", which in R1C1 mode points to H2 of
that worksheet. Concatenate that to the file-and-sheet name in A1, add FALSE
to tell the INDIRECT function that you're handing it an R1C1-formatted
address instead of an A1, and you get back the proper value from that
workbook. Continue that throughout the row to column K, and do the same on
the other 99-or-whatever rows (each of them pulling the same top ten values
from their respective workbooks.

Now you have about like 1000 top values, from which you can pull the top ten
in another stage. But let's not tackle that stage yet: First, is this about
what you need, or are there already problems?
 
Thanks Bob for your reply.I think I need to make it more clear,I have 100
workbooks,in each workbook there's one sheet,each sheet has numbers in column
H,with this format ####-##-###-####,I want a way to find the 10 most occuring
numbers in column H for all workbooks.Example:1234-56-789-1234 this is a
number in column H,I want to know if its one of the 10 most numbers repeating
in column H in all workbooks.

Bob Bridges said:
100 workbooks, eh? You didn't mention which worksheet these figures are in,
for each workbook; is that because there's only one worksheet in each
workbook or because you meant you have like 100 worksheets? I'll take you
literally on that, and pretend each workbook has a particular sheet you want.
I'm going to ignore the format issue for now (because I'm not it is one),
and I'll pretend for the purposes of this idea I have that by "top" you mean
"greatest" (ie 100 is greater than 15). With that in mind, how about this:

1) Set up a central workbook, or if you already have one then set up a sheet
in that workbook, consisting of workbook path-and-file names and the
worksheet name, one to each row. We'll pretend they're in column A, all
looking like this:

Row 1: ="'[C:\Users\Manni\Locators\Forthwith 2006-11.xls]Sheet1!'"
Row 2: ="[C:\Users\Manni\Locators\Forthwith 2006-11.xls]Sheet3!'"
Row 3: ="[C:\Users\Manni\Locators\Forthwith 2006-11.xls]LastSheet!'"

...and so on. Now, let's further assume that each of these worksheets has a
single header row and that each is sorted on column H in descending order, so
that from here all you need is H2:H11 in each of the workbook/worksheets
named above. I suggest you do it like this: In columns B through K (that's
ten columns, one for each of the top ten values in a given worksheet) pull
the values from that worksheet using the INDIRECT function:

=INDIRECT(A1&"R"&COLUMN()&"C8",FALSE)

Look what's happening here: In column B, the COLUMN() function returns a 2
(because B is column 2); in that column you want to pull the value from row 2
of that workbook, so now you have "R2C8", which in R1C1 mode points to H2 of
that worksheet. Concatenate that to the file-and-sheet name in A1, add FALSE
to tell the INDIRECT function that you're handing it an R1C1-formatted
address instead of an A1, and you get back the proper value from that
workbook. Continue that throughout the row to column K, and do the same on
the other 99-or-whatever rows (each of them pulling the same top ten values
from their respective workbooks.

Now you have about like 1000 top values, from which you can pull the top ten
in another stage. But let's not tackle that stage yet: First, is this about
what you need, or are there already problems?

--- "MAANI said:
I have like 100 workbooks,each one has column H with numbers in this format
####-##-###-####,I want to find the top 10 numbers occuring in all these
workbooks. Note that come cells in column H are blanks.
Click to expand...
Click to expand...
 
Ah, so "top 10 occurring numbers" doesn't mean the 10 greatest numbers, but
the numbers that occur most often? Got it.

Seems to me, though, that if you want to do it with Excel worksheet
functions you'll still have to include something in each workbook that counts
up...no, that's not going to work. (Pause for thought.) Ok, this may be
POSSIBLE to do with worksheet functions, but it's more complicated than I can
easily see right now. I think you need to make this a VBA program, a macro
in other words. Are you up for that?
 
Thanks again Bob.But I don't know VBA or macros.If theres no other way don't
worry.I appreciate your help.
 
Back
Top