substitute | A free runtime modification library | TCP library

Thank you in advance for any help provided as it's much appreciated! Hope you're all keeping well in these uncertain times.

I have a question regarding Google Sheets. Below you'll find a link to a very large Google Sheets document in which I am trying to substitute text such as 'XXXXX', 'BBBBB', 'TTTTT', 'YYYYY', 'RRRRR' and replacing the same phrases into situations later in the document. If someone could help input a formula into columns such as P, Q, R, S, T, V and W that would be really helpful. I believe I have made the document pretty self-explanatory where in row 1 you can find the labels 'XXXXX', 'BBBBB', 'TTTTT', 'YYYYY', 'RRRRR' and the places to replace them respectively in columns P, Q, R, S, T, V and W.

I have tried to use an arrayformula with substitute, but every time I did this I could only change the first row and it would not cascade down the sheet to affect the other 900+ rows no matter what I tried.

I would appreciate an explanation of how to do it if someone is changing the document so I can learn moving forwards!

The other thing which I am very confused about how to achieve is, if in column O the word 'Yes' appears, then I would like 'VF' to come into column W but if it says 'No' in column O then I don't want it to appear in column W.

Again an explanation of how this is achieved, as well as implementing it into my document would be much appreciated!

Thank you.

https://docs.google.com/spreadsheets/d/1JgAFxqJqVlg2Q-LtLP1udrdJksJAtpC1tLH5dyxf1SA/edit?usp=sharing

I wanted a ArrayFormula at C1 which gives the required result as shown.

Entry sheet:
(Column C is my required column)

Date Entered is the date when the Name is Assigned a group i.e. a, b, c, d, e, f

Criteria:

1. The value of count is purely on basis of Date Entered (if john is assigned a on lowest date(10-Jun) then count value is 1, if rose is assigned a on 2nd lowest date(17-Jun) then count value is 2).
2. The value of count does not change even when the data is sorted in any manner because Date Entered column values is always permanent & does not change.
3. New entry date could be any date not necessarily highest date (If a new entry with name Rydu is assigned a on 9-Jun then the it's count value will become 1, then john's (10-Jun) will become 2 and so on)

Example:

After I sort the data in any random order say like this:

Random ordered sheet:
(Count value remains permanent)

And when I do New entries in between (Row 4th & 14th) and after last row (Row 17th):

Random Ordered sheet:
(Doesn't matter where I do)

1. ={"AF Formula1"; ArrayFormula(IF(B2:B="", "", COUNTIFS(B$2:B, "="&B2:B, D$2:D, <"&D2:D)+1))}
2. ={"AF Formula2";INDEX(IFERROR(1/(1/COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B)))))}

I tried to figure my own ArrayFormula but it's not working:

I got Formula for each cell:
=RANK($D2,FILTER($D$2:$D, $B$2:$B=$B2),1)
I figured out Filter doesn't work with ArrayFormula so I had to take a different approach.

I took help from my previous question answer (Arrayformula at H3) which was similar since in both cases each cell FILTER formula returns more than 1 value. (It was actually answered by player0)

Using the same technique I came up with this Formula which works absolutely fine :

=RANK($D2, ARRAYFORMULA(TRANSPOSE(SPLIT(VLOOKUP($B2, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ","))), 1)

Now when I tried converting it to ArrayFormula: ($D2 to $D2:$D & $B2 to $B2:$B)

=ARRAYFORMULA(RANK($D2:$D,TRANSPOSE(SPLIT(VLOOKUP($B2:$B, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ",")), 1))

It gives me an error "Did not find value '' in VLOOKUP evaluation", I figured out that the problem is only in VLOOKUP when I change $B2 to $B2:$B.

I'm sure VLOOKUP works with ArrayFormula, I fail to understand where my formula is going wrong! Please help me correct my ArrayFormula.

Here is the editable sheet link

Edit: It looks like this is a known issue with the "cascade" method. Results that return NA values after the first attempt don't like being converted to doubles when subsequent methods return lat/lons.

Data: I have a list of addresses that I need to geocode. I'm using lapply() to split-apply-combine, which works, but very slowly. My thought to split (further)-apply-combine is returning errors about dim names and sizes that are confusing to me.

Is there a method in R, to substitute the values of a vector using a dictionary (2 column dataframe with old and new value)

The only method I know is to extract the old value into a dataframe and merge it with, what I call,the dictionary (which is a two column dataframe with old and new values). Afterwards reassign the new value to the original old value. However, it seems when using merge (at least since R v4.1, the order of the x value is not maintained, so I am using join now which keeps the original order of dataframe x intact. I am thinking that there must be an easier way, I just have not found it. Hope this is understandable, I appreciate any help.

cheers Hermann

I need to create an app for iOS that lets the user to call any number via VoIP. I'm trying to implement the quickstart guide for twilio that I found here: https://www.twilio.com/docs/voice/voip-sdk/ios/get-started#bullet9

The app starts well but when I insert a number and press on "Call", a voice says me that an error occurred. In the twilio console I found this error:

An attempt to retrieve content from https://my-quickstart-dev.twil.io/make-call returned the HTTP status code 404

But I cannot understand what it means... that URL is from the tutorial... with which URL I should substitute it? In addition, following the tutorial, I wrote my verified number in the field "callerNumber" of the fields of the server make-call.js and place-call.js

I'm very new with the VoIP services so I'm trying to understand how to implement ad app-to-phone service (tried twilio and vonage)

This question was previously posted as SO#67861846. But someone (non-moderator) marked it as a duplicate and closed it. It is NOT a duplicate as claimed: getResponseCode WAS indeed called but the result was no help (always 200 even for an invalid host). So, I try again...

The Question

My app regularly downloads files from a server using HttpUrlConnection(). Brief code example below:

Dear Stackoverflow community, I have an excel file "big_excel.xlsx", which comprises of four columns namely "date_column","efficacy", "composition" and "testgroups". Basically, I have splitted this excel quarterly "q1..q4", so that I can compare values in each column with 4 different excels that I recieved from 4 different sources which are supposed to be 100% identical. This excels from sender are such that elements are already sorted in such way that it should match exactly with the excel which are splitted quarterly. My code works perfectly for quarter q1. And to compare I have used ".equals" because it can have nans. Now I have to apply same code concept for the remaining quarters q2..q4.

I have a PowerShell script that I have developed and tested in Visual Studio Code that runs without error. However, when I attempt to run the same script from a PowerShell command line, it fails with several errors, the first one being a complaint about the use of Param on the first line of the script. I have PS 5.1 installed on my machine.

When launched from the command line, here is what I execute:

powershell -ExecutionPolicy Bypass -File Export_PI.ps1 -Project Catalyst-DHT

When I run this script from Visual Studio Code, I substitute a hard-coded value in lieu of the parameter -Project for testing purposes. When I run it from the PS command line, this is the first error I get:

Param : The term 'Param' is not recognized as the name of a cmdlet, function, script file, or operable program.

I have read elsewhere that this typically occurs when Param() is not the first non-commented line in the script. In my script, it is the first non-commented line:

$ProjectName = Param([String]$Project)

I'm not a PowerShell guru, so this could well be a newbie mistake. I'm hoping someone can help me figure it out. Thanks.

I have a very similar query that works for two different types of inputs and will match on the correct column. Both queries are essentially the same except for one word, i.e. the column name.