Pass array of data to the excel formula.

[anavarathan]

Dear all,

Kindly help me to derive a formula to pass a array of value and return result for each items in the array.

i want to pass array of data from Column C9# to formula in D9 in the filter criteria (CHOOSECOLS(FTR,$J$2)=$C9) and print the result for all cell value from Column C to Column D.


image1.pngimage2.png


Formula in C9:-

Please Login or Register  to view this content.

Formula in D column

Please Login or Register  to view this content.

Thank you

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[anavarathan]

thank you update the sample workbook

[AliGW]

Where have you mocked up your expected results?

Explain in WORDS the LOGIC of what you are trying to do.

Your sample workbook is full of errors - every formula refers to your personal machine. It's of no use to us like that.

e.g.

=COUNT(FILTER(CHOOSECOLS('C:\Users\anavs\Anavarathan\Nokia\NAM\OneWeb\Activity\Support - Mayur\FTR\FTR dashboard.xlsm'!FTR[#Data],D$6),((CHOOSECOLS('C:\Users\anavs\Anavarathan\Nokia\NAM\OneWeb\Activity\Support - Mayur\FTR\FTR dashboard.xlsm'!FTR[#Data],$J$3)=$C$6)*(CHOOSECOLS('C:\Users\anavs\Anavarathan\Nokia\NAM\OneWeb\Activity\Support - Mayur\FTR\FTR dashboard.xlsm'!FTR[#Data],$J$2)=$C9)*(ISNUMBER(SEARCH(IF($D$2="ALL","*",$D$2),CHOOSECOLS('C:\Users\anavs\Anavarathan\Nokia\NAM\OneWeb\Activity\Support - Mayur\FTR\FTR dashboard.xlsm'!FTR[#Data],$G$2))))*(ISNUMBER(SEARCH(IF($D$3="ALL","*",$D$3),CHOOSECOLS('C:\Users\anavs\Anavarathan\Nokia\NAM\OneWeb\Activity\Support - Mayur\FTR\FTR dashboard.xlsm'!FTR[#Data],$G$3))))*(ISNUMBER(SEARCH(IF($D$4="ALL","*",$D$4),CHOOSECOLS('C:\Users\anavs\Anavarathan\Nokia\NAM\OneWeb\Activity\Support - Mayur\FTR\FTR dashboard.xlsm'!FTR[#Data],$G$4)))))))

[anavarathan]

updated workbook

[AliGW]

Useless!

No expected results, no explanation of what you are trying to do (logic).

Sorry - can't help.

[anavarathan]

i want to pass array of data from Column C9# to formula in D9 in the filter criteria (CHOOSECOLS(FTR,$J$2)=$C9) and print the result for all cell value from Column C to Column D.


=COUNT(FILTER(CHOOSECOLS(FTR,D$6),((CHOOSECOLS(FTR,$J$3)=$C$6)*(CHOOSECOLS(FTR,$J$2)=$C9)*(ISNUMBER(SEARCH(IF($D$2="ALL","*",$D$2),CHOOSECOLS(FTR,$G$2))))*(ISNUMBER(SEARCH(IF($D$3="ALL","*",$D$3),CHOOSECOLS(FTR,$G$3))))*(ISNUMBER(SEARCH(IF($D$4="ALL","*",$D$4),CHOOSECOLS(FTR,$G$4)))))))

[AliGW]

One last try: what is the formula meant to be doing? What are your expected results? What's the logic?

Do not simply repeat what you have already said.

[Glenn Kennedy]

Your formula fails at the first step...

CHOOSECOLS(FTR,D$6) column 102 is empty... So even if the rest is correct, it can only EVER return 0.

[anavarathan]

i dont want to drag below formula for very long.

Formula need to be applied/autofilled untill the values in adjacent column C and i want pass each name(values from column C) to be passed to highlighted filter criteria

sorry for the trouble and poor presenting

thanks for your kind support and time
=COUNT(FILTER(CHOOSECOLS(FTR,D$6),((CHOOSECOLS(FTR,$J$3)=$C$6)*(CHOOSECOLS(FTR,$J$2)=$C9)*(ISNUMBER(SEARCH(IF($D$2="ALL","*",$D$2),CHOOSECOLS(FTR,$G$2))))*(ISNUMBER(SEARCH(IF($D$3="ALL","*",$D$3),CHOOSECOLS(FTR,$G$3))))*(ISNUMBER(SEARCH(IF($D$4="ALL","*",$D$4),CHOOSECOLS(FTR,$G$4)))))))

[Glenn Kennedy]

You do not understand me. This bit:
CHOOSECOLS(FTR,D$6)

returns an array of zeros. There is therefore NOTHING to count!!! EITHER column 102 (d$6) which corresponds to VNET TICKET NUMBER, is incorrect, or there have to be SOME values in column 102.

[anavarathan]

Dear that i can understand i will correct it later if any error in that part. please help me to auto fill this formula or you can give any example how to pass range of data to formula and print the result for each cells.

[Glenn Kennedy]

No. YOU put some values in those cells and then manually calculate what the expected answers are.

[anavarathan]

whether can we use let,lambda,array or vstack function to achieve

[AliGW]

Not until you provide some usable sample data with expected results mocked up.