Excel Macro help

Registration date
Thursday March 26, 2009
Last seen
March 27, 2009
 WutUp WutUp -
I am new, and know next to nothing about Macros in excel, and I was wondering if anyone could help me out.

I do a report every week, and it is extremely complicated, but I don't think it has to be that way.
What the report consits of is something like this....
I copy a table from microsoft access, and paste into excel. From there I am supposed to seperate certain information. Lets use cars as examples.
I would have an excel sheet that looked something like this

VW 09
VW 08
VW 09
VW 06
VW 07
Chevy 02
Chevy 04
Ford 08
Ford 07
Ford 07

Now let's say that I wanted to only see the count of each by its variation. Like
There are
2 09 VW's
1 08 VW's
1 07 VW's
1 06 VW's
1 02 chevys
1 04 chevys
1 08 fords
2 07 fords

Is there some kind of Macro I could use to help me do this without having to use the filter to sort by one thing, and count out the other, than start with the next "Car manufacturer" and so on and so forth.

My superiors want charts made of that, and it is a lot more information than above, but the same effect. Any advice and input would be GREATLY appreciated, like you have no idea.

2 replies

Registration date
Monday December 29, 2008
Last seen
June 3, 2009
try this link to get tutorials about using excel macros:
this will help you.
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

This assumes that there is a heading in the first row, and your data you want to filter for unique records is in
column A. This uses the advanced filter to extract the unique records to column J. Then, it will copy and paste
values to column K and delete the "extract" from column J so the countif formula can be used. You said a
chart will need to be made from this information that is why the table will be created to the right of the data
within the same spreadsheet. Once the table is set-up, you can create a chart from there. You can change the
ranges (or columns) to suit your spreadsheet.

Hope this helps!

Sub CreateList()

Dim List
List = 2

Range("A2:A2000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J2"), Unique:=True

Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Do Until Range("K" & List) = ""

Range("L" & List) = "=countif(A2:A2000,K" & List & ")"

List = List + 1



End Sub
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month