Excel Macro help [Closed]

Report
Posts
1
Registration date
Thursday March 26, 2009
Status
Member
Last seen
March 27, 2009
-
 WutUp WutUp -
Hello,
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

Posts
15
Registration date
Monday December 29, 2008
Status
Member
Last seen
June 3, 2009
2
hi,
try this link to get tutorials about using excel macros:
https://www.helpwithpcs.com/software/microsoft-excel-macro-tutorial.php
this will help you.
1
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

Columns("J").Select
Selection.Copy
Range("K1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Columns("J").ClearContents
Application.CutCopyMode = False

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

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

List = List + 1

Loop

Range("M2").Select

End Sub
1
Thank you

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

CCM 2942 users have said thank you to us this month

Newsletter

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!