 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I have two CSV files.
One lists clients, then their location (most clients have 100+ locations) and then, by location, some preference item (one of 5 values)
So something like this:
Client A, location 1, "red"
Client A, location 2, "red"
Client B, location 1, "red"
Client B, location 2, "blue"
I need to differentiate the clients list into two categories: those with all their locations use the same preference, and those clients with locations using different preference values.
So, Client A would be in one category because all locations have the same preference whereas Client B would be in the other category because at least two locations use different preferences.
I then have a separate CSV, which list those clients and locations again, but with what they actually ordered (and there may be no orders; indeed there are a lot of empty rows).
So, I then need to figure out which Clients which had a single preference have only ordered items which match their preference.
I presume Excel can do such a thing (maybe not in single function, so I don't mind if I need separate steps and/or generate new CSVs) but I'm stumped at the moment ...
Sounds like (a) filtered pivot table(s) could do some if not all of it.
BUT it sounds more like a database and SQL job to me.
Database was my first thought too, that or get dirty with VBA.
Interesting.
Might be able to create an app to do that.
🤔
If you have any kind of ongoing need to record and work with this data then setting up a database now will make your life a lot easier.
For now, when you create a pivot table in Excel you have the option to add to data model. This will allow you to do a distinct count on the preference field and see which customers only have 1. Not sure of exact steps as I don't have excel on home PC, google search for excel distinct count if you need it.
Is this a one off or something that's going to be updated regularly? For a one off you could probably slog through it using standard excel functions with a bit of filtering and manual intervention. But as others have said, this is what databases do better than Excel so if you've got O365 I think Access is still included so you could try pulling the CSVs into that instead. You could potentially also use the "get data" (power query) capabilities of Excel to import and manipulate the CSVs - VBA would be a last option.
Hmmm, thanks for help so far. I'll read up about distinct count.
I haven't taken the time to fully visualise what you're doing but it seems like you're aiming for a list of client with a few conditional attribute columns and thise conditions should be straightforward to determine with standard excel functions like sumifs, countifs, counta, vlookup, index, match, etc
1. Add a new column (client+pref) whcih contains the concatenated client name and preference fill down and then copy/paste special to fix the values.
2. Sort on that on that client+pref column.
3. Add another column to the right after client+pref column (dup) and then use an if statement to put in a 1 if the value in client+pref in this row is different from the row above, 0 if it's the same.
4. Copy and paste special values in dup to fix the values
5. sort by dup then client+pref
6. Rows with 1 in the dup column identify clients that have multiple preferences.
There's probably a couple of extra steps to tidy it up, but that should point you in the right direction.
If the list of clients is cells A1:A100
their locations are in cells B1:B100
and the preferences in cells C1:C100
then create in D1:D100 a concatenation of client and preference
=A1&C1
and copy down to cell D100
To get your list of clients with the same preference use:
=UNIQUE(FILTER(A1:A100,COUNTIF(A1:A100,A1:A100)=COUNTIF(D1:D100,D1:D100)))
To get your list of clients with the different preferences use:
=UNIQUE(FILTER(A1:A100,COUNTIF(A1:A100,A1:A100)<>COUNTIF(D1:D100,D1:D100)))