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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
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
Neil FlemingConsultant and developerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 FlemingConsultant and developerCommented:
That vertical line in my format example above is just the cursor, by the way. You should not type a vertical line...
1
Neil FlemingConsultant and developerCommented:
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 FlemingConsultant and developerCommented:
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 FlemingConsultant and developerCommented:
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 FlemingConsultant and developerCommented:
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 FlemingConsultant and developerCommented:
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
Martin LissOlder than dirtCommented:
Try this.
29087072a.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Neil FlemingConsultant and developerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.