MS Excel / VBA help thread

@Chalfie Flows are possible but I can't see how you create a temporary spreadsheet using a Flow, it seems to want to save somewhere and I don't want that (I guess I could create another Flow to clear out a temp folder every hour).

@chez_jay It's the private data I want to use to populate the spreadsheet. Basically it's a big list of jobs and I only want people to see data relevant to the jobs assigned to them but not be able to access the source of the data. I'm fine with the formulas/VBA but not sure of the permissions. I guess I should get some people to test it and see what happens.

ah no, I thought it went public > private. Give the people the public, they update, it updates your private.

it might work the other way though?
I currently have a list which is for current and planned stuff. We have a historic list too, which is not yet populated. Part of the work is capturing information, so rather than writing it twice, i thought write once to excel then to list. I know, why can't they just update the fucking list?

I thought you were talking about just public (all can read) + private (some users can read) data, but it sounds like you want to control read permissions to individual rows based on user.

I don't think this is a job for Excel really.

At least not if you want to do it in real-time, rather than e.g. compile data per user once a day.

I can do the actual filtering easily in Excel but I think the permissions of who can update is going to screw me.

Annoying as the actual spreadsheet will take me ten minutes to produce but think I may have to use something else.

Help me out here.
I love a power query. I love a merge. BUT I ALSO REALLY FUCKING LIKE NOT GROWING MY TABLES AFTER MERGING.

How do I replicate the vlookup in powerquery? All I Want To Do : match this key to that key and when it matches bring back [code] else FUCKOFFDAMO

This might be what you want:

= Table.AddColumn(#"Source", "NewColumn", each try Table2{[z=[a]]}[x] otherwise null)

2 Attachments

ta

Why does this in VBA

MyFormula = "=TEXTJOIN(" & Chr(34) & ", " & Chr(34) & ",TRUE,IF(ISNUMBER(SEARCH(" & Keywords.Address(ReferenceStyle:=xlR1C1, External:=True) & "," & "RC" & ColumnNumber & "))," & Keywords.Address(ReferenceStyle:=xlR1C1, External:=True) & "," & Chr(34) & Chr(34) & "))"

result in this formula

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(@Sheet3!$A$1:$A$749,$A2)),Sheet3!$A$1:$A$749,""))

and in particular why has it added an @ to the first range but not the second. That @ fucks the formula up but I can't find out why it is happening.

I suspect it is something to do with this but I can't see anything on how to stop it fucking my formula
https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34

Edit: Seems I should have been using .Formula2
https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2?WT.mc_id=M365-MVP-33461

The issue you're encountering is likely due to the way the Keywords.Address function is being used and how the formula is being constructed. The Keywords.Address function is generating an absolute reference (e.g., Sheet3!$A$1:$A$749), and when combined with the SEARCH function, it results in an array formula. However, the @ symbol is being added automatically by Excel to indicate implicit intersection, which can cause unexpected behavior.

To construct the formula correctly in VBA, you need to ensure that the Keywords.Address function is used properly and that the formula string is constructed without introducing unwanted characters. Here's a revised version of your VBA code:

Dim MyFormula As String
Dim Keywords As Range
Dim ColumnNumber As Integer

' Assuming Keywords and ColumnNumber are already defined
MyFormula = "=TEXTJOIN(" & Chr(34) & ", " & Chr(34) & ",TRUE,IF(ISNUMBER(SEARCH(" & _
            Keywords.Address(ReferenceStyle:=xlR1C1, External:=True) & "," & _
            "RC" & ColumnNumber & "))," & _
            Keywords.Address(ReferenceStyle:=xlR1C1, External:=True) & "," & _
            Chr(34) & Chr(34) & "))"

' Example of setting the formula to a cell
Range("B2").Formula = MyFormula

Explanation:
Keywords.Address: This function generates the address of the Keywords range in R1C1 reference style.
Chr(34): This is used to insert double quotes (").
Formula Construction: The formula is constructed as a string in VBA, ensuring that all parts are correctly concatenated.

from github visual studio copilot thing

Cheers. I did try something like that but it turns out the @ is added after in newer versions of Excel (the ones with dynamic arrays) and they brought in .Formula2 to fix it (which I'd never heard of).

Agh! how to ask this?!

I have a big list of parties, each has a UID. Each of these parties has >1 product with >1 role, but for simplicity lets say their are "pairs" made up of UID + Role

There is a field ('Process') which should have been changed when a particular process was performed. However, in error only one in a pair has been changed.

e.g.

Correct

A	B	C	D
UID	Product	Role	Process
RAB123	Product 1	Butcher	On
RAB123	Product 1	Meat Eater	On • #2165 hugo7 Incorrect A B C D UID Product Role Process RAB123 Product 1 Butcher On RAB123 Product 1 Meat Eater Off • #2166 hugo7 What I need is to identify all the instances where a pair's Process does not match for a particular role - e.g. Column D = 'On' and 'Off' for 'Butchers'. My problem is that so many of the UIDs have other roles I don't need so the data is more like (but with lots of different UIDs natch): A B C D UID Product Role Process RAB123 Product 1 Butcher On RAB123 Product 1 Meat Eater Off RAB123 Product 1 Baker On RAB123 Product 1 Baker On RAB123 Product 1 Baker On RAB123 Product 1 Candelstickmaker On RAB123 Product 1 Candelstickmaker On RAB123 Product 1 Candelstickmaker On RAB123 Product 1 Candelstickmaker On RAB123 Product 1 Candelstickmaker On RAB123 Product 1 Candelstickmaker On • #2167 aggi in reply to @hugo7 So you want to identify all rows where UID and Role are the same but Process has differing entries? • #2168 Drakien Combine UID and Role in a new column, order by this column, add a formula column that compares one row to the next, and flags if RoleUID is the same but process is different. • #2169 Drakien • #2170 hugo7 in reply to @aggi Actually no. Agh! • #2171 aggi If I was doing it dynamically I'd go along the lines of (I'm not at a computer so haven't checked it) =textjoin(sort(unique(filter(d:d,(a:a=a2)*(c:c=c2)))) Anything with "offon" in the column needs fixing. EDIT: I wrote this before your above comment • #2172 hugo7 Maybe I'm looking at this wrong. If I only show Butchers and Meateaters then all UIDs should have the same value for 'Process'/Column D. • #2173 Drakien Another solution, countifs c:c = c3 then countifs c:c=c3 and d:d=d3, compare the two values, if different you have an issue: • #2174 Drakien in reply to @hugo7 show Butchers and Meateaters Ah, that's different then. How do you know what roles pair with what? Based on the product? • #2175 hugo7 In my defence I'm trying to simplify and translate something that isn't as simple as I'm portraying. First 86 87 88 Last Page of 91 First Prev / 91 Last Next Post a reply Bold Italics Link Image List Quote code Preview Sign-in to post a reply × Formatting Help Don't worry about formatting, just type in the text and we'll take care of making sense of it. We will auto-convert links, and if you put asterisks around words we will make them bold. Tips: Create headers by underlining text with ==== or ---- To *italicise* text put one asterisk each side of the word To **bold** text put two asterisks each side of the word Embed images by entering:   That's the hard one: exclamation, square brackets and then the URL to the image in brackets. * Create lists by starting lines with asterisks 1. Create numbered lists by starting lines with a number and a dot > Quote text by starting lines with > Mention another user by @username For syntax highlighting, surround the code block with three backticks: ``` Your code goes here ``` Just like Github, a blank line must precede a code block. If you upload more than 5 files we will display all attachments as thumbnails. For a full reference visit the Markdown syntax. Close Post a reply About MS Excel / VBA help thread Posted by @mattty Actions Forums Today Following People Top About Terms of Use Privacy Policy Cookie Policy Report a problem × LFGSS London Fixed Gear and Single-Speed is a community of predominantly fixed gear and single-speed cyclists in and around London, UK. This site is supported almost exclusively by donations. Please consider donating a small amount regularly. Sign in or Register to continue