What formula to use to check current or previous owner?

Microsoft Office 2013 professional produ...
November 21, 2015 at 05:23:58
Specs: Windows 8.1
Hello all

I have some data that looks like the following:

	A	B	C		D		E		F		G
1	ID	Name	Date bought	Date sold	Numerator	denominator	Owner status
2	111	Matt	01-02-2002	01-01-2010	1		2	
3	111	Sam	01-02-2002	01-01-2010	1		2	
4	111	Matt	01-01-2010			1		1	
5	222	David	01-05-2008	01-01-2010	1		1	
6	222	Harold	01-01-2010			1		1	
7	333	David	01-01-2004	01-12-2008	1		1	
8	333	Sam	01-12-2008	01-01-2010	1		1	
9	333	Matt	01-01-2010			1		1	
10	444	Matt	01-01-2014			1		2	
11	444	David	01-01-2014			1		2	

As you can see, it just lists some item IDs, names of those who bought them, the date they did and if they bought all of that item or they bought a share together with a colleague. But how can I tag them as to weather they are the current owners or if they are previous owners of the item?

As you can see, the data I have avalible returns a sale date for all shareholders, when one of the shareholders are selling there share of the item. They then get a new "bought date". But as you can see, they still own some or all of that item, so they are still currently owning it.

Any suggestions to a solution. If column G could just return current or previous, then that would be fine.

Thanks many times in advance

/Jacob


See More: What formula to use to check current or previous owner?

Report •

#1
November 21, 2015 at 06:41:00
JacobJ,

Need more information on what exactly you are trying to do?
This looks like some type of Stock Trading journal, or Time Share tracking.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
November 21, 2015 at 07:31:45
Hello again

It is part of a mail merge solution I am working on. It lists a number of specific items in column A and depending on the bought and sold dates I would like to determind if the person who has owned the item still owns it or is the previous owner. The letters we are going to write to them has to be very specific about that, and therefore we need to say "You have owned 111" or "You own 111".

But because the dataset contains a sold date each time an owner sells a share of a given item, I can't figure out how to check for this.

If you look at the first row, Matt and Sam bought 1/2 of item 111 each the 1st february 2002 and they "both" sold that item again the 1. january 2010. Matt didnt really sell his share, but he just bought Sams and now he owns 1/1 of that item 111.

Hope you understand or else I am happy to try to explain it again.


Report •

#3
November 21, 2015 at 08:45:58
Try this and see if it works for you:

In cell G2 enter the formula: =IF($D2<>"","Previous","Current")

It simply check to see if Date Sold is Empty.
If it is NOT Empty, then item has been sold and the person is a Previous Owner.
If it is Empty, then item has not been sold and the person is the Current Owner..

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 21, 2015 at 10:22:39
Hello Mike

That I don't think will work, because it doesn't take the "new" buying date into account. If you see Matt in B2, he "sells", but then again buys Sams part of the item 111. So Matt will turn out to be previous owner in row 2, but then again he will be current owner in row 4. I think something with an array formula is needed, but again, thats why I am asking:D


Report •

#5
November 21, 2015 at 10:35:08
Using my formula this is what I get:

     A    B          C           D            E           F            G
 1) ID  Name    Date bought  Date sold    Numerator   Denominator  Owner status
 2) 111 Matt    01/02/2002   01/01/2010       1           2        Previous
 3) 111 Sam     01/02/2002   01/01/2010       1           2        Previous
 4) 111 Matt    01/01/2010                    1           1        Current
 5) 222 David   01/05/2008   01/01/2010       1           1        Previous
 6) 222 Harold  01/01/2010                    1           1        Current
 7) 333 David   01/01/2004   01/12/2008       1           1        Previous
 8) 333 Sam     01/12/2008   01/01/2010       1           1        Previous
 9) 333 Matt    01/01/2010                    1           1        Current
10) 444 Matt    01/01/2014                    1           2        Current
11) 444 David   01/01/2014                    1           2        Current

On line four it shows Matt as the current owner of all of 111
since there is no one else designated with ID 111.

Is this not correct?

MIKE

http://www.skeptic.com/


Report •

#6
November 21, 2015 at 12:34:00
Yes that is true, but Matt in line 2 is the same Matt as in line 4 for the ID 111. Therefore G2 should be Current as well, because he isnt actually a previous owner, he has been and still is the owner of item 111. I would like it to say either current in both G2 and G4 or maybe current in G2 and G4 would be empty, both ways are fine for my needs.

Report •

#7
November 21, 2015 at 13:16:00
OK, try this formula in G2

=IF(AND($D2<>"",SUMPRODUCT(--($A$1:$A$11=$A2),--($B$1:$B$11=$B2))>1),"Current",IF(D2<>"","Previous","Current"))

See how that works for you.

ALSO,

I notice that you one column headed Numerator and one headed Denominator

You can have 1/2 displayed in a cell,
use the Format Fraction instead of Number or General
You can still do math with them also.

MIKE

http://www.skeptic.com/


Report •

#8
November 22, 2015 at 09:23:33
Jacob,

Something was nagging me about your set up, and I finally figured it out.

In your set up, you have Matt buying 1/2 share along with Sam on 01/02/2002
You then have them both selling on 01/01/2010

BUT

Actually, Matt did not sell his original share,
he purchased Sam's half share.

So shouldn't your sheet actually look like this:


    A    B          C           D            E           F            G
 1) ID  Name    Date bought  Date sold    Numerator   Denominator  Owner status
 2) 111 Matt    01/02/2002                    1           2
 3) 111 Sam     01/02/2002   01/01/2010       1           2
 4) 111 Matt    01/01/2010                    1           2

Now if you try my original formula of:

=IF($D2<>"","Previous","Current")

You should get what your looking for.

MIKE

http://www.skeptic.com/


Report •

#9
November 23, 2015 at 01:25:45
Hello Mike

No actually, its not about what he buys, its about what he owns. So Matt does at that point own 1/1 of item 111. He now owns it all.

I have modified your formula to the following and it does work just as it should, only not in the example at the bottom (and sadly we get those kind of dublicate rows sometimes), but somehow I think its an easy fix, just dont know how..

=IF(AND($D2<>"",SUMPRODUCT(--($A$2:$A$10=$A2),--($B$2:$B$10=$B2),--($C$2:$C$10<>$D$2:$D$10))>1),"Current";IF(D2<>"","Previous","Current"))

Example that it doesnt work on.

  A    B          C           D            E           F            G
 1) ID  Name    Date bought  Date sold    Numerator   Denominator  Owner status
 2) 11122 Carl    01/02/2002   01/01/2010      1           2
 3) 11122 Tom     01/02/2002   01/01/2010      1           2
 4) 11122 Tom    01/01/2002                    1           2
 5) 11122 Tom    01/01/2002                    1           2
 6) 11122 Carl    01/01/2010                   1           1

message edited by JacobJ


Report •

#10
November 23, 2015 at 10:54:07
There is probably an easier way to do this,
but the best I can come up with is this.

You will need to add a "Helper" column.

So your data will look like:

     A     B         C            D        E    F      G         H
 1) ID   Name    Date bought  Date sold   Num Denom  Status    HELPER 
 2) 111  Matt    01/02/2002   01/01/2010   1    2              111Matt37258
 3) 111  Sam     01/02/2002   01/01/2010   1    2              111Sam37258
 4) 111  Matt    01/01/2010                1    1              111Matt40179
 5) 222  David   01/05/2008   01/01/2010   1    1              222David39452
 6) 222  Harold  01/01/2010                1    1              222Harold40179
 7) 333  David   01/01/2004   01/12/2008   1    1              333David37987
 8) 333  Sam     01/12/2008   01/01/2010   1    1              333Sam39459
 9) 333  Matt    01/01/2010                1    1              333Matt40179
10) 444  Matt    01/01/2014                1    2              444Matt41640
11) 444  David   01/01/2014                1    2              444David41640

In Cell H2 enter the formula: =A2&B2&C2
Drag down and you should get the above.
We are going to combine the three cells and use them as one value.

In cell G2 enter the formula:

=IF(AND(ISNA(MATCH($A2&$B2&$D2,$H$2:$H$11,0)),D2<>""),"Previous","Current")

Drag down and you should end up with:

     A     B         C            D        E    F      G         H
 1) ID   Name    Date bought  Date sold   Num Denom  Status    HELPER 
 2) 111  Matt    01/02/2002   01/01/2010   1    2    Current   111Matt37258
 3) 111  Sam     01/02/2002   01/01/2010   1    2    Previous  111Sam37258
 4) 111  Matt    01/01/2010                1    1    Current   111Matt40179
 5) 222  David   01/05/2008   01/01/2010   1    1    Previous  222David39452
 6) 222  Harold  01/01/2010                1    1    Current   222Harold40179
 7) 333  David   01/01/2004   01/12/2008   1    1    Previous  333David37987
 8) 333  Sam     01/12/2008   01/01/2010   1    1    Previous  333Sam39459
 9) 333  Matt    01/01/2010                1    1    Current   333Matt40179
10) 444  Matt    01/01/2014                1    2    Current   444Matt41640
11) 444  David   01/01/2014                1    2    Current   444David41640

It seems to work.

If you like, you can hide the helper column so it is not a distraction.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
November 25, 2015 at 02:11:46
Hello Again Mike

That approach Works fine, but it still doenst "clean" out the false rows. Take a look here at Tom in the bottom 5 rows. He has sold and he gets recognized as have bought it Again, altho that isnt the case because the date he "buys" it in row 2 and 3 from the bottom is before he sold it, which Means that is a false-data line that needs to be "cleaned" out. How could I add that check?

ID	Name	bought	        sold	        Num	Denom	Status	HELPER
111	Matt	01-02-2002	01-01-2010	1	2	Current	111Matt37288
111	Sam	01-02-2002	01-01-2010	1	2	Previous111Sam37288
111	Matt	01-01-2010		        1	1	Current	111Matt40179
222	David	01-05-2008	01-01-2010	1	1	Previous222David39569
222	Harold	01-01-2010		        1	1	Current	222Harold40179
333	David	01-01-2004	01-12-2008	1	1	Previous333David37987
333	Sam	01-12-2008	01-01-2010	1	1	Previous333Sam39783
333	Matt	01-01-2010		        1	1	Current	333Matt40179
444	Matt	01-01-2014		        1	2	Current	444Matt41640
444	David	01-01-2014		        1	2	Current	444David41640
555	Carl	01-01-2002	01-01-2005	1	2	Current	555Carl37257
555	tom	01-01-2002	01-01-2005	1	2	Previous555tom37257
555	tom	01-01-2002		        1	2	Current	555tom37257
555	tom	01-01-2002		        1	2	Current	555tom37257
555	Carl	01-01-2005		        1	1	Current	555Carl38353


Report •

#12
November 25, 2015 at 14:03:19
Sorry, I've run out of ideas.
I don't think your problems can be solved using a FORMULA
there may be a MACRO solution, but my macro skills are just above nil.

Hopefully, some one else can offer some Macro assistance.

MIKE

http://www.skeptic.com/


Report •

#13
December 3, 2015 at 04:42:19
Thanks for your help so far! It has helped me a lot and I can Work with your solutions, there are just a bit more manual Work to do when those repeated lines of data come out. If anyone else has a solution for the problem mentioned in #11 they are more than welcome to post it here.

Report •

#14
December 3, 2015 at 10:59:51
Jacob,

Try this, it is a separate operation from any of the previous formulas.

It is designed to find multiple purchases, of the same item, on the same date.

It uses Conditional Formatting to highlight any multiple purchases, IE greater than 1
and uses the Helper Column.

Conditional Formatting 2007

1) Select your cell or range of cells, IE A2:H16
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=COUNTIF($H$2:$H$16,$H2)>1

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

If there are any multiple purchases by the same person on the same day
they should be highlighted.

Does that work for you?

MIKE

http://www.skeptic.com/


Report •

Ask Question