Create a Table from data set

[lacke81c]

Hello,

I compile a routine report from the data in the attached spreadsheet. I’m hoping to write some code to streamline this process.

In the attached workbook, there are three spreadsheets.

(1) In the first spreadsheet “Before” is the data I receive.

(2) In the “After” spreadsheet, I removed the Event/Date Time, Last Name, First Name columns, and totaled the number of times each FC4 code was counted in the “Before” spreadsheet. (I use 'subtotal' count, and then only leave the 'total' line for the subtotal, and delete other entries)

(3) I then use the “After" spreadsheet to create a Pivot Table (the final spreadsheet). That reports the City (Column A) as the Row Label, Count of FC4 (Column B) and Sum of Number (Column C).

I do not know if Pivot Tables can be commanded from VBA but if its, possible that would be ideal!
Any ideas to help streamline this process would be appreciated as going from the “Before” to “After” is quite a few steps (counting using subtotals, then removing rows that don’t include that count, then cutting out columns).

Thank you!

[JieJenn]

Well. Not pretty, but this macro should automate the data to the state where you can perform the pivot table. I used a helper column to do the Sum on the pivot table just an FYI.

PS: Copy your data to the Worksheet named "In" first before run the macro.

[lacke81c]

Hi JieJenn,

The code is deleting the columns A,B,C and duplicate entries well.

However, the Helper Column is full of '0's (instead of the subtotal counts) when I run the code. Any ideas on this?

Thank you for your help!

[JieJenn]

Did you copy your raw data to the "In" tab first? Basically what I did was based on your bullet #2, except I use SumIF to sum the total first then remove the dupes.

(2) In the “After” spreadsheet, I removed the Event/Date Time, Last Name, First Name columns, and totaled the number of times each FC4 code was counted in the “Before” spreadsheet. (I use 'subtotal' count, and then only leave the 'total' line for the subtotal, and delete other entries)

[lacke81c]

I copied the data into the 'In' spreadsheet first and then ran the code. The 'helper column' is all zeros though. Hmmm...

Thank you!

[JieJenn]

Can you attach your file?

[lacke81c]

Attached is the latest workbook, with the Helper Column with 0's.

Thanks.

[JieJenn]

Oh. Didn't realize your column E was formatted as Text. Here, try

Please Login or Register  to view this content.

[lacke81c]

Great! It is working and all of the counts are being totaled in the helper column. Thank you.

Now, I will just have to create the pivot table. (Unless you know of a way to command the pivot table with code?)

Thank you so much, this is a huge improvement!