Jul 27 2020 01:35 AM - edited Jul 27 2020 01:37 AM
i need help for creating a code, Count several variables in the same column.
like 1R, 1B, 1G and so on thank you!
Jul 27 2020 02:33 AM
Jul 28 2020 01:55 AM - edited Jul 28 2020 12:20 PM
@TheAntony , not exactly like that, in the same column same numbers with different letters.
like
1r 1b 1c 1g
2e 2f 2g
then the count will be total 3 times 2
and so on with 3-99999
and than the count will count all the same numbers with different letters.
Jul 28 2020 01:56 AM
Jul 28 2020 05:15 AM
Jul 28 2020 08:23 PM
@killerBee615 , it's hard to visualize your words. Can you attach a sample worksheet (with all confidential info removed)?
Jul 28 2020 09:33 PM
@killerBee615 i think I understand what you want. you want to know how many different numbers there are in the column if you ignore the letters.
The best I got is if you create a helper column using this formula to strip off the letters:
=LEFT(A1,SUM(--ISNUMBER(--MID(A1,{1,2,3,4,5},1))))
but that assumes a) a maximum of 99999 (i.e. 5 digits) and b) there are no numbers after the letters start. Other versions of this formula are possible if needed.
Once you have this 'helper' column then you can simply use:
=COUNTA(UNIQUE(B:B))
to count how many unique numbers exist in that helper column.
Jul 29 2020 08:58 AM
@killerBee615 based on this I believe you will always have and only have 1 letter following the product number, which makes the formula much easier. but if that isn't the case, we can still work with that. I used the UNIQUE() formula to produce a list of all the products and then a counts column for each of those product numbers:
=SUMPRODUCT(--IFERROR(LEFT(A:A,LEN(A:A)-1)=C2,0))
see attached
Jul 29 2020 09:17 AM - edited Jul 29 2020 09:19 AM
SolutionIf combine @mtarler formulas in one
=FILTER(IF({1,0},SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1))),
COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?")),
COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?") >0)
Jul 29 2020 11:51 AM
Maybe something very simple ... even if the formulas from the others are much better than these
Nikolino
I know I don't know anything (Socrates)
Jul 30 2020 03:50 AM
Jul 30 2020 04:38 AM
Jul 30 2020 05:59 AM
You may use another range instead of $A$2:$A$1500 or use dynamic range like A2:INDEX(A:A,COUNTA(A:A))
Jul 30 2020 09:57 PM
@Sergei Baklan i meant to the kinds of the number it counts 1-3,i want that it will count 1-100 how can i expand it. the code to hard for to understand how to do it myself.
thanks for the help
Jul 30 2020 10:00 PM
Jul 31 2020 03:26 AM
Aug 01 2020 04:43 AM
@Sergei Baklan yes exactly, how did you add the numbers 22, 33 and 100
Jul 29 2020 09:17 AM - edited Jul 29 2020 09:19 AM
SolutionIf combine @mtarler formulas in one
=FILTER(IF({1,0},SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1))),
COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?")),
COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?") >0)