• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting number of cells and placing the value beside it

Jack-P-Winner

Jack-P-Winner

Member
I have several sheets that I have to count manually how many of the same numbers are in a group of cells. I would like to be able to have code to count this automatically as I am human and make mistakes plus it is taking forever since I have almost 500,000 rows to count. I am attaching an example with some detailed info as to how I am doing this and what I need.

Once again, thank you for looking at this and I appreciate your help!

Numbersontheside_zps3ef02bef.jpg
 

Attachments

  • Side Numbers.xlsx
    11.7 KB · Views: 10
Jack,

What happens when you have two sets of number in different columns starting on the same row,

So if you in
Case 1. Row 10 Col C a 5 and in Col E a 3 (both 1st instances),and then you 5 repeated to Row 15 Col C and 3 repeated likewise.
Case 2. As per above but 3 is only repeated to Row 14.

Do you count the 5's or the 3's

Further in your example, you have no result in Row 3 despite the fact that you have 7 three times, 6 four times and 3 twice.

cheers
kanti
 
kchiba said:
Jack,

What happens when you have two sets of number in different columns starting on the same row,

So if you in
Case 1. Row 10 Col C a 5 and in Col E a 3 (both 1st instances),and then you 5 repeated to Row 15 Col C and 3 repeated likewise.
Case 2. As per above but 3 is only repeated to Row 14.

You would have to have 5's and 3's all the way down. The numbers always count up. If you notice starting with the bottom, the numbers all go up




Do you count the 5's or the 3's

Both, you count the groups of cells. Hang on and I will post a video

Further in your example, you have no result in Row 3 despite the fact that you have 7 three times, 6 four times and 3 twice.

There is no result because that's the end of that line. Its hard to explain. I make an input on MASTER sheet which is linked together with several other sheets. When I place a number in that MASTER sheet one of the numbers will go UP. If we take the 5 then the 5 will change to 6 , if we take the 3, it will change to a 4. For what I need this has nothing to do with it. I need these sheets done to help me troubleshoot the MASTER sheets and the ones linked to them. I will actually copy and paste all the other rows of info from all the sheets to one sheet and then let this program rip and hopefully it will do its thing and count the groups of numbers

Did I give you a link to dropbox for the entire file?

cheers
kanti
Click to expand...
 

Attachments

  • Groups of cells.jpg
    Groups of cells.jpg
    160 KB · Views: 10
Last edited:
OK Jack,

So turn my question on it's head, going up from Row 9 you have 5 5's in Column A and 5 3's in Column C so which do you count the 5's or the 3's

upload_2013-11-29_10-51-11.png
 
Ouch! I forgot I pasted all the sheets together. I will have to run these one page at a time and then copy and paste to the MASTER sheet. Those are actually two different pages. If I can get code to say run up to 3000 rows I can make numerous pages or just copy paste, clear contents and start all over again



Video taking forever to upload
 
For all practical purposes this is going to be used more like a tool and is totally separate from the actual function of the numbers. The numbers are dumb on this sheet. No logic behind them whatsoever
 
Jack,

Check out the attached, I have used quite a number of helper cells and VBA.

The top row and the last row may have a problem with the solution.
Check the logic, we can always see about reducing the helper columns and any other corrections/improvements.
 

Attachments

  • Side Numbers_Solution1.xlsm
    25.8 KB · Views: 4
kchiba said:
Jack,

Check out the attached, I have used quite a number of helper cells and VBA.

The top row and the last row may have a problem with the solution.
Check the logic, we can always see about reducing the helper columns and any other corrections/improvements.
Click to expand...
wow! Great start. Row D4 should be an red 8 in one column and a 4 in the other (whatever) and 8D will have the other 4 in the other column (whatever) The 9's and 1's have to treated differently
 
Glad you like it

The 9's and 1's we will deal with last, we can try and set the colour with conditional formatting.
 
Yup that time zone got me last night LOL

I added about 1000 lines and started where the first digit is "3" and I am getting this error

dRows = Selection.CurrentRegion.Rows.Count - 1
For x = 1 To dRows
cCol = Cells(x + sRow, sCol)
Select Case cCol
Case "B"
zz = sCol - 4
dataCol = 2
Case "C"
zz = sCol - 3
dataCol = 3
Case "D"
zz = sCol - 2
dataCol = 4
Case "E"
zz = sCol - 1
dataCol = 5
End Select


https://www.dropbox.com/s/i8yu0qi2pmajq77/Copy of Side Numbers_Solution1-1 2step.xlsm
 
Okay, this file works great except for he 1's and 9's. I also need a couple cosmetic things done LOL. I tried but it gives me an error. I need the game numbers in column A, C1,C2,C3,C4 in BCDE and the location in column F like this below and of course the yellow and gray cells LOL, I did the conditional formatting but it did not go all the way down the rows so I do not know why

https://www.dropbox.com/s/y1dvyytb8r0vt3s/Side Numbers_Solution GOOD.xlsm
 

Attachments

  • 11-27-2013 9-40-53 AM.jpg
    11-27-2013 9-40-53 AM.jpg
    325.8 KB · Views: 7
Please enjoy your weekend off. Spend time with family and friends. Get to it when you have time and please do not feel like I am rushing you with it. I have nothing but time and appreciate your help
 
Hi Jack,

I have uploaded an amended file, you will note that it falls down around Row 104, because we now have Zeroes appearing in the data, how do we deal with this, currently the assumption is that there are no zeroes in the data

I think we need to resolve this before we do anything else.

cheers

kanti
 

Attachments

  • Side Numbers_Solution GOOD.zip
    338 KB · Views: 4
Hi Jack,

Here is a link to the file.

https://dl.dropboxusercontent.com/u/48309468/Side Numbers_Solution GOOD_Rev1.xlsm

So what we have here is the Counting done for all rows, the Conditional Formatting is also in place.

Please note, that the Macro will for some reason "seem" to get stuck. What I have done in this case is hit Control and it runs OK.

We can now look to the other solutions required, starting with the 1 and 9 problem.

I would appreciate a recap of the treatment of this.

cheers

kanti
 
Sorry, I fell asleep last night and didn't gave a chance to see thus until this a.m. Guess after staying up for two night straight will do that lol. I will check this out once I get to my laptop and give you a recap. Again, ANY "0" is an error in my code. And errors in my original code makes a "0" so we can just delete them as if they don't exist.

For the time being. The 9's and 1's. If we have for example (picture this stacked vertically) we have 4 "9"s and then 6 "1"s

1
1
1
1
1
1
9
9
9
9


We would write a total count of 10 in 1st column and then. 6 in column 2 for the six 1's next to the top 1 and. 4 in column 2 next to the 9"s. I will take a pic as soon as I get to my laptop
 
Hi Jack,

here is the link to the updated file. It takes care of the 9's and 1's

https://dl.dropboxusercontent.com/u/48309468/Side Numbers_Solution GOOD_Rev1.xlsm

This was a very interesting problem, however, I am disappointed that I had to use so many helper columns, but I think that columns in Excel are no longer a premium and the solution is more visible so more easier to work with.

BTW, I hope you are not working on counting cards or something as dangerous

cheers

kanti
 
hmmm, counting cards LOL, Have you seen 21? great movies but I do not want to end up six feet under. I will check this out thanks
 
You must log in or register to reply here.
Back
Top