LinkBack URL
About LinkBacks
Hello There,
I have a big excel sheet with data in many cells across rows and columns:
X Y Z
X X Z
T V V
Z V V
What i wanted to do is generate a list of cells with count as
V 4
Z 3
X 3
T 1
Y 1
Can someone point me in the right direction, thanks.
Hello There,
I have a big excel sheet with data in many cells across rows and columns:
X Y Z
X X Z
T V V
Z V V
What i wanted to do is generate a list of cells with count as
V 4
Z 3
X 3
T 1
Y 1
Can someone point me in the right direction, thanks.
Let your range of data is in sheet1
the results will end in sheet2
mind the starting row in sheet1
Please Login or Register to view this content.
Thanks RCM this is exactly what i was thinking. I am facing issue though, i think your script is only counting the first three columns, i want to make it so it counts all where there is text, as i have some rows with 10 columns some with 16, some with 25 etc
Last edited by ginjack; 07-03-2016 at 05:15 PM.
Dear ginjack:
The inner loop scoops data until there is none so there can be variable number of columns
look at sample with variable number of columns
I think that this will do what you want
Please Login or Register to view this content.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Thanks RCM, i tried. script works as expected, but the values are incorrect.
For example, if i search for the same keyword in excel, i get only 2 results
but the script counted it as 79 results.
I thought maybe script is picking up entries around it, but no number is near to the result i got by manually testing (2)
Is there something that i am overlooking?
Thanks Mike, i tried running the script but it keeps crashing my excel, i have about 1million cells of data, and a strong laptop but i guess it isn't able to process it.Originally Posted by mikerickson
I think that this will do what you want
Please Login or Register to view this content.
RCM's solution works, but is giving me incorrect number counts
Hi RCM, i have attached my output for sample, please check 1st entry, count shows there are 5, but there are only 2 results in Excel sheet.
i think somewhere, calculation is giving an error
Here is the sub. It works on the sample. could you send a larger sample?
Please Login or Register to view this content.
Ok I improved it and came up with this version
This should be easier on your computer's memory, but slower.
Please Login or Register to view this content.
Thanks Mike, but its still crashing excel rightaway. It processes for about 30 seconds and becomes non responding and closes.
-
RCM, your script works fine and runs completely, just the count that it gives is incorrect, i've testing it multiple times my batch and smaller batches, but it's still giving wrong numbers.
The error is due to, some of my fields have + in them. for example 20+ Green, 15+ Red, and i think that is breaking the script, as script adding that number multiple times. Could that be possible?
Last edited by ginjack; 07-10-2016 at 07:57 PM.
Try this
Please Login or Register to view this content.
Thanks Jindon,Try this
Please Login or Register to view this content.
this worked perfectly, and it did it in about 3 seconds.
The other script codes were taking about 30mins + and still crashing.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks