How to use continuous If else loop in Excel with

[tusharsharma24]

I have a data set with 4 columns - Original Bank , Acquiring Bank, Acquired Status ( Yes/ No) , Final Bank Name

Depending on whether the acquired status is yes or no, I want to copy the bank name to column Final Bank Name. Following rules apply :-

a) If the bank under column Original bank has been acquired, the acquiring bank name will be in second column and this name will be copied to Final bank name

b) If the bank under Original bank has not been acquired, then Final bank name will have name from Original Bank name

The complication is that a particular bank can occur in both Original bank name list and acquiring bank name list. Here is a sample table below :-

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Original Bank Name[/TD]
[TD]Acquiring Bank[/TD]
[TD]Acquired Status [/TD]
[TD]Final Bank Name[/TD]
[/TR]
[TR]
[TD]Bank of America Corp.[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]Bank of America Corp.[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co.[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]JPMorgan Chase & Co.[/TD]
[/TR]
[TR]
[TD]Bancshares, Inc.[/TD]
[TD]Bank of the Ozarks, Inc.[/TD]
[TD]Yes[/TD]
[TD]JP Morgan [/TD]
[/TR]
[TR]
[TD]First National Bank of Shelby[/TD]
[TD]Bank of the Ozarks, Inc.[/TD]
[TD]Yes[/TD]
[TD]JP Morgan [/TD]
[/TR]
[TR]
[TD]Summit Bancorp, Inc.[/TD]
[TD]Bank of the Ozarks, Inc.[/TD]
[TD]Yes[/TD]
[TD]JP Morgan [/TD]
[/TR]
[TR]
[TD]Bank of the Ozarks, Inc.[/TD]
[TD]JP Morgan [/TD]
[TD]Yes[/TD]
[TD]JP Morgan [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The column Final Bank name needs to show the name of final parent. How can I do this in excel ? Does it need VBA formula ? Can anyone help with this.

 

[Peter_SSs]

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function FinalBank(rData As Range, sOriginalBank As String) As String
  Dim d As Object
  Dim aData As Variant
  Dim i As Long
  Dim sNewBank As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  aData = rData.Value
  For i = 1 To UBound(aData)
    d(aData(i, 1)) = IIf(IsEmpty(aData(i, 2)), aData(i, 1), aData(i, 2))
  Next i
  sNewBank = sOriginalBank
    Do While d.exists(sNewBank) And d(sNewBank) <> sNewBank
      sNewBank = d(sNewBank)
    Loop
  FinalBank = sNewBank
End Function

Excel Workbook
	A	B	C	D
1	Original Bank Name	Acquiring Bank	Acquired Status	Final Bank Name
2	Bank of America Corp.		No	Bank of America Corp.
3	JPMorgan Chase & Co.		No	JPMorgan Chase & Co.
4	Bancshares, Inc.	Bank of the Ozarks, Inc.	Yes	JP Morgan
5	First National Bank of Shelby	Bank of the Ozarks, Inc.	Yes	JP Morgan
6	Summit Bancorp, Inc.	Bank of the Ozarks, Inc.	Yes	JP Morgan
7	Bank of the Ozarks, Inc.	JP Morgan	Yes	JP Morgan
Sheet1

 

[tusharsharma24]

Peter,

Thanks a ton. This is working.

One more thing - If I had a column " Defunct ( Y/ N )" in my data and I wanted Final Bank Name to have the Final bank name value only if Column Defunct had value "N". How can we Put in this check in our current Macro ? I am providing a sample output table below to explain what I mean.

[TABLE="width: 603"]
<colgroup><col width="188" span="3" style="mso-width-source:userset;mso-width-alt:6875; width:141pt"> <col width="77" style="mso-width-source:userset;mso-width-alt:2816;width:58pt"> <col width="163" style="mso-width-source:userset;mso-width-alt:5961;width:122pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 188"]Original Bank Name[/TD]
[TD="class: xl65, width: 188"]Acquiring Bank [/TD]
[TD="class: xl65, width: 188"]Acquired [/TD]
[TD="class: xl65, width: 77"]Defunct [/TD]
[TD="class: xl65, width: 163"]Final Bank Name[/TD]
[/TR]
[TR]
[TD="class: xl65"]Bank of America Corp.[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]No[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Bank of America Corp.[/TD]
[/TR]
[TR]
[TD="class: xl65"]JPMorgan Chase & Co.[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]No[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]JPMorgan Chase & Co.[/TD]
[/TR]
[TR]
[TD="class: xl65"]Gateway Bancorp[/TD]
[TD="class: xl65"]Banc of California, Inc.[/TD]
[TD="class: xl65"]No[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Bank of Sydney[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Bancshares, Inc.[/TD]
[TD="class: xl65"]Bank of the Ozarks, Inc.[/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]JP Morgan [/TD]
[/TR]
[TR]
[TD="class: xl65"]First National Bank of Shelby[/TD]
[TD="class: xl65"]Bank of the Ozarks, Inc.[/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]JP Morgan [/TD]
[/TR]
[TR]
[TD="class: xl65"]Summit Bancorp, Inc.[/TD]
[TD="class: xl65"]Bank of the Ozarks, Inc.[/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]JP Morgan [/TD]
[/TR]
[TR]
[TD="class: xl65"]Bank of the Ozarks, Inc.[/TD]
[TD="class: xl65"]JP Morgan [/TD]
[TD="class: xl65"]Yes[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]JP Morgan [/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

A	B	C	D
Original Bank Name	Acquiring Bank	Acquired Status	Final Bank Name
Bank of America Corp.		No	Bank of America Corp.
JPMorgan Chase & Co.		No	JPMorgan Chase & Co.
Bancshares, Inc.	Bank of the Ozarks, Inc.	Yes	JP Morgan
First National Bank of Shelby	Bank of the Ozarks, Inc.	Yes	JP Morgan
Summit Bancorp, Inc.	Bank of the Ozarks, Inc.	Yes	JP Morgan
Bank of the Ozarks, Inc.	JP Morgan	Yes	JP Morgan

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

</tbody>

</body>

 

[Peter_SSs]

If I had a column " Defunct ( Y/ N )" in my data and I wanted Final Bank Name to have the Final bank name value only if Column Defunct had value "N". How can we Put in this check in our current Macro ?

1. Add the extra code as shown in blue

Function FinalBank(rData As Range, sOriginalBank As String, sStatus As String) As String
  Dim d As Object
  Dim aData As Variant
  Dim i As Long
  Dim sNewBank As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  aData = rData.Value
  If sStatus = "N" Then
    For i = 1 To UBound(aData)
      d(aData(i, 1)) = IIf(IsEmpty(aData(i, 2)), aData(i, 1), aData(i, 2))
    Next i
    sNewBank = sOriginalBank
      Do While d.exists(sNewBank) And d(sNewBank) <> sNewBank
        sNewBank = d(sNewBank)
      Loop
    FinalBank = sNewBank
  End If
End Function

2. Include the extra parameter when you use the function in the worksheet as shown

Excel Workbook
	A	B	C	D	E
1	Original Bank Name	Acquiring Bank	Acquired	Defunct	Final Bank Name
2	Bank of America Corp.		No	N	Bank of America Corp.
3	JPMorgan Chase & Co.		No	N	JPMorgan Chase & Co.
4	Gateway Bancorp	Banc of California, Inc.	No	Y
5	Bank of Sydney		Yes	Y
6	Bancshares, Inc.	Bank of the Ozarks, Inc.	Yes	N	JP Morgan
7	First National Bank of Shelby	Bank of the Ozarks, Inc.	Yes	N	JP Morgan
8	Summit Bancorp, Inc.	Bank of the Ozarks, Inc.	Yes	N	JP Morgan
9	Bank of the Ozarks, Inc.	JP Morgan	Yes	N	JP Morgan
Sheet2

An alternative would have been to keep the UDF exactly as it was in post 2 and use this formula in the worksheet:
=IF(D2="N",FinalBank(A$2:B$9,A2),"")



I don't understand rows 4 & 5 though. Although they don't matter for this example since the rows are both marked as 'Defunct', how can ..
- in row 4, column C show 'No' to Acquired when there is an Acquiring Bank listed in column B?
- in row 5, column C show 'Yes' to Acquired when there is not an Acquiring Bank shown in column B?

 

[tusharsharma24]

Thanks Peter. I was using the workaround suggested in the second option by you. But the extra piece of code in the macro itself is certainly much efficient.

Appreciate all the help.

 

[Peter_SSs]

Appreciate all the help.

You're welcome.