Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
I use Excel 2003.
I pulled good info off this forum and off other sites referenced within. I can do what Im trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Heres my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. Thanks. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Your criteria is quite complex. Here's one formulas way to model it up, using
a couple of cols for better clarity in the processes involved .. Source data as posted in A2:C6, with real dates presumed chronologic in C2 down In D2: =COUNTIF($B$2:B2,B2) In E2, normal ENTER: =IF($D21,INDEX(C$2:C$6,MATCH(1,INDEX((B$2:B$6=$B2 )*(D$2:D$6=$D2-1),),0)),"") In F2, normal ENTER: =IF($D21,INDEX(A$2:A$6,MATCH(1,INDEX((B$2:B$6=$B2 )*(D$2:D$6=$D2-1),),0)),"") Then in G2: =IF(E2="","",IF(AND(C2-E2<180,(A2-F2)/F225%),"x","")) Copy D2:G2 down to G6. Col G will flag those cases which satisfy the complex criteria with "x". Col D marks the instances of the addresses in col B. Cols E/F then extracts the cost/date of the (n-1)th instance from cols A and C. Col G finally computes the required comparisons/criteria of dates < 6 mths* apart & cost delta 25%, and flags such cases. *taking 1 mth = 30 days Adapt to suit the extents of your actuals. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what Im trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Heres my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
If we are permitted to sort by address (since I assume you want less than 6
months, 25% increase for SAME address), then this formula =IF(AND(MONTH(C3)-MONTH(C2)<6,(A3-A2)/A20.25),"X","") placed in D3 and copied down the column results in an X next to the Oct sales of Oak Ave. Note in row 1 I have labels; in row 2 I have the first sale and I have sorted the data by Address. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bad_Shot" wrote in message ... I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what I'm trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Here's my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. Thanks. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
WOW Thanks Max. I'll give it a try and let you know.
"Max" wrote: Your criteria is quite complex. Here's one formulas way to model it up, using a couple of cols for better clarity in the processes involved .. Source data as posted in A2:C6, with real dates presumed chronologic in C2 down In D2: =COUNTIF($B$2:B2,B2) In E2, normal ENTER: =IF($D21,INDEX(C$2:C$6,MATCH(1,INDEX((B$2:B$6=$B2 )*(D$2:D$6=$D2-1),),0)),"") In F2, normal ENTER: =IF($D21,INDEX(A$2:A$6,MATCH(1,INDEX((B$2:B$6=$B2 )*(D$2:D$6=$D2-1),),0)),"") Then in G2: =IF(E2="","",IF(AND(C2-E2<180,(A2-F2)/F225%),"x","")) Copy D2:G2 down to G6. Col G will flag those cases which satisfy the complex criteria with "x". Col D marks the instances of the addresses in col B. Cols E/F then extracts the cost/date of the (n-1)th instance from cols A and C. Col G finally computes the required comparisons/criteria of dates < 6 mths* apart & cost delta 25%, and flags such cases. *taking 1 mth = 30 days Adapt to suit the extents of your actuals. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what Im trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Heres my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Thanks.
Bernard Yes correct on both assumptions. I can sort by address or any other col. The data starts off with A-U. I then ,via a macro move, it to another worksheet, deleted stuff I don't need, concatenate two cols to build the address. Here's were I would use either your suggestion or Max's and determine whether the houses have been flipped (sold for more then 25% in 6 months). "Bernard Liengme" wrote: If we are permitted to sort by address (since I assume you want less than 6 months, 25% increase for SAME address), then this formula =IF(AND(MONTH(C3)-MONTH(C2)<6,(A3-A2)/A20.25),"X","") placed in D3 and copied down the column results in an X next to the Oct sales of Oak Ave. Note in row 1 I have labels; in row 2 I have the first sale and I have sorted the data by Address. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bad_Shot" wrote in message ... I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what I'm trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Here's my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. Thanks. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Hi,
Here is a simple way - I will assume the addresses are in column B and the dates in C, starting on row 2 with titles on row 1. 1. Sort by Address as 1st key, and Date as 2nd key. 2. Highlight all the dates and choose Format, Conditional Formatting, from the first drop down pick Formula is, in the second box enter the formula =AND(DATEDIF(C2,C3,"M")<6,B2=B3) Click Format and choose a color on the Patterns tab. Click OK twice. This will format the date of all the sales that are less than 6 months apart for a prarticular address. You could highlight all the data and repeat the above process but change the formula to read =AND(DATEDIF($C2,$C3,"M")<6,$B2=$B3) This would format the entire row. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Bad_Shot" wrote: Thanks. Bernard Yes correct on both assumptions. I can sort by address or any other col. The data starts off with A-U. I then ,via a macro move, it to another worksheet, deleted stuff I don't need, concatenate two cols to build the address. Here's were I would use either your suggestion or Max's and determine whether the houses have been flipped (sold for more then 25% in 6 months). "Bernard Liengme" wrote: If we are permitted to sort by address (since I assume you want less than 6 months, 25% increase for SAME address), then this formula =IF(AND(MONTH(C3)-MONTH(C2)<6,(A3-A2)/A20.25),"X","") placed in D3 and copied down the column results in an X next to the Oct sales of Oak Ave. Note in row 1 I have labels; in row 2 I have the first sale and I have sorted the data by Address. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bad_Shot" wrote in message ... I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what I'm trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Here's my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. Thanks. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Shane;
This works, but I've been here before. It still requires me to sreach thru the 1500 rows to fine the highligthed ones and do the calculations. "Shane Devenshire" wrote: Hi, Here is a simple way - I will assume the addresses are in column B and the dates in C, starting on row 2 with titles on row 1. 1. Sort by Address as 1st key, and Date as 2nd key. 2. Highlight all the dates and choose Format, Conditional Formatting, from the first drop down pick Formula is, in the second box enter the formula =AND(DATEDIF(C2,C3,"M")<6,B2=B3) Click Format and choose a color on the Patterns tab. Click OK twice. This will format the date of all the sales that are less than 6 months apart for a prarticular address. You could highlight all the data and repeat the above process but change the formula to read =AND(DATEDIF($C2,$C3,"M")<6,$B2=$B3) This would format the entire row. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Bad_Shot" wrote: Thanks. Bernard Yes correct on both assumptions. I can sort by address or any other col. The data starts off with A-U. I then ,via a macro move, it to another worksheet, deleted stuff I don't need, concatenate two cols to build the address. Here's were I would use either your suggestion or Max's and determine whether the houses have been flipped (sold for more then 25% in 6 months). "Bernard Liengme" wrote: If we are permitted to sort by address (since I assume you want less than 6 months, 25% increase for SAME address), then this formula =IF(AND(MONTH(C3)-MONTH(C2)<6,(A3-A2)/A20.25),"X","") placed in D3 and copied down the column results in an X next to the Oct sales of Oak Ave. Note in row 1 I have labels; in row 2 I have the first sale and I have sorted the data by Address. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bad_Shot" wrote in message ... I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what I'm trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Here's my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. Thanks. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Bernard;
We must have something wrong here. This is producing a "X" for duplicate dates, regardless of the address. I need to play with it some more. Thanks "Bernard Liengme" wrote: If we are permitted to sort by address (since I assume you want less than 6 months, 25% increase for SAME address), then this formula =IF(AND(MONTH(C3)-MONTH(C2)<6,(A3-A2)/A20.25),"X","") placed in D3 and copied down the column results in an X next to the Oct sales of Oak Ave. Note in row 1 I have labels; in row 2 I have the first sale and I have sorted the data by Address. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bad_Shot" wrote in message ... I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what I'm trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Here's my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. Thanks. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Max;
Once I sorted the data, 1st by address, then by Date I got some results. Unique addresses produced a "1", the second occurance of that address produced a "2". The next formulars produced "#NA" results in their respective fields. I need to break down the nested Index/Match/Index to find out what's going on. Thanks "Max" wrote: Your criteria is quite complex. Here's one formulas way to model it up, using a couple of cols for better clarity in the processes involved .. Source data as posted in A2:C6, with real dates presumed chronologic in C2 down In D2: =COUNTIF($B$2:B2,B2) In E2, normal ENTER: =IF($D21,INDEX(C$2:C$6,MATCH(1,INDEX((B$2:B$6=$B2 )*(D$2:D$6=$D2-1),),0)),"") In F2, normal ENTER: =IF($D21,INDEX(A$2:A$6,MATCH(1,INDEX((B$2:B$6=$B2 )*(D$2:D$6=$D2-1),),0)),"") Then in G2: =IF(E2="","",IF(AND(C2-E2<180,(A2-F2)/F225%),"x","")) Copy D2:G2 down to G6. Col G will flag those cases which satisfy the complex criteria with "x". Col D marks the instances of the addresses in col B. Cols E/F then extracts the cost/date of the (n-1)th instance from cols A and C. Col G finally computes the required comparisons/criteria of dates < 6 mths* apart & cost delta 25%, and flags such cases. *taking 1 mth = 30 days Adapt to suit the extents of your actuals. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: I use Excel 2003. I pulled good info off this forum and off other sites referenced within. I can do what Im trying to do with filters, formulas and macros, etc. But to I can do it faster with a calculator. Heres my data: A B C 1 Sale Price Address Sale Date 2 $25000 216 Oak Ave 12/7/2007 3 $35500 14 Wyman St 1/12/2008 4 $29000 216 Oak Ave 7/1/2008 5 $49000 14 Wyman St 9/30/2008 6 $45000 216 Oak Ave 10/2/2008 I need to find all sales that were less then 6 months apart, and the sales price was 25% greater then the previous price. In this case row 6 would be true. Note the rows can be from 250 to 1500 per month. |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
My earlier solution doesn't require any sorting by Address,
it should work "as-is", with the only presumption as stated: .. real dates presumed chronologic in C2 down Perhaps rummaging through this working sample might help: http://freefilehosting.net/download/44fl3 Duplicates flagging w criteria.xls The #N/As that you're hitting could be due to some "dirty" source data somewhere that's tripping up the processing, eg: formula returned error values, extra spaces, invalid dates, etc. Try sleuthing around for these, and clean it up. If you still can't get it to work, post a link to a desensitised sample file (you can use the same filehost above to upload) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: Max; Once I sorted the data, 1st by address, then by Date I got some results. Unique addresses produced a "1", the second occurance of that address produced a "2". The next formulas produced "#NA" results in their respective fields. I need to break down the nested Index/Match/Index to find out what's going on. |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Max;
Got your link, See that it works. I added some more data to your sheet and it still works. I've got to get back to my sheet. I must have a ref wrong when typing it in. I actually have 25 cols of data, vs. the simplified stuff I posted. I'll keep you posted. Thanks "Max" wrote: My earlier solution doesn't require any sorting by Address, it should work "as-is", with the only presumption as stated: .. real dates presumed chronologic in C2 down Perhaps rummaging through this working sample might help: http://freefilehosting.net/download/44fl3 Duplicates flagging w criteria.xls The #N/As that you're hitting could be due to some "dirty" source data somewhere that's tripping up the processing, eg: formula returned error values, extra spaces, invalid dates, etc. Try sleuthing around for these, and clean it up. If you still can't get it to work, post a link to a desensitised sample file (you can use the same filehost above to upload) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: Max; Once I sorted the data, 1st by address, then by Date I got some results. Unique addresses produced a "1", the second occurance of that address produced a "2". The next formulas produced "#NA" results in their respective fields. I need to break down the nested Index/Match/Index to find out what's going on. |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Max;
Got it working. Thanks. "Max" wrote: My earlier solution doesn't require any sorting by Address, it should work "as-is", with the only presumption as stated: .. real dates presumed chronologic in C2 down Perhaps rummaging through this working sample might help: http://freefilehosting.net/download/44fl3 Duplicates flagging w criteria.xls The #N/As that you're hitting could be due to some "dirty" source data somewhere that's tripping up the processing, eg: formula returned error values, extra spaces, invalid dates, etc. Try sleuthing around for these, and clean it up. If you still can't get it to work, post a link to a desensitised sample file (you can use the same filehost above to upload) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: Max; Once I sorted the data, 1st by address, then by Date I got some results. Unique addresses produced a "1", the second occurance of that address produced a "2". The next formulas produced "#NA" results in their respective fields. I need to break down the nested Index/Match/Index to find out what's going on. |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Great to hear that. Pl take a moment to click the YES buttons in all
responses which help to answer your query. Thanks. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: Max; Got it working. Thanks. |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Thanks Again;
Up for another question or should I make another post? As I stated before my rows can be from 250 to 1500. In the example below. I have to keep changing the last row to ensure I get all the data. I have about 50 formulars in the spread sheet that require changing. Any easy way to do this? Thanks again! =MEDIAN(IF(B30:B499<=(TODAY()-90),IF(B30:B499=(TODAY()-180),E30:E499))) "Max" wrote: Great to hear that. Pl take a moment to click the YES buttons in all responses which help to answer your query. Thanks. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bad_Shot" wrote: Max; Got it working. Thanks. |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating Duplicate Entries
Thanks for all the ratings.
Up for another question or should I make another post? Pl put in new queries as new posts. Maximizes your query's exposure to ALL responders, and it makes for better archiving/retrievals in future, too. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
duplicate entries | Excel Worksheet Functions | |||
Duplicate entries | Excel Discussion (Misc queries) | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Duplicate entries | New Users to Excel |