Tricky One? : Creating consolidated text from a string of data

[danelskibr]

Hey everyone,

I am looking to create a macro that will consolidate several columns of data into a single cell. I am using this workbook to make trades in the stock market, and would like the trades that I make to be summarized in a single cell so that I can copy and paste this data into other places. I will first post the most important goals of this macro, then I will throw out some features that would be really nice, but are not required.

GOAL:

In cell J5 I have a drop down that has two options: "Raise Cash" and "Invest Cash".
Cell J6 is where I would like the consolidated data to go once the macro is ran.

1) Raise or Invest Cash

If "Raise cash" is selected in the dropdown, I want the consolidated cell to begin with the following words: "I have raised the required cash by selling".

If "Invest Cash" is selected in the dropdown, I want the consolidated cell to begin with the following words: "I have invested excess cash by purchasing"


2) Consolidate stock data

I have two sets of stock data that that look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]J[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]SPY[/TD]
[TD]10sh[/TD]
[TD]VFSUX[/TD]
[TD]$12,500[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]IJH[/TD]
[TD]15sh[/TD]
[TD]CIU[/TD]
[TD]5sh[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]VFINX[/TD]
[TD]$10,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]ARTQX[/TD]
[TD]$20,500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Columns A and E go together and columns J and N go together.
NOTE: The actual ranges of data begin in row 16 and end in row 48 for each columm. What row the data goes down to will vary.

This is where I am struggling. For each row that contains data, I want the data to be grouped together and added the the end of the invest cash or raise cash sentence I described above (in cell J6) in the following format: "E16 A16, E17 A17, E18 A18, E19 A19, N16 J16, N17 J17."

The end result for the sample data I gave (assuming "Raise Cash" was selected) would be the following:

I have raised the required cash by selling 10sh SPY, 15sh IJH, $10,000 VFINX, $20,500 ARTQX, $12,500 VFSUX, 5sh CIU

BONUS

1) The last trade described in the consilidated data could have the word "and" before the details and end with a period. EXAMPLE: (...$12,500 VFSUX, and 5sh CIU.)


2) If the dollar amount is over $1,000, change the formatting in the consolidated data to $1k. EXAMPLE: (...$12.5k VFSUX, and 5sh CIU)


3) At the end of the consolidated sentence, add another sentence that adds settlement dates of the trades. It would be in the following format, "The trades settle TODAY PLUS ONE BUSSINESS DAY and TODAY PLUS THREE BUSINESS DAYS". EXAMPLE: If today is 1/8/2015 "The trades settle 1/9/15 and 1/13/15."



I dont know if any of this can be done or how complicated this would be. All I know it is FAR outside my abilities, so I figured I would give you gurus a shot at this one. Thank you all inadvance for considering this.

best possible outcome: "I have raised the required cash by selling 10sh SPY, 15sh IJH, $10k VFINX, $20.5 ARTQX, $12.5 VFSUX, and 5sh CIU. The trades settle 1/9/15 and 1/13/15."

 

[danelskibr]

PARTIALLY SOLVED

Since I am stuck in the world of formulas, I wrote this (with help):


=IF(K5="Invest Cash","I invested excess cash by purchasing ",IF(K5="Raise Cash","I raised the required cash by selling ",""))&IF(G16<>"",IF(RIGHT(G16,2)="sh",CONCATENATE(G16," ",A16,","),CONCATENATE(IF(LEN(G16)<4,"$"&G16,"$"&INT(G16/100)/10&"k")," ",A16)),IF(A16<>"",IF(E16<>"",IF(RIGHT(E16,2)="sh",CONCATENATE(E16," ",A16,","),CONCATENATE(IF(LEN(E16)<4,"$"&E16,"$"&INT(E16/100)/10&"k")," ",A16)),"END OF FIRST IF")))


This formula can accomplish pretty much all of what I described above. I now need the VBA experts to give me a hand in applying this formula to the applicable cells.

I need to run a section of this formula (the section after the first "&" sign) for all the rows that have contents in the Range(A16:A48) and (J16:J48). I am thinking this will be some sort of End Up code with a loop?? Ideally it would run the formula for A16:A48 and then J16:J48.

1) Im thinking something like EndUp to select the range of data in A16:A48. Then run THE WHOLE formula above. Go to the next row, run the formula AFTER the first "&" sign.

2) Repeat step 2 until the last row in the modified range.

3) Repeat step 2 until the last row in the modified range for J16:J48.


Remember all of this data needs to end up strung together in cell J6.

The only problem is that the formula will need to be modified for each row to reflect the data in that row (not the row above).


I think Im getting close!