LinkBack URL
About LinkBacks
Hello everyone,
I attached herewith a file filled with Countif, sum, or, and. I look for a VBA to get lighter file. Because the file in fact will be filled up to 40,000 rows.
Please anyone can help me.
Thank you so much.
Thanh
Hello everyone,
I attached herewith a file filled with Countif, sum, or, and. I look for a VBA to get lighter file. Because the file in fact will be filled up to 40,000 rows.
Please anyone can help me.
Thank you so much.
Thanh
I might be able to help you, but...
I used a different formula in F30:
=SUMPRODUCT(COUNTIF(F$1:F$27,$A30:$D30))
and got some different numbers. Specifically, G30 was a 9 instead of an 8, and I30 was a 2 instead of a 1. Is my assumption on your intended calculation correct, or do you want the same logic you used to get your numbers?
I understand your first set of data is 40,000 rows. Is it always 40,000?
Are the columns going to be the same width (i.e. 6) or will they be different? Will the data in columns F:K and S:X be the same or could they be different?
What about the data set at A30:D34? Is that provided by the user or is it somehow generated from the data above it? Is it always the same as the data in N30:Q34? Is it always 4 columns and/or 5 rows? Is it always 3 rows below the bottom of your top data and does it always start in the first column?
What is the range of the numbers in the data set? 00 to 99?
Also, why do you have all the numbers as text? The first thing I would do is convert them all to numbers. Any hard reason why you have them as text?
Finally, I don't quite understand your need to go to VBA. With 40,000 rows of data, your file will not be light, and the formulas you are using should be fairly quick and efficient (Excel formulas, unless you are doing array formulas, are generally faster than VBA).
Last edited by Pauleyb; 05-29-2014 at 03:34 PM.
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
Since I'll probably be sleeping when you respond, here is some code that works with your specific example. I made many assumptions (always 6 columns, only checking four numbers per row, my calculated results, etc.), but the number of rows can be different, it just expects the numbers to check against (e.g. A30:D34) to be three rows beneath the base data. I added this as a macro to Sheet1.
Please Login or Register to view this content.
Last edited by Pauleyb; 05-29-2014 at 04:27 PM.
Thank you for your help.Originally Posted by Pauleyb
Since I'll probably be sleeping when you respond, here is some code that works with your specific example. I made many assumptions (always 6 columns, only checking four numbers per row, my calculated results, etc.), but the number of rows can be different, it just expects the numbers to check against (e.g. A30:D34) to be three rows beneath the base data. I added this as a macro to Sheet1.
Please Login or Register to view this content.
It seems that I was not good at previous explanation. Now I enclose a new file that separate into 2 sheets. You can see the formula will give result when the data updated daily (the formula is filled at all cells of the row)
And now I make clear to your questions:
1. I understand your first set of data is 40,000 rows. Is it always 40,000?
- It depend on the weight of file. It can be 40,000 or 30,000 but
2. Are the columns going to be the same width (i.e. 6) or will they be different? Will the data in columns F:K and S:X be the same or could they be different?
- The columns are updated with data daily
3. What about the data set at A30:D34? Is that provided by the user or is it somehow generated from the data above it?
- It is provided by user.
4. Is it always the same as the data in N30:Q34? Is it always 4 columns and/or 5 rows? Is it always 3 rows below the bottom of your top data and does it always start in the first column?
- It is always 4 columns.
9. What is the range of the numbers in the data set? 00 to 99?
- Yes, it is from 00 to 99
10. Also, why do you have all the numbers as text? The first thing I would do is convert them all to numbers. Any hard reason why you have them as text?
- Because I want to have 0 in: 00, 01, 02,..09
11. Finally, I don't quite understand your need to go to VBA. With 40,000 rows of data, your file will not be light, and the formulas you are using should be fairly quick and efficient (Excel formulas, unless you are doing array formulas, are generally faster than VBA).
- Because with Excel formulas, the file is up to 600Mb, and I have to wait for minutes to get result.
Okay, so is the code provided enough to get you started and you can modify to your specifics?
Just some other comments:
I'm still not sure if the 'input' data is always 6 columns. Is a new column added every day? If so, would you only need to calculate the AND/OR for the last column?Are the columns going to be the same width (i.e. 6) or will they be different? Will the data in columns F:K and S:X be the same or could they be different?
- The columns are updated with data daily
I would still change them to numbers instead of text. Then just use the custom format of '00'. It makes calculations so much easier.- Because I want to have 0 in: 00, 01, 02,..09
If you still need help, I can work on it again on Monday.
Thank you for your help.
For the question:
"I'm still not sure if the 'input' data is always 6 columns. Is a new column added every day? If so, would you only need to calculate the AND/OR for the last column?"
My answer is the input is filled daily and it will be more than 6 columns and will be up to the last (IV) column
Create a Module for this spreadsheet and put this code in it:
Please Login or Register to view this content.
Last edited by Pauleyb; 06-04-2014 at 04:35 PM. Reason: Added Application.ScreenUpdating lines
Thanks for the rep bump. Always nice to be acknowledged. Also, I forgot in the code to add:
Application.ScreenUpdating = False
at the top of the code and then
Application.ScreenUpdating = True
at the end.
That should speed it up quite a bit. I updated the code above to include.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks