New-work-assignment / Sort data

[DavidWaldo]

Hello, first of all I'm very sorry for my English (not a native speaker).
I'm pretty decent with excel and VBA but I hit a wall when it comes with my newest assignment. I have a workbook into which I download data via SQL query every 24 hours and overwrite the old data. So far good and easy. All the data are in a single sheet. Data consists of sales (telephone,adress etc.). Now comes the part I dont know how to do. I want the VBA to sort the data into several groups(depending if a client is a creditworthy) each group into new sheet. I could do this manually every morning but thats just pain.
How would you proceed?
Would you use cycle and go through the data one after one?We are talking about 100 - 200 entries, each having about 40 properties and once the entry is moved to a group(new sheet) its removed from the original sheet. Or is there a better way?I'm also new here, so thanks every one for their answers.

[p45cal]

That depends on how you determine creditworthiness. A straight filter (on a single field?)?
I would firstly strongly consider having three separate SQL queries on the 3 different sheets, let the query do the job and just refresh them every 24 hours.
Otherwise a macro which filters then cuts and pastes to each sheet.

Perhaps some more detail from you?

[DavidWaldo]

Data consists of mostly client names, phones, ordered service, price etc.. many of the clients work for the same company. For example I have 1000 people who buy from me that work for Evil corp., Evil corp. has many branches. The SQL spits out the list of clients my employees have to visit that day. What I want is to sort out all the customers that work for evil corp and work at the same branch, delete them in main sheet and then sort rest of the clients depending on prize of products they buy. Having sheet with clients that spend 100€ - 200€ in one sheet, clients that spend 300€ - 400€ in other sheet. What is the problem for me are the evil corp people. I dont want to have one evil corp person in 100€-200€ cattegory and other in 300€-400€ cattegory. The way i see it, there would be a groups sheet(in wich would be large groups of cust. with same adress and same company) and then 100€ sheet,200€ sheet with cust. that dont belong to a group. I hope you understand... sorry for my enghlish.

[p45cal]

I haven't really understood… but maybe:
You have a set of employees (sales reps perhaps) and a whole bunch of clients. You want to group your clients so that the sales reps make efficient visits; perhaps prioritising those customers who have have spent more money with you in the past, but if there are many clients in one place then have a single rep visit all of them at one site (perhaps also prioritising those who spend most money).
So you group clients (1) by their location,
and then (2) for other locations where there is only one client per location, group them according to creditworthiness?

Could you give us a spreadsheet with raw data from the SQL without anything having been deleted, then it would also help to provide other sheets where you have manually placed the correct rows. Then it will be very clear what you want.

This sort of thing is perfect for automation, but I haven't yet given up hope of doing it through SQL on the different sheets.

By the way, sheets like:
1. All locations with more than one person, regardless of creditworthiness
2. Single clients-at-a-single-location spending 0-100 euro
3. Single clients-at-a-single-location spending 110-200 euro
4. Single clients-at-a-single-location spending 200-300 euro
5. Single clients-at-a-single-location spending 300-400 euro
6. Single clients-at-a-single-location spending more than 400 euro

??

[DavidWaldo]

Yes, thats exatly what i ment. I dont think i can squeeze more from SQL. Second thing I really dont want to give out data about customers on public site, it would be unethical. For me is the problem how to sort the data and move them, I mean sorting by money thats easy, i would just cycle through single cell with IF inside it. But the sorting the data on similarities, thats what i dont know how to do.

[p45cal]

Yes, thats exatly what i ment.Excellent!





I dont think i can squeeze more from SQL.You may not be able to, but others might.
You could find the SQL used and quote it here - it might be useful if it's not just SELECT * FROM nnnn! I'm also certain an expert SQL person could give you what you want. I'm not a SQL expert.





Second thing I really dont want to give out data about customers on public site, it would be unethical.There are two things that you can do here.
1. Do some data 'sanitisation'; which translates to doing some searching-and-replacing on the whole sheet of raw data to disguise people's names, company names, addresses and so on. Don't do this wholesale so that everyone has the same names and same address and works for ther same company - that would be pointless. It needs to be done so that what's left is realistic data which can be experimented upon and processed in a similar way to what you're hoping to do. This does require some work on your part.
2. Don't make the data public at all, but if you're prepared to allow a few people to see it in private, then arrange to send that data privately…
…and a 3rd option, a mix of 1. and 2.





For me is the problem how to sort the data and move them, I mean sorting by money thats easy, i would just cycle through single cell with IF inside it. But the sorting the data on similarities, thats what i dont know how to do.Yes, and it's the same problem for those trying to help you, but they don't have the benefit of the data.
It's a balance: you're asking people to help you but they have very little idea of the data they have to work on, and only limited idea of the results you want. This means they have to guess an awful lot, and probably they'll guess wrongly, so what they suggest is quite probably going to be unusable to you. All people will be prepared to do is give broad suggestions like mine in msg#2 (or like: do some sorting, put in an autofilter, cut and paste results, set up a pivot table and…).
The balance is between how much work you want to do, and how much work others will want to do to help; the more you're prepared to do, the more others will be prepared (and able) to do.

[SamT]

We are talking about 100 - 200 entries, each having about 40 properties

But only two properties matter, Location and Spending Habits.

I would: create a workbook with the following sheets


EvilCorp Location 1
EvilCorp Location 2, as needed
100
200
300
400, as needed


Save that workbook as "Sales Calls Template.xlsx"

Then use a macro to open Sales Calls Template.xlsx, read and transfer the SQL data as indicated into Sales Calls Template.xlsx, and finally save Sales Calls Template.xlsx as "Sales Calls 11- Feb- 2017.xlsx" Using current dates, of course.

If any filtering or sorting needs to be done on the transferred data, those macros can be written also.

Even with a thousand entries, that little bit would be done before you can sip your tea and put the cup down.

To write that, we only need to know the specifics of EvilCorp's adress that make them unique, the actual breakoff values for spending and which columns contain that info.

Finally a list of tab names in Sales Calls Template.xlsx and their matching filtering data

Tab Name:= "EvilCorp Location1", Filtering Data:= "#3 Downing"
Tab Name:= "300", Filtering Data:= $201 - $300

[DavidWaldo]

So, I've made a sheet with simmiliar random data. I can send you the file if needed. But it looks like this..
18338
About the data: I've only added the data important for sorting. The last collumn (Waiting for visit) is not part of the data from SQL but a result of my own formula that deretmins if the cust. should be visited (there are companies I dont want to trade anymore or I deal with myselft, etc.)
What i want to do now with this sheet is to sort data via VBA. I want the data to sort among sheets like this:
1. All locations with more than one person, regardless of Average_revenue
2. Single clients-at-a-single-location with Average revenue 0-99
3. Single clients-at-a-single-location with Average revenue 0-129
4. Single clients-at-a-single-location with Average revenue 130-300
5. Single clients-at-a-single-location with Average revenue 300-more
Skipping all the data that has NO in Waiting for visit cattegory. This will be done every 18 hours.

How to achieve this?
My Idea was to cycle through all cells in Waiting for visit and remove rows that have NO.
What I dont now is how to find a move duplicates in the adress cattegory via VBA - maybe again cycle through these cells but with one cycle inside another cycle?(seems too complicated - is there a better way?)
third step is to cycle through average revenue cells and move them.

Thanks for your advice.

[p45cal]

I can send you the file if needed. Yes, post it please. I can't experiment on a picture.

Also:


2. Single clients-at-a-single-location with Average revenue 0-99
3. Single clients-at-a-single-location with Average revenue 0-129Is this right?

[DavidWaldo]

Excel file should be in the attachment.



3. Single clients-at-a-single-location with Average revenue 0-129

should have been 100 - 129.

[p45cal]

I'll be working on this on and off today. In the meantime, here's a formula to put in row 2 (any spare column) of the Source_Data sheet and copy down:

=CHOOSE(MIN(2,COUNTIFS($B$2:$B$201,$B2,$D$2:$D$201,$D2,$F$2:$F$201,"YES"))+1,"No",MATCH($E2,{0;100;130;300}),"G")It's not quite right..

[snb]

Never, ever post a protected sample file in a forum.

[p45cal]

It's not quite right..This is better:

=CHOOSE(MIN(2,IF($F2="YES",COUNTIFS($B$2:$B$201,$B2,$D$2:$D$201,$D2,$F$2:$F$201,$F2),0))+1,"None",MATCH($E2,{0;100;130;300}),"G")
(It's the destination sheet of the row.)

[DavidWaldo]

Never, ever post a protected sample file in a forum.
My bad, it's a relflex. The password is "Pass"


=CHOOSE(MIN(2,IF($F2="YES",COUNTIFS($B$2:$B$201,$B2,$D$2:$D$201,$D2,$F$2:$F$201,$F2),0))+1,"None",MATCH($E2,{0;100;130;300}),"G")

Ok, I think I understand ... you create another collumn in the main data with this formula that determins in which cattegory this fits.

[p45cal]

Try the attached which has this code in called by the button on the Source_Data sheet:
Sub blah()
ActiveWorkbook.Unprotect Password:="Pass"

SheetNames = Array("0-100", "100-130", "130-300", ">300", "Groups")
AFCriteria = Array(1, 2, 3, 4, "G")
With Sheets("Source_Data")
.AutoFilterMode = False
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2:G" & lr).FormulaR1C1 = "=CHOOSE(MIN(2,IF(RC6=""YES"",COUNTIFS(R2C2:R" & lr & "C2,RC2,R2C4:R" & lr & "C4,RC4,R2C6:R" & lr & "C6,RC6),0))+1,""None"",MATCH(RC5,{0;100;130;300}),""G"")"
.Range("G1").Value = "Destn sheet"
For i = LBound(SheetNames) To UBound(SheetNames)
.Range("A1").AutoFilter Field:=7, Criteria1:=AFCriteria(i)
If .AutoFilter.Range.Columns(1).SpecialCells(xlVisible).Cells.Count > 1 Then
Set NewSht = Sheets.Add(After:=Sheets(Sheets.Count))
On Error Resume Next: NewSht.Name = SheetNames(i): On Error GoTo 0 ' if a sheet with that name already exists don't bother renaming it.
.AutoFilter.Range.Columns("A:E").SpecialCells(xlVisible).Copy NewSht.Cells(1)
NewSht.Columns("A:E").AutoFit
End If
Next i
.AutoFilterMode = False
.Range("G1:G" & lr).ClearContents
End With
End Sub

[DavidWaldo]

Great! This solves the problem, but the bad thing is I dont understand a single line in there.... I'll study the code tonight and hopefully learn from it. Thanks very much.

[p45cal]

What I've done here:

On Error Resume Next: NewSht.Name = SheetNames(i): On Error GoTo 0 ' if a sheet with that name already exists don't bother renaming it.
may not be very clever; one of the categories might not exist in newer data and that would leave the old sheet still there, and you might think it's up-to-date data.
Safer instead to delete all the old sheets earlier in the macro:
Application.DisplayAlerts = False: On Error Resume Next
For Each nm In SheetNames
Sheets(nm).Delete
Next nm
Application.DisplayAlerts = True: On Error GoTo 0

and change that line to:
NewSht.Name = SheetNames(i)

[DavidWaldo]

What if I would like to create a new workbook insted of heving new sheets in this file every day? My reason is I dont want the sales reps to acces this important file.

[p45cal]

add a line:
NewSht.Columns("A:E").AutoFit
NewSht.Move

[DavidWaldo]

Doesnt work for me...


Set NewBook = Workbooks.Add
With NewBook
.SaveAs Filename:=Date & ".xls"
End With

With Sheets("Source_Data")
.AutoFilterMode = False
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2:G" & lr).FormulaR1C1 = "=CHOOSE(MIN(2,IF(RC6=""YES"",COUNTIFS(R2C2:R" & lr & "C2,RC2,R2C4:R" & lr & "C4,RC4,R2C6:R" & lr & "C6,RC6),0))+1,""None"",MATCH(RC5,{0;100;130;300}),""G"")"
.Range("G1").Value = "Kategorie"
'-------------------------------------------------------
For i = LBound(SheetNames) To UBound(SheetNames)
.Range("A1").AutoFilter Field:=7, Criteria1:=AFCriteria(i)

If .AutoFilter.Range.Columns(1).SpecialCells(xlVisible).Cells.Count > 1 Then
Set NewSht = Sheets.Add(After:=Sheets(Sheets.Count))
NewSht.Name = SheetNames(i)
.AutoFilter.Range.Columns("A:E").SpecialCells(xlVisible).Copy NewSht.Cells(1)
NewSht.Columns("A:E").AutoFit 'uprav šířku sloupců A až E
NewSht.Move Workbooks(NewBook).Sheets(1)
End If

Next i
'-------------------------------------------------------
.AutoFilterMode = False
.Range("G1:G" & lr).ClearContents

End With

This only creates workbook with sheets but no data is moved, could be something with the selections?

[p45cal]

NewSht.Move NewBook.Sheets(1)