Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
occurance in more than on work book
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
occurance in more than on work book
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 he 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" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
occurance in more than on work book
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" wrote: 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 he 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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
occurance in more than on work book
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? --- "MAANI" wrote: 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. --- "MAANI" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
occurance in more than on work book
Thanks again Bob.But I don't know VBA or macros.If theres no other way don't
worry.I appreciate your help. "Bob Bridges" wrote: 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? --- "MAANI" wrote: 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. --- "MAANI" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas within a work book | Excel Worksheet Functions | |||
Creating a work book | New Users to Excel | |||
Work book shared | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
simultaneously work in a work book with other users | Excel Discussion (Misc queries) |