count unique comma separated values from a single cell in excel

[azad092]

Age	1Y-10Y	11Y-20Y	21Y-30Y	31Y-40Y	41Y-50Y	51Y-60Y	Above 60Y
1,5,14,20,30,45,41	0	0	0	0	0	0	0

i want to calculate different age stages using comma separated value formula but it not works properly
how i can do this...
1,5 should be count in 1Y-10Y , 20 should be count in 11Y-20,30 should be count in 21Y-30Y and 45,41 should be count in 41Y-45Y

 

[azad092]

Could you structure your age ranges at the top like this (make sure they are numbers, not numbers stored as text) and then use this standard-entry formula, copied across and down? This also avoids the use of the volatile function INDIRECT.

Book1
	A	B	C	D	E	F	G	H
1	Age	1	11	21	31	41	51	61
2		10	20	30	40	50	60	99
3	1,5,14,20,30,45,41	2	2	1	0	2	0	0
4	1,2,3,4,89,15,22,23,99,24,25,26,27	4	1	6	0	0	0	2
5	11,11,41,52,79,90	0	2	0	0	1	1	2
6	22,22,40	0	0	2	1	0	0	0
Sheet4

Cell Formulas
Range	Formula
B3:H6	B3	=COUNT(1/(LOOKUP(0+MID(SUBSTITUTE($A3,",",REPT(" ",100)),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A3)))*100-99,100),$B$1:$H$1)=B$1))

Good work
thanks dear

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[azad092]

Could you structure your age ranges at the top like this (make sure they are numbers, not numbers stored as text) and then use this standard-entry formula, copied across and down? This also avoids the use of the volatile function INDIRECT.

Book1
	A	B	C	D	E	F	G	H
1	Age	1	11	21	31	41	51	61
2		10	20	30	40	50	60	99
3	1,5,14,20,30,45,41	2	2	1	0	2	0	0
4	1,2,3,4,89,15,22,23,99,24,25,26,27	4	1	6	0	0	0	2
5	11,11,41,52,79,90	0	2	0	0	1	1	2
6	22,22,40	0	0	2	1	0	0	0
Sheet4

Cell Formulas
Range	Formula
B3:H6	B3	=COUNT(1/(LOOKUP(0+MID(SUBSTITUTE($A3,",",REPT(" ",100)),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A3)))*100-99,100),$B$1:$H$1)=B$1))

hi
is it possible to apply this formula for a specif cell not for a row... i mean i want to apply as cell index instead of row index because with row index the file hanged every time
so please help in this matter thanks

 

[Peter_SSs]

hi
is it possible to apply this formula for a specif cell not for a row... i mean i want to apply as cell index instead of row index because with row index the file hanged every time
so please help in this matter thanks

I don't know what you mean by this.

 

[DanteAmor]

the file hanged every time

If you have performance problems with your file, you may want to think of a macro as an alternative.

Keep the following structure with your data:

Book1
	AL	AM	AN	AO	AP	AQ	AR	AS
1	Age	01-10	11-20	21-30	31-40	41-50	51-60	61-150
2	1,2,11,21,31,32	2	1	1	2
3	11,11,41,52,70,90		2			1	1	2
4	22,22,40			2	1
Sheet

The process with 10,000 records takes 2 seconds with the following macro:

Sub count_unique_comma()
  Dim a As Variant, b As Variant, n As Variant, i As Long, j As Long
  a = Range("AL2", Range("AL" & Rows.Count).End(xlUp)).Value
  b = Application.Transpose(Range("AM1:AS1").Value)
  ReDim c(1 To UBound(a), 1 To UBound(b))
  
  For i = 1 To UBound(a)
    For Each n In Split(a(i, 1), ",")
      For j = 1 To UBound(b)
        If Val(n) >= Val(Split(b(j, 1), "-")(0)) And Val(n) <= Val(Split(b(j, 1), "-")(1)) Then
          c(i, j) = c(i, j) + 1
          Exit For
        End If
      Next
    Next
  Next
  Range("AM2").Resize(UBound(a), UBound(b)).Value = c
End Sub

____________________________________________________________________
HOW TO INSTALL MACROs
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (count_unique_comma) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
____________________________________________________________________

Anyway I attach my file so you can see how it works

Dropbox - File Deleted - Simplify your life

www.dropbox.com