Dynamic hyperlink based on groupins

[Djmason2001]

Team,

I need your help with an Excel formula.

I want to create a URL which dynamically populated based on 3 criteria's .

The 3 criteria's are ;

1)ID
2)Master 15id
3)Group_ID

Example Data

ID	Master 15id	GROUP_ID
0033a00002WiusTAAR	0033a00002WiusT	64​
0033a00002QOJhsAAH	0033a00002WiusT	64​
003f100001pOrHCAA0	0033a00002WiusT	64​
0033a00002dqNb9AAE	0033a00002dopw3	120​
0033a00002dopw3AAA	0033a00002dopw3	120​
0033a00002TnrLwAAJ	0033a00002dopw3	120​

The hyperlink/formula should evaluate those criteria and create the hyperlink appropriately (See expected outcome)

Expected Outcome

ID	Master 15id	GROUP_ID	merge Link
0033a00002WiusTAAR	0033a00002WiusT	64​	https://salesforce.com/merge/conmer...cid=0033a00002QOJhsAAH&cid=003f100001pOrHCAA0
0033a00002QOJhsAAH	0033a00002WiusT	64​
003f100001pOrHCAA0	0033a00002WiusT	64​
0033a00002dqNb9AAE	0033a00002dopw3	120​	https://salesforce.com/merge/conmer...cid=0033a00002dopw3AAA&cid=0033a00002TnrLwAAJ
0033a00002dopw3AAA	0033a00002dopw3	120​
0033a00002TnrLwAAJ	0033a00002dopw3	120​

The URL is broken down into sections

Full URL :

https://salesforce.com/merge/conmergewizard.jsp?goNext=+Next+&p2=0033a00002WiusT&cid=0033a00002WiusTAAR&cid=0033a00002QOJhsAAH&cid=003f100001pOrHCAA0

If we break the URL into chucks The first part of the URL will always be the same : "https://salesforce.com/merge/conmergewizard.jsp?goNext=+Next+"

The next step is is to set the "Master Id". The master ID will always start with "&p2="

&p2=0033a00002WiusT

https://salesforce.com/merge/conmergewizard.jsp?goNext=+Next+&p2=0033a00002WiusT

Once the master id is added to the URL, we then need to append the other IDS onto the URL. To to this we just need to add the following "&cid=" +Id"

As there is more than one ID to append &cid=" +Id" would be repeated 2 times ( this is the number of Ids associated to a group ) The total Number of IDS associated to a group id is 3

https://salesforce.com/merge/conmergewizard.jsp?goNext=+Next+&p2=0033a00002WiusT&cid=0033a00002WiusTAAR&cid=0033a00002QOJhsAAH&cid=003f100001pOrHCAA0

Looking forward to your help

 

[Djmason2001]

Bump Thread

 

[FormR]

Hi,

The total Number of IDS associated to a group id is 3

Always 3?

If so, perhaps you can try this.

Book2
	A	B	C	D
1	ID	Master 15id	GROUP_ID	merge Link
2	0033a00002WiusTAAR	0033a00002WiusT	64	https://salesforce.com/merge/conmergewizard.jsp?goNext=+Next+&p2=0033a00002WiusT&cid=0033a00002WiusTAAR&cid=0033a00002QOJhsAAH&cid=003f100001pOrHCAA0
3	0033a00002QOJhsAAH	0033a00002WiusT	64
4	003f100001pOrHCAA0	0033a00002WiusT	64
5	0033a00002dqNb9AAE	0033a00002dopw3	120	https://salesforce.com/merge/conmergewizard.jsp?goNext=+Next+&p2=0033a00002dopw3&cid=0033a00002dqNb9AAE&cid=0033a00002dopw3AAA&cid=0033a00002TnrLwAAJ
6	0033a00002dopw3AAA	0033a00002dopw3	120
7	0033a00002TnrLwAAJ	0033a00002dopw3	120
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(C2=C1,"",HYPERLINK("https://salesforce.com/merge/conmergewizard.jsp?goNext=+Next+"&"&p2="&B2&CONCAT("&cid="&A2:A4)))