Pre Populating a Value based on one condition

Djmason2001

New Member
Joined
Feb 24, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
Hi Forum,
Apologizes to ask but need some help from the wider community l

I have two asks ;

Aim 1:) Currently i have a formula in Outcome (Column D) That looks at “Group id(Column B)” and where “Helper (Column C) = 'Master'” returns the value of the id(Column A)

Current Outcome Formula : =INDEX($A:$A,MATCH(B2&"Master",$B$1:$B$101&$C$1:$C$101,0)).

Id (Column A) is Maximum of 18 chars. If possible i would like to return the first 15 chars ( of the Id in column A). I have put expected outcome in Column E

Aim 2:) Based on one conditions “Group id(Column B)” I would like to concatenate the values id(Column A) in Column F (outcome 2)

Criteria ;

The formula should always start with &cid=

After the equals sign i would need 1 of the ids(column A) relating to the groupid.

Group 21 Example ;
&cid=(Criteria GroupID21 Row2) = 001j000000hdOIWAA2
&cid=(Criteria GroupID21 Row3)= 0013a00001bv1rBAAQ

Full Url : &cid=001j000000hdOIWAA2&cid=001j000000hdQb6AAE

Group 25 Example ;
&cid=(Criteria GroupID25 Row4) = 001j000000hdOIWAA2
&cid=(Criteria GroupID25 Row5) = 001j000000hdPTjAAM

Full Url : &cid=0013a00001bv1rBAAQ&cid=001j000000hdPTjAAM

IDGroup IDHelperOutcomeExpected OutcomeOutcome 2
001j000000hdOIWAA2
21Master001j000000hdOIWAA2
001j000000hdOIW
&cid=001j000000hdOIWAA2&cid=001j000000hdQb6AAE
001j000000hdQb6AAE
21Non Master001j000000hdOIWAA2
001j000000hdOIW
&cid=001j000000hdOIWAA2&cid=001j000000hdQb6AAE
0013a00001bv1rBAAQ
25Non Master001j000000hdPTjAAM
001j000000hdPTj
&cid=0013a00001bv1rBAAQ&cid=001j000000hdPTjAAM
001j000000hdPTjAAM
25
Master001j000000hdPTjAAM
001j000000hdPTj
&cid=0013a00001bv1rBAAQ&cid=001j000000hdPTjAAM

Looking forward to your help
Regards
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Just an update,
For Aim 1 : i have used the following formula to get the expected outcome

=LEFT(INDEX($A:$A,MATCH(B2&"Master",$B$1:$B$103&$C$1:$C$103,0)),15)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top