data collection

[driller]

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..

 

[B. R.Ramachandran]

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 haven’t 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