Summarize Data

[rizmomin]

Hello:

Please refer to attached file.
Have lots of purchase data in sheet1

Sheet1:
column D is the purchase date
Column E is the Item#
Column E is Item Description
Column H is the cost per item.

I need a VB Code to summarize these data in Sheet3 with UNIQUE Item # in column A, Its item description in Column B and
Average cost of the item for each month in column D thru O.

Let me know if you have any questions.
Thanks.

Riz

[GC Excel]

How about using a pivot table?
(with some extra calculated fields)

[rizmomin]

Hi GC excel:

Thanks a lot with this solution.
However I am not good at Pivot Table and hence i prefer VB Code. I may be able to use this...Thanks
Riz

[karedog]

Hi,

There are some rows with negative Qtty, such as row 257, 258, 607-610, etc, is this expected or an error typing ?
Also, there is one item at row 28 which not on the same year with others, is this expected ?

Meanwhile, this is the code :

Please Login or Register  to view this content.

Regards

[rizmomin]

Hi karedog:

Seems to work, I will be sure after further testing.
Just need small correction in the code.
The data in column H is Per item, so please correct the code accordingly.
Thanks and Please let me know if you have any questions.
Thanks.

Riz

[karedog]

You are welcome.

Please change this line in previous code :

Please Login or Register  to view this content.

to this :

Please Login or Register  to view this content.

Please let me know if this is not worked as expected.



Regards

[rizmomin]

Hi karedog:

Super work...seems to work great and very fast.
I need one more feature added.
I would have a list of some main Item# and what i want to do is highlight that row in the final output in Sheet2.
Item # of interest would be from cell O2 down in Sheet1.

Thanks a lot
Riz

[karedog]

You are welcome rizmomin, thank you for the reputation points.

This is the modified code as your request.

Please Login or Register  to view this content.

Regards

[rizmomin]

Hi karedog:

Thanks a lot for great help.
Just wanted to clarify that the highlight should go in the final output sheet3 and not sheet1
I need to take this one step further.

Hello:

Please refer to attached revised file.
As mentioned Sheet1 has data of purchase
Column A has Store #
Column D date of Purchase
Column E is Item#
Column G is Qty

I need a VB Code which will give Qty for each store for the item mentioned in cell D3:W3 for each store tab.


Let me know if you have any questions.
Thanks.

Riz

[karedog]

You are welcome.

The code in #8 doesn't highlight anything in sheet1, I do it manually to make you easier spot the pointed items. If you remove all highlighted color in sheet1 and then run the code, you will find that the code does not change anything in this sheet.

This is new code as your request in #9 :

Please Login or Register  to view this content.

Regards

[rizmomin]

Hi karedog:

Very smart and very fast...works like a charm.
Thanks a lot....as i was using formula's to do this and it was taking long time to calculate.
Now it is on the fly...this is why i like VB Code a lot more when you have too much calculation.
Once again, thank you so much and will contact you further if any assistant is needed.

Riz

[karedog]

You are welcome rizmomin,

Yes, that is what the power of kernel mode is, learning programming language is nothing useless at all.

Regards

[rizmomin]

Hello karedog:

Whild testing in my real workbook, it gave an error.
Please refer to attached as part of Real workbook.
Sheet1 is now "Inv_Sum" in my real workbook.
Your macro is called "SortStore" and the code is in "Summary" module.
Please run and see the error.

While doing this, if you can see in column T thru AD, i am using formulas and those takes long time.
Would appreciate if i can get VB Code for that as below:

For each store, i need Date, Inv # and Total Qty Shipped as shown.

Please let me know if you have any questions.
Thanks.

Riz

[karedog]

Hi Riz,

Your data is messy, you should tidy it up a little bit.
For example, sheet 43227 is fine, but for sheet 10687 and 10693 you must delete row 3:6 first (so row 7 become row 3), to make all data uniform.

Here is the code according to your new layout :

Please Login or Register  to view this content.

And this is the code to replace existing functions in columns T:AD :

Please Login or Register  to view this content.

Regards

[rizmomin]

Hi karedog:

Its is strange, the code inserts the 3 rows as you had mentioned.
Please refer to attached file and run the code.
Also in the code for Column T:AD, the code needs to consolidate the result as shown.
Let me know if you have any questions.
Thanks.
Riz

[karedog]

I have no idea what you are talking.

Here is what I do :
- First, I make a copy of each store sheet (10687 --> Copy 10687, etc)
- Then I run the Sub SortStore()
- Then I compare the unmodified original sheet (Copy 10687 etc) which use functions, with the sheet which macro is applied (10687 etc)
- The result is exactly the same

As for columns T:AD, you want to sum them by dates (no breakdown per items), so I create a new sub Test4() for this purpose.

Note :
The copy of original sheets (Sheets with name : Copy ....) will slow down the final process, since they still have so many functions in them. After these sheets are deleted, the result will be much faster. I purposely keep these sheets so you can compare before and after.




Regards

[rizmomin]

Hi karedog:

Seems to work now.
As for column T:AD, what changes in the code i will need to add total which is on column P instead of column G.
Please refer to updated excel file.
(Note: just to let you know that there will be some blank columns between G and P).
Also since i am not going thru VBA and wants to avoid the formulas, i have added 3 sheets
Credit_10687,Credit_10693 and Credit_43227
In these sheets, i need VB code to do as follows:
The data source would be Inv_sum.
if the value in column P is negative AND Item# Column E is NOT blank then copy that row data A:P into appropriate Credit sheet for each store (Column A) starting in row 2.

Please let me know if you have any questions.
Thanks a lot...........Super work.

Riz

[karedog]

Well, it's kind a strange when nothing is changed but the code suddenly begin to work.

As for you new request , I have create a new sub in the attached file.



Regards

[rizmomin]

Hi Karedog:

Thank you so much for such a great help.
This is far better then lots of formula's and it was slowing down.
Love it and will do more testing and will contact you if any further issue or help is needed.

Once again Thanks

Riz

[karedog]

You are welcome, glad I can help.


Regards