how to fetch the distict records from xcelsius excel column

[Former Member]

i have brought the datas from database using webservice

from this data i want to fetch the distinct records in another column

how to do this without using third party tool like "Centigon filtered summary"

plz help me.

Regards

Ram.G

Edited by: g.ram84 on Dec 1, 2009 6:57 AM

[former_member192142 · Accepted solution]

Hi,

Can you be a bit more specific? What do you need the distinct values for?

If you use your data in e.g. a List box it will automatically show only the distinct values. If its transaction data that you want to sum, you can use the SUMIF() function of Excel and finally you could create another web service only with the dimension that you would like and hereby get the distinct values. It all depends on what you are trying to do.

Best regards,

Jacob

[Former Member]

Hi,

U can try this formula to extract unique values from a column.

=INDEX(array,MATCH(0,COUNTIF($B$1:B1,array),0))+ CTRL + SHIFT + ENTER

and copy it down as far as necessary.

If the data in column A starts from 2nd row then Place the above formula in 2nd row of B column. B1 is a blank cell.

This will surely work.

Thanks,

Rajesh

[Former Member]

thanks for the reply

i used the formula "=INDEX($A$2:$A$5,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$5),0))" in B column 2nd row & dragged down

and the record is in A column 2nd row onwards

but its showing "#N/A"

[Former Member]

Hi,

After typing the formula in cell B2, press CTRL + SHIFT + ENTER. It will surely work coz i have used it many times.

Thanks,

Rajesh

[Former Member]

@ Rajesh - Array formulae are not supported in Xcelsius.

The best way is to set up another WebService to only return the Unique values you need.

Regards

Charles

[Former Member]

Try to use another WS to get the unique list from the database. There may be some formula which will help your generate unique list. However I am pretty sure that you will end up with some script time out as your list grows.

The best way is to develop a custom component to do this. May be you can give a try of "Crosstab modeler component" available in sap market place. which will generate unique value list & convert the vertical table to cross table/pivot table without writing a single excel formula.

Anil

[Former Member]

Hi Ram,

This can be achieved using functionalities within excel of the Xcelsius.

Please try the steps below:

1) From the excel menu choose Data | Filter | advaced Filter.

2) choose your entire range.

3) choose location to copy.

4) choose Unique records only

and only unique records will be copied.

Hope this helps...

Regards,

Ankeet

[Former Member]

hi Ankeet

this is not fetching dictinct records in the runtime.

thanks for the reply.

Ram.G

Edited by: g.ram84 on Dec 3, 2009 11:10 AM