Create a Table from data set

**lacke81c** · 05-11-2015, 12:50 PM

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** · 05-11-2015, 01:13 PM

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** · 05-11-2015, 02:01 PM

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** · 05-11-2015, 02:10 PM

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** · 05-11-2015, 02:45 PM

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** · 05-11-2015, 03:11 PM

Can you attach your file?

**lacke81c** · 05-11-2015, 04:02 PM

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

Thanks.

**JieJenn** · 05-11-2015, 04:11 PM

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

Sub GenReport()
Dim wsIn As Worksheet, wsOut As Worksheet
Dim LR As Long

Set wsIn = Worksheets("In")
With wsIn
LR = .Cells(Rows.Count, 1).End(xlUp).Row
    .Columns("A:C").Delete
    
    .Columns("E").TextToColumns
    
    .Range("F2").Formula = "=SUMIF($D$2:$D$" & LR & ",D2,$E$2:$E$" & LR & ")"
    .Range("F2").AutoFill .Range("F2:F" & LR)
    .Columns("F").Copy
    .Columns("F").PasteSpecial xlValues
    
    .Range("F1").Value = "Helper Column"
    .Range("A1:F" & LR).RemoveDuplicates 4, xlYes
End With

End Sub

**lacke81c** · 05-12-2015, 06:20 PM

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!

Create a Table from data set

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Create a Table from data set

Re: Create a Table from data set

Re: Create a Table from data set

Re: Create a Table from data set

Re: Create a Table from data set

Re: Create a Table from data set

Re: Create a Table from data set

Re: Create a Table from data set

Re: Create a Table from data set

Thread Information

Users Browsing this Thread

Similar Threads

Automatically create new data table based on new table record

Macro to create a statistics table from another data table (containing merged cells)

Using pivot table data and other data to create another table

how to create a table given data...

How do I create a one variable data table?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions