Excel Arrays VBA

[rohankekatpure1987]

HI,

In my current assignment I need to develop a comma separated string based on the below example table:

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]S[/TD]
[TD="width: 64, align: center"]M[/TD]
[TD="width: 64, align: center"]T[/TD]
[TD="width: 64, align: center"]W[/TD]
[TD="width: 64, align: center"]Th[/TD]
[TD="width: 64, align: center"]F[/TD]
[TD="width: 64, align: center"]S[/TD]
[TD="width: 64, align: center"]Output[/TD]
[/TR]
[TR]
[TD="class: xl85, align: center"]A[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="class: xl84, align: center"]1,3,4,5,7[/TD]
[/TR]
[TR]
[TD="class: xl85, align: center"]B[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="class: xl84, align: center"]2,3,5,7[/TD]
[/TR]
[TR]
[TD="class: xl85, align: center"]C[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="class: xl84, align: center"]1,2,4,6[/TD]
[/TR]
[TR]
[TD="class: xl85, align: center"]D[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="class: xl84, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl85, align: center"]E[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="class: xl84, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl85, align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="class: xl84, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl85, align: center"]G[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="class: xl84, align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

A,B...G rows and S,M,T,W,Th,F,S are columns. My data has either 1 or zero.
For each row I need to check at what position zero is present. In row A, 0 is present at S,T,W,Th and S so output is position 1,2,4,5,7.
Can some one please help in generating a comma separated string in Output using VBA or excel formulas.

Thanks!!
Rk

 

[Ombir]

By my testing, all the recent Join(Filter.. functions are at least 3 times slower than the pos() function from post #12.

snb_'s functions only return the correct position in the array if the array begins in column A, which I don't think we have been given information about.

My apology Peter,

You're right. I tested Sub Procedure for 5000 rows and its around 30 times slower than yours. Further, its the slowest of all approaches.

 

[Peter_SSs]

@P_S

Why did you assume otherwise ? No information on that either.
When no specific information provided I assume the obvious (default).

I didn't assume otherwise, but I did allow for the possibility. Not too different to you allowing for the possibility that the number of columns might not always be 7.
What is obvious depends on the viewer. What was obvious to me was that without column or row information, the data could be anywhere on the sheet.
I also didn't suggest your functions were wrong, just pointed out a difference with the other codes that had been discussed in the thread.

 

[wideboydixon]

This simple requirement turned in to a very interesting topic! Personally, I'd go for readability and maintainability over speed or "cleverness" unless I thought that the formula/UDF would be invoked thousands of times and performance was key. In future, someone coming across a convoluted Join/Evaluate UDF might take twice as long to adapt/fix the code as one that is readable and logical. Too many year of unscrambling spaghetti code has made me a cynic

WBD

 

[rohankekatpure1987]

Thanks for your answers team. Really appreciate it. trying to run this in my schema