Multiple entries in a cell - Help!

[Marquis76]

Hi,

I've been looking for hours for a solution - to no avail. I'm keeping track of my dividends in my stock portfolio. I have multiple accounts, at different banking institutions, some registered some non-registered. I'm using a code for each (i.e. 1-11).

Sometimes, I buy the same stock in multiple accounts. Since dividend amounts are the same and paid on the same date for all accounts, I'm looking to register multiple values under Account in my excel sheet, to avoid duplicating the amounts paid/dates for each account where the stock is held. My sheet looks like the attached sheet.


I have sections of my sheets where each individual account sums up all dividends paid for this stock, per account. However, my problem is that I am unable to get excel to "read" cells with multiple values in them. Help!

Thanks a million

[MarvinP]

Hi marquis and welcome tot he forum,

Could you show us in another attached workbook what you mean whey you say "with multiple values in them". Your example doesn't show any cells (that I see) with multiple values in a single cell.

[Gregb11]

You probably should show more of the spreadsheet showing more combination of possibilities for what you want to do, but can you have unlimited number of accounts under the Account column?
I assume you'll always separate them with a comma. I don't know if this is any help for you, but you could use this formula to get the first account number:
=LEFT(C2,SEARCH(",",C2)-1)
To get the last account number, you could use this:
=RIGHT(C2,LEN(C2)-SEARCH(",",C2))

[Marquis76]

HI Marvin. Thanks for your answer. The C column has two values in them (10,11). Excel can read 10 by itself, but not "10" and "11" in the same cell!

Thanks

[Marquis76]

Thanks for your response, Gregb11. I can have between 1-11 numbers under accounts. So I guess the same stock could be bought in 11 accounts, so it would be 1,2,3,4,5,....in the same cell. What formula would allow me to search and if number "11" is located anywhere in that suite of numbers within the same cell?

Thanks!

[MarvinP]

Hi Marquis,

I understand the problem now. You might be able to use Advanced Filters with your data to pull out those single account transactions. If you had a "1,2,5,8,11,9" as the accounts you bought MSFT in you could create a wildcard filter of "*1," which will pull both 1 and 11 (darn). Your best bet is to give a row of data for each account when you buy. I've constructed that table from your example. AND - done a Pivot Table with the account number as the filter. You can also group by date. This data structure allows Pivots and a much better long term answer.
Change Data Structure Acct to Pivot.xlsx

[Marquis76]

Hi Marvin. Thanks so much for your time and effort. I was aware of the possibility of duplicating dates like you did, but the issue is that elements in the column will go wayyy down since the dates will have to be repeated for each account. Wish there was a way for Excel to read multiple variables in the same cell!

[MarvinP]

Hi Marquis,

If you would accept a VBA answer, we could transfer your transactions into a table that allows a pivot table. We'd need a better example of what you have as a sample. Would a VBA answer to convert the data be ok?

[Marquis76]

Hi Marvin,

Ok so I uploaded the actual worksheet. I highlighted in yellow the cells in question. Can you do something with standard formulas or do we have to go VBA?

THanks a million Marvin!

[MarvinP]

Hi,

You have a few problems with your table. You need a blank row above the Date entry at A11. That would then define a table from row 12 down.
You have the same column head name of "Date" twice which keeps tables from working correctly.
Why don't you have a Transaction type of "dividend" under your buy/sell?
If your Transaction is to BUY you need the amount to be negative. Sell would then be Positive as would dividends.
See the attached for some of the fixes I would suggest.
Better Fomat of Data.xls
Hope this helps somewhat.

[Marquis76]

Hi Marvin,

Thanks for your help, but I was looking to avoid duplicating dates. I see this is impossible. I'm not using negative for Buy because this sheet is not used for removing any amount from the cash balance. Also adding dividends under Buy would influence the adjusted cost base (ACB), which they should not.

Anyways, thanks. A bit frustrating that Excel cannot read multiple values in a single cell. That would entirely solve my problem without making this sheet unwieldy!

[FlameRetired]

If I understand correctly try these steps.

1.) Avoid the merged cells in columns R:X like the plague. They cause nothing but grief in formulas. I removed merging in the attached.
2.) In a helper column (see column S in the attached) use this helper formula to determine the number of accounts in each column Q string.
3.) In column R this formula parses the account #s within each cell.

Please Login or Register  to view this content.

They are not all apparent upon first examination but they are there in memory. If you click in the formula bar and hit the F9 function key you will see them in an array .... {"10";"11"} .... of text "numbers".

If they need to have numeric values coerce those arrays by adding a 0 to the formula like this

Please Login or Register  to view this content.

Then the array will be numeric ... {10;11}

Does this do what you want?

[Marquis76]

Hi Flameretired. Thanks for your help! This seems to be going to going in the right direction, but for some reasons AE8 and AH8 don't sum the dividends for the account. Any way to fix?

Thanks!

[FlameRetired]

You hadn't mentioned exactly where or how you needed to use these parsed values until now. SUMIF won't handle this.

I am still not confident I understand the end goal, but try array entering this formula in AE8

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

and this one in AH8

Please Login or Register  to view this content.