Index Match Count

[philb99]

Hi - I had some great code from Mrmmickle1 but I would like further code please

I have put my Drop Box Link

https://www.dropbox.com/s/prmdf2unq4fy5je/dropbox2.xlsx?dl=0

Col E tells me if a case has been received

I have created in Col G, with coded help, all the Case Numbers where No Case - What I would like please in Col J details the Customer Number from Col B Linked directly to the No Case Number in Col G.

The I can report No Case and Customer Number

My spreadsheet could run to 20000+ lines

Thanks

 

[FDibbins]

I am not able (allowed) to access file-hosting sites, so could you paste some sample data here please?

From what I can make out, take a look at using the countifS() function

 

[philb99]

Thanks for the reply - should be able to Click Open after clicking the link - are you saying that doesn't work?

 

[FDibbins]

I am not allowed to access file-hosting sites

 

[philb99]

ok Here is the code I use in Col G =

INDEX($A$2:$A$100, MATCH(0, IF("No Case"=$E$2:$E$100, COUNTIF($G$1:$G1, $A$2:$A$100), ""), 0))

<tbody>
</tbody><colgroup><col></colgroup>

 

[Aladin Akyurek]

ok Here is the code I use in Col G =

INDEX($A$2:$A$100, MATCH(0, IF("No Case"=$E$2:$E$100, COUNTIF($G$1:$G1, $A$2:$A$100), ""), 0))

<tbody>
</tbody>

In G1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$12),IF($E$2:$E$12="no case",MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),1))

In G2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$2:G2)>$G$1,"",INDEX($A$2:$A$100,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$12),IF($E$2:$E$12="no case",MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),ROW($A$2:$A$12)-ROW($A$2)+1),ROWS($G$2:G2))))

 

[thisoldman]

Your INDEX formula in column G is an array formula. When an IF function contains an array operation, Ctrl+Shift+Enter must be used.

Excel 2012
	A	B	C	D	E	F	G	H
1	case number	customer number	Region	Matched Case	case Y/N		No Case -Numbers
2	1	123	N	n/a	No Case		1
3	90	456	S	90	Case Received		50
4	3	789	W	3	Case Received		#N/A
5	50	No Customer Number	E	n/a	No Case		#N/A
6
Sheet2 (2)

Cell Formulas
Range	Formula
E2	=IF(ISNUMBER(D2)=TRUE, "Case Received","No Case")
G2	{=INDEX($A$2:$A$92, MATCH(0, IF("No Case"=$E$2:$E$92, COUNTIF($G$1:$G1, $A$2:$A$92), ""), 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[philb99]

Thanks for the reply thisoldman - I have the array already (You appear to have just copied my existing formulae) what I am looking for is in Col J please

 

[philb99]

Thanks AA - long time. Not sure what it is you are trying to tell me - how you looked at my dropbox - I am after the results in Col J - All of my other formulae I am ok with

 

[Aladin Akyurek]

Thanks AA - long time. Not sure what it is you are trying to tell me - how you looked at my dropbox - I am after the results in Col J - All of my other formulae I am ok with

In J2 enter and copy down:

=IF(ISNUMBER($G2),VLOOKUP($G2,$A$2:$B$12,2,0),"")