jphleung
09-17-2008, 11:31 AM
Hello fellow users,
I am now working on a research thesis, which need to draw sample from data of various companies. It will take quite a long time to do it by hand, so I wonder if there is a faster way to do it, and hence I am trying VBA.
problem:
there are n sheets, where each of them is the data of a company. In those sheets, the entries in column Q (assume there is no header row) are the ones i want to draw sample from. The number of entries in column Q varies from sheet to sheet, they can be as low as 6 or as high as 24000. The size of the sample I want to draw is also different from sheet to sheet. I would like to have the exported entries to be outputed in another workbook (if that is too much work, it's ok to export them to a new sheet of the same workbook), in one single column.
e.g.
1 workbook: WB1.xls
4 sheets inside: GE, Agfa, Du Pont, P&G
where, GE has 50 entries in column Q, Agfa has 40, Du Pont has 55, and P&G has 5
10 entires to be randomly selected from the 50 entries in GE's column Q, 8 to be selected from Agfa, 20 to be selected from Du Pont, and 5 to be selected from P&G.
A total of 43 entries (10+8+20+5) are selected and pasted in column A of a new workbook (or sheet).
I notice that there is some other topics with similar problems, but the one i have requires retrieving samples from multiple sheets.
Jeff
I am now working on a research thesis, which need to draw sample from data of various companies. It will take quite a long time to do it by hand, so I wonder if there is a faster way to do it, and hence I am trying VBA.
problem:
there are n sheets, where each of them is the data of a company. In those sheets, the entries in column Q (assume there is no header row) are the ones i want to draw sample from. The number of entries in column Q varies from sheet to sheet, they can be as low as 6 or as high as 24000. The size of the sample I want to draw is also different from sheet to sheet. I would like to have the exported entries to be outputed in another workbook (if that is too much work, it's ok to export them to a new sheet of the same workbook), in one single column.
e.g.
1 workbook: WB1.xls
4 sheets inside: GE, Agfa, Du Pont, P&G
where, GE has 50 entries in column Q, Agfa has 40, Du Pont has 55, and P&G has 5
10 entires to be randomly selected from the 50 entries in GE's column Q, 8 to be selected from Agfa, 20 to be selected from Du Pont, and 5 to be selected from P&G.
A total of 43 entries (10+8+20+5) are selected and pasted in column A of a new workbook (or sheet).
I notice that there is some other topics with similar problems, but the one i have requires retrieving samples from multiple sheets.
Jeff