Arrary Formula in VBA or As Count If

[alanhather]

Hi all,

I'm trying to input this array formula in VBA and its filling in the cell C180. Any ideas? Or if better could this be as a countif function?

The sheet5 is called admissons

Sub Array_Formula()


Range("C180").Select
Selection.FormulaArray = "=SUM(--(FREQUENCY(IF('Raw Data Prev Year'!$B:$B<>"",IF('Raw Data Prev Year'!$D:$D=A180,IF('Raw Data Prev Year'!S:S>=$B$167,IF('Raw Data Prev Year'!$S:$S<=$C$167,MATCH('Raw Data Prev Year'!$B:$B,'Raw Data Prev Year'!$B:$B,0))))),ROW('Raw Data Prev Year'!$B:$B)-ROW('Raw Data Prev Year'!$B$1)+1)>0))"


End Sub

Thanks

 

[baitmaster]

I don't understand what you're asking here. What do you mean "filling in the cell C180"? You select that cell then tell your code to write a formula to it, is that not what you want? Is your formula wrong?

 

[alanhather]

I don't understand what you're asking here. What do you mean "filling in the cell C180"? You select that cell then tell your code to write a formula to it, is that not what you want? Is your formula wrong?

Hi - this formula works but as it’s an array it takes a while for excel to calculate it. So I though putting it in to VB to help with the process. If possible I would try to make it as a COUNTIFS formula. I’m trying to count a raw data spreadsheet but don’t wont to count a uniqueness I’d twice if it comes up. Does this make sense?

Thanks

 

[baitmaster]

I'm unclear on exactly what you're trying to calculate as I can't see the data

If it's only the one formula then I can't see it slowing down your file that much by itself, though if you have a load of these I could understand it

I don't think VBA will help speed the calculation up, unless you're using it to switch the calculation on and off, e.g. converting it to a value once calculated and then reinserting the formula as required

Also can't see how a COUNTIFS would be able to take account of the duplication issue, but again there may be a better way depending on how your data is laid out and what exactly you're trying to calculate. I wouldn't rule out the use of a helper column or two either, if it simplifies the steps needed for the formula it could speed things up a bit

Suggest you try and post more info about how your data looks, what you're trying to compare and where

 

[alanhather]

I'm unclear on exactly what you're trying to calculate as I can't see the data

If it's only the one formula then I can't see it slowing down your file that much by itself, though if you have a load of these I could understand it

I don't think VBA will help speed the calculation up, unless you're using it to switch the calculation on and off, e.g. converting it to a value once calculated and then reinserting the formula as required

Also can't see how a COUNTIFS would be able to take account of the duplication issue, but again there may be a better way depending on how your data is laid out and what exactly you're trying to calculate. I wouldn't rule out the use of a helper column or two either, if it simplifies the steps needed for the formula it could speed things up a bit

Suggest you try and post more info about how your data looks, what you're trying to compare and where

Hi sorry,

here is some data so i can show you what im trying to do,

Thanks

[TABLE="class: cms_table, width: 630"]
<tbody>[TR]
[/TR]
[TR]
[TD="class: cms_table_xl65, width: 70"]
A[/TD]
[TD="class: cms_table_xl65, width: 70"]B[/TD]
[TD="class: cms_table_xl65, width: 70"]C[/TD]
[TD="class: cms_table_xl65, width: 70"]D[/TD]
[TD="class: cms_table_xl65, width: 70"]E[/TD]
[TD="class: cms_table_xl65, width: 70"]F[/TD]
[TD="class: cms_table_xl65, width: 70"]G[/TD]
[TD="class: cms_table_xl65, width: 70"]H[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]1[/TD]
[TD="class: cms_table_xl65"]ID[/TD]
[TD="class: cms_table_xl65"]Name[/TD]
[TD="class: cms_table_xl65"]Date[/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"]Name[/TD]
[TD="class: cms_table_xl65"]Start[/TD]
[TD="class: cms_table_xl65"]End[/TD]
[TD="class: cms_table_xl65"]Result[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]2[/TD]
[TD="class: cms_table_xl65"]A112[/TD]
[TD="class: cms_table_xl65"]John[/TD]
[TD="class: cms_table_xl66"]01/01/18[/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"]John[/TD]
[TD="class: cms_table_xl67, align: right"]01/01/18[/TD]
[TD="class: cms_table_xl67, align: right"]30/04/18[/TD]
[TD="class: cms_table_xl65"]2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]3[/TD]
[TD="class: cms_table_xl65"]A112[/TD]
[TD="class: cms_table_xl65"]John[/TD]
[TD="class: cms_table_xl66"]02/01/18[/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]4[/TD]
[TD="class: cms_table_xl65"]A113[/TD]
[TD="class: cms_table_xl65"]John[/TD]
[TD="class: cms_table_xl66"]03/01/18[/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]5[/TD]
[TD="class: cms_table_xl65"]A114[/TD]
[TD="class: cms_table_xl65"]Dave[/TD]
[TD="class: cms_table_xl66"]01/01/18[/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65"]6[/TD]
[TD="class: cms_table_xl65"]A115[/TD]
[TD="class: cms_table_xl65"]Jenny[/TD]
[TD="class: cms_table_xl66"]01/01/18[/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[TD="class: cms_table_xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[baitmaster]

OK, so I guess you have some sort of MIN(IF(...) array formula for the Start date, and corresponding MAX for the end date? Or are these set values that you're using to limit the dates that you're looking in?

Why is the result 2? Is this due to there being 2 IDs for John?

And will you be looking to do just one person at a time (John, this value will vary) or are you going to have a list and calculate all at once?

 

[baitmaster]

I've got a potential alternative formula for you that looks simpler than yours but I don't know if it meets your needs

In your example above it would be
=COUNT(1/FREQUENCY(IF(($C$2:$C$6=F2),RIGHT($B$2:$B$6,3)+0),RIGHT($B$2:$B$6,3)+0))
entered as an array formula.

This formula looks at the ID but needs to see a number rather than a text string. I've extracted a number using
right(ID,3)+1
which may or may not work in all cases. Alternatively you could use a lookup column of some sort which will reduce the array formula further to say
=COUNT(1/FREQUENCY(IF(($C$2:$C$6=F2),$E$2:$E$6),$E$2:$E$6))
where column E is some other way of converting the ID to a unique number

 

[wideboydixon]

Shot in the dark based on your example data:

Book1
	A	B	C	D	E	F	G	H
1	ID	Name	Date		Name	Start	End	Result
2	A112	John	01/01/2018		John	01/01/2018	30/04/2018	2
3	A112	John	02/01/2018
4	A113	John	03/01/2018
5	A114	Dave	01/01/2018
6	A115	Jenny	01/01/2018
Sheet1

Cell Formulas
Range	Formula
H2	{=SUM(IF(($B$2:$B$6=$E2)*($C$2:$C$6>=$F2)*($C$2:$C$6<=$G2),1/COUNTIFS($B$2:$B$6,$E2,$A$2:$A$6,$A$2:$A$6,$C$2:$C$6,">="&$F2,$C$2:$C$6,"<="&$G2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[alanhather]

Thanks for all your help but unfortunately they didn't work. The array formula i posted works fine i was just trying to find a quicker alternative if possible.

Thanks again