Concatenate Formula

[Dslittle16]

Please assist with following:

1) The AR 12901 tab needs to reflect the Deposit ID results from the control totals tab. I'm trying to eliminate manual input per cell since each deposit is based on a particular company.
For example, 1 means HA, 7 means HAI, 3 means HHO, 5 means HIHC, and 340B means 340B.

2) The GC 12017 & Other tab needs to reflect the Deposit ID results from the control totals tab.

[loginjmor]

Hi -

I don't understand what you are looking for. Can you walk me through an example for each of your sheets telling me exactly what should be showing up and in what cells?

Thanks

[Dslittle16]

Under the AR 12901 tab:

Column A

Cell A4 needs to say 1-092916 EFT, the 1 represents HA Gen

Cell 6 needs to say 2-092716 EFT, the 2 represents HHCS Gen

So forth...I need to be able to drag this formula down and not enter a 1-, 2-, or 3- through manual input.

Under the GC 12017 & Other tab:

Cell A2 needs to say 1-092916GC, the 1 represents HA Gen

Cell 43 needs to say 2-092716GC, the 5 represents HIHC Gen

I need to be able to drag this formula down and not enter a 1-, 2-, or 3- through manual input.

[loginjmor]

Hi -

OK, I have the first part:

Copy and paste this formula into cell A2 of Sheet AR12901 and copy down:

=IF(E2="","",CONCATENATE(INDEX('Control totals'!$E$4:$E$39,MATCH(SUBSTITUTE('AR 12901'!B2," Gen",""),'Control totals'!$E$4:$E$39,0)+1)&"-"&TEXT(C2,"mmddyyyy")," EFT"))

On Sheet GC 12017 I'm having a little difficulty. First Cell A2 appears to be HHO 340B, not HA Gen as you mention above. Can you clarify that? Also, the existing formula in Cell A2 refers to a sheet 'Bank Txns'!B1 which I don't have. So when I edit your formula, everything goes to 0 and I can't experiment with the formulas. Can you upload a revised spreadsheet with that sheet also?

[loginjmor]

Hi -

I found the other sheets - they were just hidden. I'm running into a circular reference problem. A lot of the cells, sheets and formulas are more inter-related than an Arkansas family tree (I'm from Arkansas originally so I can say that). It's taking me a little bit to trace through this. I will try to have it figured out in the morning.

[Dslittle16]

Any luck with the GC tab?

[Dslittle16]

Hi loginjmor,

Do we have any luck with the GC tab? I was thinking of maybe changing the formulas to link with the control tab, but there are so many variables. How can we simplify this formula?

[loginjmor]

Hi -

Sorry, my day job took my attention Friday and this morning. I'm back on it now. I'm not sure how to simplify this as many of the formulas not only refer to other sheets, but there are numerous named ranges and named formulas that refer to specific files on your server such as this one defined at "Internal_Tsfr"

=OR('AR nuances'!Return="RETURN",'S:\Finance\FAS Operations\Cash Receipts\Bank Scan Files\FY 16\11 May 2016\EFTs\05.09\[EFTs - 05.02.16 - 05.06.16 - Master.xlsx]Bank Txns'!$H2="Internal Wire",AND('AR nuances'!Orig="HEARTLAND",'AR nuances'!Bene="HEARTLAND"))

So, this thing is so complex, and I don't completely understand what is the relationship between these various files. For example, perhaps the external file referenced above is updated by someone else, and the spreadsheet you posted gets updated when that file is opened. I just don't know. I hate to make wholesale changes without understanding how the whole thing works.

That being said, what I can do is get Excel to concatenate the Deposit ID on the GC 12017 & Other spreadsheet, Column A, which is your original question. I should have it this afternoon.

[loginjmor]

Hi -

OK - Copy this formula into Cell A2 on Sheet GC 12017 & Other:

=IF(ISERROR('AR 12901'!O2),"",IF('AR 12901'!O2="GC",CONCATENATE(MATCH(VLOOKUP('Bank Txns'!F1,'Bank Account Lookup Table'!A:B,2,0),'Control totals'!$A$13:$A$18,0),"-",LEFT(TEXT('Bank Txns'!B1,"00000000"),2),MID(TEXT('Bank Txns'!B1,"00000000"),3,2),RIGHT(TEXT('Bank Txns'!B1,"00000000"),2)," GC"),""))

Copy down. It should give you the results you described in your earlier post.

Hope this helps!

[Dslittle16]

On the GC tab:

Can you relate the Deposit ID first numeric number to the appropriate company. First example, cell A5 should say 2-100316GC (delete space between GC)

2- shd be HHCS Gen
3- shd be HHO Gen
5- shd be HIHC Gen
1- shd be HA Gen

[loginjmor]

Hi -

Copy and paste this into Cell A2 and copy down:

=IF(ISERROR('AR 12901'!O2),"",IF('AR 12901'!O2="GC",CONCATENATE(MATCH(VLOOKUP('Bank Txns'!F1,'Bank Account Lookup Table'!A:B,2,0),{"HA Gen","HHCS Gen","HHO Gen","HHO 340B","HIHC Gen"},0),"-",LEFT(TEXT('Bank Txns'!B1,"00000000"),2),MID(TEXT('Bank Txns'!B1,"00000000"),3,2),RIGHT(TEXT('Bank Txns'!B1,"00000000"),2),"GC"),""))

I simply hard coded the array {"HA Gen","HHCS Gen","HHO Gen","HHO 340B","HIHC Gen"} into the formula. Since you didn't have a choice 4, I improvised and used "HHO 340B". If you want a different item, simply replace that one. If you want them in a different order, just rearrange the list.

Hope this helps.

[Dslittle16]

Under the GC 12017 & Other tab:

Cell A2, needs to read 340B092916GC.

Could you please create a manual formula for this one, I know its out the box?

[loginjmor]

OK - Try this:

=IF(ISERROR('AR 12901'!O2),"",IF('AR 12901'!O2="GC",CONCATENATE(CHOOSE(MATCH(VLOOKUP('Bank Txns'!F1,'Bank Account Lookup Table'!A:B,2,0),{"HA Gen","HHCS Gen","HHO Gen","HHO 340B","HIHC Gen"},0),"1-","2-","3-","340B","5-"),LEFT(TEXT('Bank Txns'!B1,"00000000"),2),MID(TEXT('Bank Txns'!B1,"00000000"),3,2),RIGHT(TEXT('Bank Txns'!B1,"00000000"),2),"GC"),""))