I would want with a macro use the sheet STATISTICA similar a pivot (but repeat i dont want a pivot).
Bsed on column H and column D of GAF, scanning in all cells of columns H and if is found a value present in one sheet CORPORTAE, RETAIL_POE, PUBB_AMM, LARGE_COR, SCARTI, (have the same range for all sheet G2:G500) add 1 in cell refred column D of GAF and column A of STATISTICA fill the cell in STATISTICA based column D...
Example:
cell H2 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C25 of STATISTICA because in A25 is present 4549...
cell H3 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C31 of STATISTICA because in A31 is present 4561...
cell H4 in GAF contain "" put 1 in G25 of STATISTICA because in A25 is present 4549...
cell H5 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C30 of STATISTICA because in A30 is present 4558...
........
cell H51 in GAF contain 11 this value is present only in sheetCORPORATE in range G2:G500 put 1 in B2 of STATISTICA because in A2 is present 4500...
naturally if the value in H is found for many matching ad 1 the the old value stored
Hope i am clear...
here my file: http://www.mytempdir.com/991542
Bsed on column H and column D of GAF, scanning in all cells of columns H and if is found a value present in one sheet CORPORTAE, RETAIL_POE, PUBB_AMM, LARGE_COR, SCARTI, (have the same range for all sheet G2:G500) add 1 in cell refred column D of GAF and column A of STATISTICA fill the cell in STATISTICA based column D...
Example:
cell H2 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C25 of STATISTICA because in A25 is present 4549...
cell H3 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C31 of STATISTICA because in A31 is present 4561...
cell H4 in GAF contain "" put 1 in G25 of STATISTICA because in A25 is present 4549...
cell H5 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C30 of STATISTICA because in A30 is present 4558...
........
cell H51 in GAF contain 11 this value is present only in sheetCORPORATE in range G2:G500 put 1 in B2 of STATISTICA because in A2 is present 4500...
naturally if the value in H is found for many matching ad 1 the the old value stored
Hope i am clear...
here my file: http://www.mytempdir.com/991542