Macro to populate Data from Columns into Rows.

[Pitsere]

Good Day

Please assist me with a Macro that will populate and calculate data that is in Columns into rows.

I have shown an example in the attached spreadsheet on how data is populated and how i want it to display the "Data" tab shows how i receive the data and the "Report" tab shows how i want the macro to populate the data

- The "Report Tab is populated with information on Column A(Division), B(Category) and Column D (Season). the Macro needs to calculate All the Information from Column G onward based on Column A,B & D to Populate information accurately onto the Report Tab.

- The "Report" Tab's Rows are the Same as the Column headings from Column G onward in the "Data" Tab. in essence this is more like we are transposing information, the only difference is that the information is based on the conditions i mentioned about Column A, B & D.

Thanking you in advance

Pitsere

[SamT]

What values do you want in Reports!"I4" and Reports!"AP4"?

Every Accounting Group has two columns on Data, Units and Value

[Pitsere]

Hi SamT

I want to add up each accounting Group's Values... ignore the Units. let me know if you still require further information.

Thanks

[SamT]

I have added a sheet to your workbook. Vba_Constants. If you can maintain that sheet it will make it much easier for me to code what you need.

Some advantages to you in this style of coding is that you never have to touch the code to update your accounts. You merely have to keep the general format of the Data sheet and the Report sheet. You can change any parameter you find on the Vba_Constants sheet. You can add accounts, divisions, and categories. You can move Rows on the Report Sheet and Columns and Rows on the Data Sheet.

It also makes it possible to perform the math in VBA. You may prefer to do the math yourself.

With VBA, I would be doing matrix math which limits the Total Area of the report sheet (AI to AQ) to being the sums of the same relative cells in each of the Division Areas.

Since the code I would write requires these areas, (3 Divisions + Total,) to be identical, this is a viable method.

I rearranged the Data Sheet, but this is not a problem. You can change your to match mine, or not. Just let me know which you prefer.

It is important that the names on all three sheets be identical except for the word "Value" which I can add programmatically.

[Pitsere]

Hi SamT

I need the Data Sheet to remain as it is:
Just to explain to you my reasons:

- I Receive information in the "Data" Tab, exactly in that format as shown on my example. It is normally thousands of records. I am hoping to get a macro that can assist me in Adding up the Values. for each customers. You will notice that on the "Data" Tab, Values are Shown in Columns and on the "Report" Tab values are shown in Rows.

eg. Take "TOTALSPORTS FTW" for instance(Column J on the "Data" Tab) I want a Macro that will automatically populate that value "TOTALSPORTS FTW" on the "Report" tab based on Whether the Value is for "Apparell,FTW or EQP(Column A of the "Data" tab)etc.

Here's a quick heads up:

- column A(Division) on the "Data" Tab links up with Column B1:K1 on the "Report" Tab etc.
-Column B (Category on the "Data" Tab links up with Column B1:K1 on the "Report" Tab etc.

Please see attached my Updated Spreadsheet where i did an example of how the Macro Must Populate.

I hope this Helps

Thanks

[SamT]

I need the Data Sheet to remain as it is:

Thank you, I needed to know that.

Since you don't mention it, I will assume that the Vba_Constants sheet is good to go an will get started.

[SamT]

Pitsere,

I must know if you want the macro to calculate the totals and paste them into the Total area, (Columns "AI" to "AR",) on the Report Sheet?

OR

Do you want me to leave the Total Aeea for you to calculate with formulas?

You must pick one.

[Pitsere]

Hi SamT

You Don't have to calculate the totals.it is a nice to have but not important at this stage.

Thanks

[Pitsere]

Hi SamT

I have Replied to the Forum

Apologies for the delayed response.

Thanks

[Pitsere]

Hi SamT

You Don't have to calculate the totals.it is a nice to have but not important at this stage.

Thanks

[SamT]

Pitsere,

Sorry to be so long geting back, I've been busy.

Anyway, I have a little homework for you. Read the instructions sheet and finish filling out the Constants sheet for me, please.

[Pitsere]

Hi SamT

Apologies for the Delayed response. I have completed the Constants Sheet as per Instructions

Please see the sheet attached

Thanks