MS Excel / VBA help thread

Yeah it's difficult to solve without seeing the actual data. Sounds like what I have above would work, you just need to check the Product column rather than the Role column.

I know because Meat Eaters go to the Butchers, not the Baker or Candlestick Maker. They're covered by a different process which was done correctly.

Meat Eaters go to the Butchers

Is that reflected in your data anywhere?

As is often the way talking through it has already helped a bit.

But the closest I've got is pivot group by UID then columns for process On and Off, with a true/false for when the sum of process matches to.

Is it something like:
IF Role = "Butcher"|"Candlestickmaker"
AND
IF UID = "RAB123"
THEN
Process = "on"

That's the logic isn't it?

so you could create a concatenated UIDROLEPROCESS (and that is unique) spot when it ends off instead of on (or vice versa)
RAB123BUTCHERON
RAB123BUTCHEROFF

?

RAB123BUTCHERON

RAB123BUTCHEROFF

I guess the question is in a list of 30k how do I find the this mismatching pair?

Remove the 100% matches followed by some sort of fuzzy duplicate matching?

if you add a column which does this concatenation, you can then :
create a lookup table of all the wrong concatenations and
then do a vlookup in a new column in the original table and then instead of the match being brought back, have it so 1 is for a match and then filter the table for 1
or
use the filter function to look at the original table and then filter the concatenation column for only the wrong concatenations

or do it in powerquery similarly where you do you an antijoin

https://support.microsoft.com/en-gb/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

so
create your column in your original data so you have a new field
that has this
RAB123BUTCHERON
RAB123BUTCHEROFF

then make your data source a table (Ctrl+T)
then use the filter table to filter the table where concatenation is in wrong concatenation list.

I'm auditioning for new jobs if anyone wants to employ me.

Fancy being a consultant?

Thanks for the tips. I've done a slightly shonky iterative version of this.

Going try and redo it in power query in a tidier format as the ultimate aim is a list with ancillary fields to be uploaded to a workaround tool. And obvs only 1/10 of the fields share the same naming convention 🫠

Will you pay me >53k a year?

do you mean the fields aren't named the same thing? or do you mean the contents of the field?

Yeah not named the same thing. As in there are a tonne of fields so 'Location' in one place is 'LCTN' in another and 'XXX_location' in another.

Not the end of the world and where PQ is good I'm discovering as you can sort it out upfront, but annoying as you can't just lookup without cross checking fields are correct.

Are you pulling in from a folder location lots of files?
Because you can create a schema that you want (location =locationnvariants) and then apply that.
Or is it in a file already and there are lots of fields called location? If it’s the latter you could make a single column of all the location variants.

No. It should be a one time fix.

I'm deriving my data off one epic xls that someone else has built which is a weekly verified/corrected version of what should be the golden source. But I will want to run a check against the golden source before I send the data for the fix. Each of those have some fields with slightly different naming conventions.

I tried to set it up from the share point folder to capture any updates, but just kept getting fails. So just used the epic xls. and will double check how to ensure I get updates.

I am struggling to think how to solve this without saying “nuke it from space”

Anyway to get a folder name from a string of data like this?

Z:_PROJECTS\client\project\03_3D\01_Scenes\WIP
Z:_PROJECTS\client\project\03_3D\01_Scenes\
Z:_PROJECTS\client\project\03_3D\02_Xref

basically I want 'client' and 'project' as fields, they may have a variable number of subfolders after them. I (kind of) know about LEN, but the paths vary in length beyond the bit that I want to extract, so not sure if that'll work very well.

Find, count and mid will help you isolate the bits you want

does it always go
projects\client\project?

textafter function and textbefore function are your friends here.

Nice one, will check those out

I think those have been depreciated by the looks of it.

They are new so may not be on every Excel. Textsplit would be the other one of use.

Otherwise try using SEARCH to get where each of the \ are and combine that with MID

yeah MID and FIND are getting me where I need I think. How do you find the 2nd or 3rd occurrence of a character with FIND?

edit: nevermind, think I'm nearly there