Multiple Values Single Cell / Creative Alternatives

[garrettwebb]

Hello,

I am trying to put multiple values into a single cell. I have looked into Array Constants but I may need some additional help with implementing the solution. I am working on Windows 7 Excel 2007.

Let me start out with what I am trying to accomplish, and perhaps there is a better way to do this without trying to have multiple values in a single cell. Let's take stock certificates, numbered 1, 2, 3, 4, and 5. They were each newly issued and created. 1 & 2 are owned by Person A, 2 & 3 are owned by person B, and 5 is owned by Person C. Person A takes their two certs and has a new one issued, cert 6. Person C has their cert and splits into three new certs, 7, 8, and 9, and then sells cert 8 to Person B. Person B takes the newly purchased cert 8, puts it into their name (new cert 10 created). Then Person B takes all their certs and combines them and has a new cert 11 issued. I am trying to monitor all this certificate activity concisely for future analysis.


<table style="border-collapse: collapse; width: 370px; height: 273px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 43pt;" width="57" span="4"> <col style="width: 43pt;" width="57"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl66" style="height: 30pt; width: 43pt;" width="57" height="40">Cert</td> <td class="xl66" style="width: 43pt;" width="57">Name</td> <td class="xl67" style="width: 43pt;" width="57">Date
Issued</td> <td class="xl67" style="width: 43pt;" width="57">From
Cert(s)</td> <td class="xl67" style="width: 43pt;" width="57">To
Cert(s)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">1</td> <td class="xl66">A</td> <td class="xl66">date1</td> <td class="xl66">new</td> <td class="xl66">6</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">2</td> <td class="xl66">A</td> <td class="xl66">date1</td> <td class="xl66">new</td> <td class="xl66">6</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">3</td> <td class="xl66">B</td> <td class="xl66">date2</td> <td class="xl66">new</td> <td class="xl66">11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">4</td> <td class="xl66">B</td> <td class="xl66">date2</td> <td class="xl66">new</td> <td class="xl66">11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">5</td> <td class="xl66">C</td> <td class="xl66">date3</td> <td class="xl66">new</td> <td class="xl66">{6,7,8}</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">6</td> <td class="xl66">A</td> <td class="xl66">date4</td> <td class="xl66">{1,2}</td> <td class="xl66">open</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">7</td> <td class="xl66">C</td> <td class="xl66">date5</td> <td class="xl66">5</td> <td class="xl66">open</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">8</td> <td class="xl66">C</td> <td class="xl66">date5</td> <td class="xl66">5</td> <td class="xl66">11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">9</td> <td class="xl66">C</td> <td class="xl66">date5</td> <td class="xl66">5</td> <td class="xl66">open</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">10</td> <td class="xl66">B</td> <td class="xl66">date6</td> <td class="xl66">8</td> <td class="xl66">10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">11</td> <td class="xl66">B</td> <td class="xl66">date7</td> <td class="xl66">{3,4,10}</td> <td class="xl66">open</td> </tr> </tbody></table>

The "From Cert(s)" field is entered as a new certificate is issued, the "To Cert(s)" is created from the formula:

=IF(COUNTIF(J:J,A14)=0,"open",IF(COUNTIF(J:J,A14)>1,"multiple "&COUNTIF(J:J,A14),IF(COUNTIF(J:J,A14)=1,MATCH(A14,J:J,0)-2,"INVALID")))

J:J is the "From Cert(s)" column
A14 is the cert number

The problem is that I have to overwrite the "multiple" returned value with what I am looking for, for example, that is what would get returned for cert 5 in the example, then I would have to go retrieve the data myself. I do not see a simple way to have this data displayed concisely. I am also interested in having this data displayed visually, something along the lines of

1--\___6
2--/
3--\__________
4--/.................\
......./--7...........>--11
5--<---8---10---/
.......\--9

The periods "." are to be ignored, I don't know how to get the spaces to stay. My belief is that this will have to be done in something besides excel.

My goal here is to create an open dialogue with this problem and perhaps get some creative solutions for this issue or obtain some suggestions as to alternatively track the information. I am doing this all with formulas for now.

Thank you.

 

[Damon Ostrander]

Hi GarretWebb,

An interesting problem. I am amazed that you think you can even accomplish this using worksheet formulas. I wouldn't consider it easy in VBA, but at least it is possible. I think you are looking at 3 or more hours of work for an experienced VBA programmer.

I suggest you look at getting some consulting help, starting with a quote from MrExcel.

Damon

 

[garrettwebb]

Thanks for the response, Damon.

Unfortunately I think it is a little more complicated that that. This is essentially 1 major tracking device out of 3, and would ultimately like tieing all three pieces together. I've been using excel out of convenience because that is what I am most comfortable using. Eventually this whole system is going to have to be overhauled probably in some type of programming language. I am trying to work out the schematics and underlying fundamentals of the interrelationships between the sets of information before we implement the overhaul. I didn't realize that MrExcel did coding-for-hire, I'll look into it.

Thanks again.