Creating a Template with Macros

[Cbowman]

So ive decided to create a Excel sheet that would help my business extensively. But i kinda need some help with the programing side. any help is useful. I know what i want to do i just dont know how to get there. lets start with Step1

First i have 4 columns salesmen, Acct, Brand, and a number.

is it possible to write a macro that can organize the sheet by Salesman then by accts of the particular salesman.(any order is fine) as long as it shows all the salesman together then each of their accts together and their brands together

Attached is a sheet with columns A-D being what people send me. it is usually a jumbled mess. and columns H-K what i want it to look like.

Just to be clear i know how to use the filter feature but can u write a macro that will do it for you??

Attached step1.xlsx

[smuzoen]

This should do what you are after

Please Login or Register  to view this content.

See attached workbook

[Cbowman]

That works perfect. i actually ran it on my master sheet and it did all 50+ salesmen. now Step 2.
can you write code to go thru and create an array with each name that is listed.

for example on a smaller scale.. i have 7 salesman (these salesman Change so its never the same names) can something go through column A and pick every name under Salesmen in "Sheet1" and create a list on the "Sheet 2" without duplicates ??

Example step2.xlsx

[Cbowman]

i would also like this to be written in macro but something like this

Name = "NULL"
start at A2 go to A" and everytime there is a name change paste it in Sheet 2 (unless the name is already there).

so it will run thru the whole list and spit out just a salesman list on the next sheet.

[smuzoen]

To create a unique list of salesman on sheet2 starting at A2 you can use

Please Login or Register  to view this content.

i would also like this to be written in macro but something like this
Name = "NULL"

I am not really sure what you mean - try the above code and if something additional is required just perhaps be very specific when you explain it. Make sure too that you run this on dummy data first so you are sure it does what you require as you cannot undo what macro's do.

[smuzoen]

Please note I have changed the code for the Unique salesman list - use the amended code above so the list when generated will always start at Row 2.

[Cbowman]

im taking a look at your code right now. i actually wrote my own code that seems to work but it doesnt have an ERROR option. soo let me take a look at your then ill let you know. here my code

Please Login or Register  to view this content.

[smuzoen]

You will find that it is a little more efficient to dynamically find the last row in a range rather than picking a large number to use as the boundary of the range to check. With your code you have to cycle through each cell multiple times however I do understand your logic and it should do what you are after. You may find my code a little faster however there is a quote "There is your way, there is my way, as for the right way, the only way, it does not exist". It is great to see people having a go coding their logic to complete a task rather than just asking for an answer so you should be congratulated.

[Cbowman]

ok so both ways work and im using your way just to make things simpler.. now ive been able to organize my list then create a separate list with just my salesman. on Sheet2.. my next step is probably the hardest..
i want to input 3 formulas Next to each salesman name on "Sheet2" the first isnt hard i created a =countIF(Sheet1!$A$2:$A$6000,$A2)
what it does it gathers how many times the salesman pops up in the first list.. this counts his placements.

is there a better way to count A2:A6000?? like u said yours is faster.. bc mine is checking empty space? so what would i put just to check the cells that have stuff in them in column A.

[Cbowman]

ill work on the next formula while you answer that

[smuzoen]

To place the formula use

Please Login or Register  to view this content.

[Cbowman]

Thats perfect. but how can we add a 2nd parameter to it??

i want the 2 formula to count the same thing but this time only count the ones that have a number Greater then "0" in column D

the first formula counts how many placements then the 2nd counts how many of them are "< 0"

[smuzoen]

Change the last lot of code to

Please Login or Register  to view this content.

[Cbowman]

awesome Thank you for your Help Smuzoen

can someone show me how to write a macro that checks every row and deletes a row that has a Certain value in one of its cells and