Forum Discussion
Hey!
If 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)
SergeiBaklan yes exactly, how did you add the numbers 22, 33 and 100
Did you check file in my previous post? There are 3 variants of how to return data. All of them return count or count & code in sync with column D
SergeiBaklan as you can see when i put 7 in A it will appear in k at line 5 and what i meant is
i want it will appear at line 9 where is write 7 at line d
so what i want is that when i write any number it will be according the number that appear at line d and it doesn't matter if there is a number before him or not
when i put first 5 in column A for example the code in column k will be in in the row in 5 in column D and it doesn't matter if put 3 or 2 before it will be in column K 6 count maybe there is a way we can talk in zoom or skype to communicate and it will be much easier?
Thank you for the file, but that's still not clear for me. If in column D are codes you added manually, e.g. 5,3,6 and in column A are all codes, e.g. from 1 to 100, when would you like to count only for the codes in D (5,3,6) or it shall be counted all codes and place in order as 5,3,6,1,2,4,7,8, etc. till 100?
SergeiBaklan hey this what i built and the code for
its from right to left.
as you can see in the right i put the codes. after that i have all the codes numbers for the products that i will know what code belongs to what name. and after the name i have your code that you built.
so what i need is it will be always in your code from 1 to the big number because i start from 0 data
and the some times it will be first the code 40 and than 2 and than 30 for example. so when i put 30 it will be in your code at number 30 where is my code number for the product and i wont need to do 1-43 before.
Let me clarify ones more.
- Do we speak about the codes in column D which you add manually and don't care about columns A, we take it as it is only to calculate the count?
- in your previous post "could it be when ill write number 7 it will be in line 7 and i won't need to write before 1-6?" Does that mean if in column D you have only codes 1 and 7, result shall be returned sorted by codes with empty rows between 1 and 7. E,g, if D8 = 1 and D9 = 7 we return F8=1,F9="",...F14=7 plus counts in next column?
In latest post requirement perhaps bit different. it says if you entered codes as on the left, result shall be as on the right two columns
In addition, does that all mean we count only codes added to column D and ignore all other unique codes in column A? Initial task was to count ALL unique codes.
Perhaps you may manually generate the sample which result you'd like to have.
SergeiBaklan thanks, but its not precisely what i meant.
if i add code let's say 1 5 7 and than 4 and 6
i want that in the table it will be 1 4 5 6 7 and not 1 4 6 5 7
sorry for the trouble that i am doing to you and thank you so much for the patience.
That looks like another task. If you'd like to count the number of codes in the left column
when like
=COUNTIF($A$2:$A$1600,$D$8:INDEX($D$8:$D$1000,COUNTA($D$8:$D$1000))&"?")Perhaps you sort as texts, not as numbers. Did you keep double dash in formula for your file?
If something else could you please illustrate on the sample.
SergeiBaklan sorry for the interruption. but in the code number if i write 1 and then 100 so the 100 will be in the next line. could it be when ill write number 7 it will be in line 7 and i won't need to write before 1-6?
killerBee615 , glad to help
SergeiBaklan thank you very much for the help!!!!
We assume there is only one letter at very right of each text. Thus the number is extracted like
=--LEFT(A1,LEN(A1)-1). We only apply this pattern to the range. Initially formula was exactly the same, I only changed the source data sample.