The fastest way to Distribute new Values in Table

**toci** · 03-15-2016, 03:02 PM

Hi folks,

In attachment you can find very big table with Data. In Columns H:S I have Number Values which I get from Server. I am tracking this Values but my problem is....

Often, I get info that I need to increase some Values for 10.000 or 20.000. Doesn't matter.

E.G. I've situation that I need to split 10.000 in April 2016 based on percentage in column G with next filters:

Column A (Country) - EU
Column B (Ter.) - EU403
Column C (Mark) - B
Column D (Division) - Financial
Column E (Type) - FIRST
Column F (Type Name) - First3 or it could be combination (First3 and First13)

So, what is the best or easier way to do this.

I've tried with some Pivot Table but without success or maybe I was on wrong way with my Pivot Table.

Is it possible to create macro for this or Query in Access....whatever, just to work

Thank you in advance.

**ChemistB** · 03-15-2016, 03:16 PM

So you're asking that 10 be divided among the remaining rows (with the filters you quoted, there are 3 rows) based on their relevant weights (Col G) and added to what is already in Col K (April 2016)?

**toci** · 03-15-2016, 03:35 PM

You are right, in this case there are only 3 rows that I need to populate and it's not 10 but 10 000.
So, for these 3 rows in Column K, cell K3087 (April 2016) I should have formula = 10+(10 000 * 0.97) and so on...
In my original file which is almost 10 MB I have for this filters almost 50 rows and as you know I can upload here only 1mb.
Also I have situation e.g. when I need to include 2 months...

**ChemistB** · 03-15-2016, 03:53 PM

Okay, using this example, it would be
10+10000*0.97%/3.91%
3.91 is the sum of the unfiltered weighted values

Here's my suggestion,
Step 1. Insert a column before the dates, For the header, use 1 as a placeholder
=Table_NAMER11[[#Headers],[10.00]]*[@[Rel Weight]]/SUBTOTAL(9,[Rel Weight]) Format Col H as Number with 0 decimals

Step 2
Filter what you need to filter, place the value you want into header H (change 1 to 10000)
You shoulld get 3 rows that add up to 10000 (with negligible differences due to rounding)

Step 3
Copy these three cells and select the cells in L (Apr -16)
Paste Special > Select "Add" AND "Values" Hit "OK"

Done

**ChemistB** · 03-15-2016, 03:54 PM

Step 1 is a one tiime event. Once it's in place, it can stay there. It doesn't hurt anything.

**toci** · 03-15-2016, 04:16 PM

Sorry, but I am very confused now. How did you get these numbers?
As I can see in cell H3087 is 2488. So =2488+10(from L3087)= 2498 and you have 2501?
The SUM for L3087+L4866+L4892 is 12 401??

I downloaded your file and in Column H I have % Values, is this correct?
How did you create this formula?

Sorry again, but I can't catch you.

P.S. I am not able to copy the formula from step 1. Where I need to put it?

**toci** · 03-15-2016, 04:39 PM

If you can explain me this once more again, I would appreciate it.
As I said, don't know how to generate and where to put this formula. When I try to insert it in cell H2 I get some kind of error.
Second, when I get the numbers in Column H (inserted) I must SUM that numbers with numbers in Column L for April 2016.

**ChemistB** · 03-15-2016, 04:43 PM

Okay, probably messed some numbers up while experimenting. However, I do see now that if I try to copy paste ADD and VALUES at the same time, it doesn't work properly.

It does work if you just do Paste Special > Add
That adds the formula into those cells, then you need to copy>Paste Special> Values on top of itself to remove formula

**toci** · 03-15-2016, 05:13 PM

Ohhhh, finally I figured out.

Thank you very much!

**toci** · 03-15-2016, 05:29 PM

Once more question regarding Copy--->Paste Special--->Add

When I do this it doesn't copy the data on the same (filtered) cells? It ADD values into filtered cells, how's that?

Thanks.

**toci** · 03-15-2016, 05:39 PM

Can you check this example for Copy--->Add

Filtered cells are the BIG problem!

Is there any solution for this?

**ChemistB** · 03-16-2016, 08:26 AM

Ahhhh, I see the problem. The only solution I can think of is to do that one row at a time which I think you said would not be viable. I'm thinking now that VBA will be necessary. I'll ask for some of the VBA experts to take a look.

**toci** · 03-16-2016, 09:21 AM

Thank you, again.

**toci** · 03-16-2016, 06:49 PM

Do we have any progress regarding VBA?

**xladept** · 03-17-2016, 12:06 PM

Try this:

Please Login or Register  to view this content.

**toci** · 03-17-2016, 12:33 PM

Originally Posted by xladept

Try this:

Please Login or Register  to view this content.

Hi #xladept,

Thank you for help, but this code is for this example, am I right?
I have a hundreds combination for filter so this won't work if I change my filters.
Also, this line of code

Please Login or Register  to view this content.

10000 is in this Case, but it changes every day...

**xladept** · 03-17-2016, 12:40 PM

Well, I've got to go to work now. How do you want the criteria? Have you a list? Are they on a worksheet? What? Where?

**toci** · 03-17-2016, 12:52 PM

You can download file from post #11, Clear Filters and from A1:F1 are my combinations.
Thanks.

**xladept** · 03-17-2016, 12:54 PM

You could enter the criteria right into the already submitted code or something like this:

Please Login or Register  to view this content.

**toci** · 03-17-2016, 03:48 PM

Sorry man, I don't understand this Code. Can you explain me what this code actually doing?
I thought it's for Copy--->Paste Special--->Add Data over Filtered Cells?

**xladept** · 03-17-2016, 07:23 PM

After the filter, the program just processes the visible rows. What would it copy the amount is calculated? Have you run macros
before?

Here's a corrected version that yields the 2498 etc.

Please Login or Register  to view this content.

**toci** · 03-17-2016, 07:37 PM

When I finished with filtering (Columns: A,B,C,D,E,F), in Column H, cell H1, I put some Values e.g. 10000 and get what I want below in that column. Than, I run macro and it hide all my rows which were visible after filtering ??
So, did I miss something.
And I have run a lot of macros before

Can you check this once more again?

**xladept** · 03-17-2016, 07:44 PM

Post #11 did nothing for me. Am I looking at the same thing you are?

**toci** · 03-17-2016, 07:54 PM

OK.
Please, open this attachment. I've already filtered some data, so the last step is to run a macro, but before you start a macro check into the VBA if I messed something with the code.
As I understood you this macro should show me only visible cells or I am wrong?

**xladept** · 03-17-2016, 08:48 PM

There's no attachment on post #24

But, you're right the macro doesn't reset the filter until the next run.

**toci** · 03-18-2016, 03:10 AM

Sorry for the attachment.
Here it is.

**xladept** · 03-18-2016, 09:49 AM

This works:

Please Login or Register  to view this content.

**toci** · 03-18-2016, 12:17 PM

Yes, this works great, but...

As I can see this macro ADD Values from H to K Column, but what about if I need to put these Values from H into e.g. Column: K, M, P as well?
Is it possible to add MsgBox or something else which will ask in which Column I would like to insert these Values?

**xladept** · 03-18-2016, 07:07 PM

If you're going to manually filter the data we can eliminate a bunch of code:

Please Login or Register  to view this content.

**toci** · 03-18-2016, 07:33 PM

Oooo MAN, congratulations! That's it!

Thank you very much!

I appreciate your work!

**xladept** · 03-18-2016, 07:58 PM

You're welcome and thanks for the rep!

**xladept** · 03-18-2016, 09:29 PM

Here's code if you want to Autofilter:

Please Login or Register  to view this content.

if you want to actually autofilter then just put an apostrophe before

Please Login or Register  to view this content.

**xladept** · 05-19-2016, 10:33 AM

Hi Toci,

With this code:

Please Login or Register  to view this content.

I get:Toci.PNG

**toci** · 05-19-2016, 04:00 PM

Yes, this works excellent!
Thank you, again!

**xladept** · 05-19-2016, 07:24 PM

You're welcome and thanks for the rep!

**toci** · 05-30-2016, 10:24 AM

#xladept Man, don't be angry on me.....

I think this would be the final question.
As you can see in the example, I inserted 12 Columns with formulas. Column H should be related with Column T (Jan-16), column I should be related with Column U (Feb-16) and so on and so forth. So values from column H go to column T, values from column I go to column U and so on.
Is it possible to edit existing macro to do this?

**xladept** · 05-30-2016, 11:44 AM

Hi Toci,

Try this:

Please Login or Register  to view this content.

**toci** · 05-30-2016, 02:00 PM

Doesn't work. I have filtered data. It seems it adds values through filtered rows?

**xladept** · 05-30-2016, 02:17 PM

Hi Toci,

It worked on your sample, have you a different sample?

**toci** · 05-30-2016, 02:27 PM

Hi, no I don't have a different sample.
After macro check the values in last row. It seems that macro doesn't add data. If we look Column H row 5588, it has value 308, and it should go to column T, where I have value 3. 308+3=311 and after macro I have 308?

**xladept** · 05-30-2016, 02:47 PM

Hi Toci,

Try this:

Please Login or Register  to view this content.

**toci** · 05-30-2016, 03:10 PM

the last one is PERFECT! Cheers man!

Thank you!

**xladept** · 05-30-2016, 03:37 PM

You're welcome!

The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Re: The fastest way to Distribute new Values in Table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] fastest vba code to add (many) new rows into a protected table at the same time

[SOLVED] Calculating Fastest time in pivot table

[SOLVED] fastest sorting routine for 2-D array of long values

Bookmarks

Bookmarks

Posting Permissions