Assigning a Value based on the Count of numbers in a specific Coumn

[daltendavis]

I am currently having to manually count how many times a number appears in a single column ranging from 25-50 rows long. I need a VBA code that allows me to take the numbers in the pasted column and not only count them but apply a value to them. For Example:

Pasted on sheet 1 Column A:
111
222-*333
444-555-777
333-*555

That would be an example of a shortened version of something pasted. I need it to return values like so on sheet 2:

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD].33[/TD]
[/TR]
[TR]
[TD]555[/TD]
[TD].83[/TD]
[/TR]
[TR]
[TD]777[/TD]
[TD].33[/TD]
[/TR]
</tbody>[/TABLE]


Essentially if a number is by itself it represents a single truck, if there are 2 numbers in a single cell it represents 2 stores on a single truck, 3 3 stores on a single truck, using 33% of said truck.

In the set I will be pasting sometimes it reads "OFF" or a string of letters in a cell, I do not need that, also when a store on a truck is split it uses an * in front which is what is seemingly making the count so difficult. If there is any more information I can give feel free to ask. This is a daily dispatch sheet that I receive everyday. An example of an entire column I will paste is as follows:

[TABLE="width: 173"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CLM CHEP[/TD]
[/TR]
[TR]
[TD]CLM CHEP[/TD]
[/TR]
[TR]
[TD]703 - 551[/TD]
[/TR]
[TR]
[TD]925 - 909[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]975 - 153[/TD]
[/TR]
[TR]
[TD]705 - 879[/TD]
[/TR]
[TR]
[TD]1 Pal WC Meats - 849 - 531[/TD]
[/TR]
[TR]
[TD]461 - 502[/TD]
[/TR]
[TR]
[TD]597 - 683[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]021 - 099[/TD]
[/TR]
[TR]
[TD]093 - 386[/TD]
[/TR]
[TR]
[TD]841 - 048[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]118 - 938[/TD]
[/TR]
[TR]
[TD]102 - 407[/TD]
[/TR]
[TR]
[TD]167 - 900[/TD]
[/TR]
[TR]
[TD]657 - 699[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]014 - 019[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]132 - 073[/TD]
[/TR]
[TR]
[TD]*022 - 136 - 164[/TD]
[/TR]
[TR]
[TD]077[/TD]
[/TR]
[TR]
[TD]508[/TD]
[/TR]
[TR]
[TD]WEST ROCK[/TD]
[/TR]
[TR]
[TD]409 - 888[/TD]
[/TR]
[TR]
[TD]719[/TD]
[/TR]
[TR]
[TD]933 - 983[/TD]
[/TR]
[TR]
[TD]395[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]015 - 790[/TD]
[/TR]
[TR]
[TD]049 - 043[/TD]
[/TR]
[TR]
[TD]829 - 022[/TD]
[/TR]
[TR]
[TD]124 - 470[/TD]
[/TR]
[TR]
[TD]850 - 788[/TD]
[/TR]
[TR]
[TD]058 - 912[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]941 - 940[/TD]
[/TR]
[TR]
[TD]137 - 989[/TD]
[/TR]
[TR]
[TD]CLM CHEP



[/TD]
[/TR]
</tbody>[/TABLE]

It varies on a daily basis and sometimes can get up to 70 rows, however is always in a single column. I have a clear macro button set up so the Idea is to put this macro onto a button and essentially paste, take value of what I need on a separate workbook, clear the column, and paste the next day continuing the process.

Thanks in advance

 

[daltendavis]

https://www.mrexcel.com/forum/excel...s-having-multiple-numbers-them-using-vba.html


The solution is second code down on thread: