Count data in one Column 4N DD 2-1

count numbers from column GU and GV
then show together in column HC  and the count in HD


as show here
a1.PNG
here the file
29086202a--4-_4N_DD_COUNT_TO.xlsm
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Try this.
29087072a.xlsm
1
 
Neil FlemingIndependent consultantCommented:
Hi Adriana.. it's not clear quite what you mean above. What do the numbers in HC, HD and HF actually represent? Count of what?

Note also that many of the numbers in your workbook are formatted as "text".
1
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

Thanks for the fast response

What do the numbers in HC, HD and HF actually represent? Count of what?


like i say  column  GU and column GW

Note also that many of the numbers in your workbook are formatted as "text".

needed because  have to work with 0
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Neil FlemingIndependent consultantCommented:
Still not clear, I'm afraid. In HF at the top you have 12,128, 283. Where do these numbers come from? See pic.

count-problem.jpg
re the text formatting, it might be better to use a "custom" number format, such as:

0;-0;0;@

The format specifies how to display positive numbers, negative numbers, zeros, and text, in that order, with the semi-colon between each. The "@" just means represent the text as text. A blank cell will remain blank, but a cell containing a zero will show as a zero.

  • select cells to format
  • press Ctrl-1 to open format menu
  • select Number tab
  • select "custom"
  • type as shown

custom-number-format.jpg
1
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

Where do these numbers come from?

here
a2.PNG

Thanks for the format idea is great !
in my case i work with 0 leading numbers  like  000    010   001
0
 
Neil FlemingIndependent consultantCommented:
That vertical line in my format example above is just the cursor, by the way. You should not type a vertical line...
1
 
Neil FlemingIndependent consultantCommented:
for leading zeros, change your number format to:

000;-000;000;@
0
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

thanks  im not an computer expert
can you provide  an example ??
0
 
Neil FlemingIndependent consultantCommented:
Sorry.. I am maybe being stupid here. I still don't know where the numbers 12, 128 and 283 come from.

You want to count the digits in the numbers in GU and GW. If we do that, the answer is 9, not 12. Then it is not clear what 128 and 283 are.

count-problem.jpg
0
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

sorry  my problems with language are

how many times each digit repeat  is the correct way to say
0
 
Neil FlemingIndependent consultantCommented:
hmm.. what languages do you speak?  Can you explain in French, German or Italian?

If I understand correctly, column HC contains ALL the digits which appear in GU and GV, ranked in order with the highest number of appearances first, and then ordered by digit.

Column HD contains the number of times each digit appears

Column HF... I still don't understand...
0
 
Neil FlemingIndependent consultantCommented:
Ah wait.. maybe I do. Column HF is just the first two digits in order of frequency, then the first three, and then digits 2-4?
1
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

You are rigth Expert !
0
 
Martin LissOlder than dirtCommented:
Try this.
29087072.xlsm
1
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

Have an 2 situation

when no data is in  i used to put an x
but it still some numbers

like here show
b44444444444444444444.PNG

then


bbbbbb55555555555555555555555555555.PNG
0
 
Martin LissOlder than dirtCommented:
Do you need the "x"?
1
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

Do you need the "x"?

is not needed

can be blank
0
 
Neil FlemingConnect With a Mentor Independent consultantCommented:
I haven't looked at Martin's solution, but this is mine.

I created a user function that retrieves the needed data.

The function takes the form

=digitdata($HB5,1,FALSE,$GU:$GW)

  • The first term passed is the rank of the data (can be a digit, eg 1, or a cell reference as here to the ranking in $HB5)
  • The second term is the number of digits to display
  • The third can be TRUE or FALSE. If TRUE the function displays the count of digits, if FALSE it displays the digits itself
  • The last term in the function is the address of the columns containing the data

I have inserted this formula in the first few hundred rows of the worksheet called "Using UDF" in the file attached. It seems to match what you want.

It also handles the "x"s and blank data, I think.
NF-29086202a--4-_4N_DD_COUNT_TO.xlsm
1
 
Neil FlemingIndependent consultantCommented:
Martin's solution may work better than my user-function, since it is a one-button one-time piece of code.

There is a danger that if data changes a lot, my function will slow your workbook down.

However, it has the benefit of automatically changing the rankings etc if you change any of the numbers in GU:GW.
1
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Great JoB !

Best Experts     Martin Liss    ,      Neil Fleming
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.