ExcelBanter - data collection

Hi,

There may be a more elegant approach to accomplish what you want, but I came
up with the following method. I am sorry if I havent understood the problem
correctly.

DO THE FOLLOWING IN EACH OF THE SIX SHEETS (OTHER THAN THE MASTER LIST SHEET):
In some cell, say C2, enter the following formula to generate the number of
entries in the respective sheet.
=COUNTA(B:B)

DO THE FOLLOWING IN THE MASTER LIST SHEET:
You need a 6 column x 3 row blank area for this.

In an empty area of 6 adjacent cells (say J1, K1, €¦O1) enter the names of
the 6 sheets you want to compile the data from followed by the exclamation
sign. (e.g., Sheet2!, Sheet3!, €¦.)

In J2, enter the following formula, and drag it across till O2 (This will
copy the total number of entries in each sheet)
=INDIRECT(J1&"$C2")

In J3, enter the following formula, and drag it across O3 (this will
generate the cumulative total number of entries in the sheets)
=SUM($J$2:J2)

Now enter the following formula in B2 of the Master List Sheet, and drag to
fill down the column appropriately.
=IF(ROW(A2)-1<=$J$3,INDIRECT($J$1&"b"&ROW()),IF(ROW(A2)-1<=$K$3,INDIRECT($K$1&"b"&ROW()-$J$3),IF(ROW(A2)-1<=$L$3,INDIRECT($L$1&"b"&ROW()-$K$3),IF(ROW(A2)-1<=$M$3,INDIRECT($M$1&"b"&ROW()-$L$3),IF(ROW(A2)-1<=$N$3,INDIRECT($N$1&"b"&ROW()-$M$3), IF(ROW(A2)-1<=$O$3,INDIRECT($O$1&"b"&ROW()-$N$3),""))))))

The last formula will fill the Column B data from the six sheet in Column B
of the Master List Sheet. Since you cannot do a sort operation on this
column, do a €œCopy€ on Column B followed by €œPaste Special€ -- €œValues€ on
another column, say Column C. Column C data can be sorted.
(Or, you could enter the above mega-formula in Column C and do the
copy,paste special,values in Column B, so that the sortable column will be by
the side of Column A for easy comparison.

Note: If names are duplicated within/among the 6 sheets, those names will
be duplicated in the newly generated Columns B and C of the Master List Sheet
as well.

Hope this works.

Regards,
B. R. Ramachandran

"driller" wrote:

Hi again,

thanks in advance..

In Sheet1 (A2 ~ A1000) i have a master list of names.
In 6 other sheets (with varying sheet name) i have a list of concatenated
text containing names along Col. A.

I have a formula to extract just the names on the 6 other sheets (Col A)
Along Col B of other sheets i have a formula, e.g. B2 =LEFT($A2,LEN($A2)-15)

Now i need to compile all the extracted results (from 6 other sheets at Col
B) and
paste them in Col B of Sheet 1.

Then I will perform a sort operation and compare them with data @ Col A..

This is part of a checking procedure i need to perform to compare the
masterlist on sheet1 and the other as listed on 6 other sheets.

Maybe this can be done in excel in the master list of sheet1..
--
regards