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

here the file
29086202a--4-_4N_DD_COUNT_TO.xlsm
Who is Participating?

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.

Consultant 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
ACCOUNTING 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
Consultant 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.

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

1
ACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

Where do these numbers come from?

here

Thanks for the format idea is great !
in my case i work with 0 leading numbers  like  000    010   001
0
Consultant 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
Consultant and developerCommented:

000;-000;000;@
0
ACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

thanks  im not an computer expert
can you provide  an example ??
0
Consultant 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.

0
ACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

sorry  my problems with language are

how many times each digit repeat  is the correct way to say
0
Consultant 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
Consultant 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
ACCOUNTING ASSISTANTAuthor Commented:
Neil Fleming

You are rigth Expert !
0
Older than dirtCommented:
Try this.
29087072.xlsm
1
ACCOUNTING 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

then

0
Older than dirtCommented:
Do you need the "x"?
1
ACCOUNTING ASSISTANTAuthor Commented:
Martin

Do you need the "x"?

is not needed

can be blank
0
Consultant 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
Older than dirtCommented:
Try this.
29087072a.xlsm
1

Experts Exchange Solution brought to you by

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

Consultant 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
ACCOUNTING 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.