Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entires in
Hi, I have multiple entries in column A with different information accross
the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entires in
1. Select the range in Col A including the header. You need to have headers
for the column 2. From menu DataFilterAdvanced FilterCopy to another location 3. In copy to I have selected G1 and check 'Unique records only' 4. Click OK will give you the unique list of reg number in ColG. 6. In H2 apply the below formula and copy down as required =MAX(IF($A$2:$A$100=G2,$B$2:$B$100)) Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
Thanks for the quick response!
It's not working for me :( I have the filtered unique entries list but I can't seem to get the array to work. I am using excel 2003, and have rows to 379 and columns across to BM. I substituted 'G' in your formula to BO (as this is where I put the filtered list) and '100' to 379, is that correct? I require all the information from each column I just want to hide the rows wich repeat in column A. Your help is very much appreciated.. :) Thanks "Jacob Skaria" wrote: 1. Select the range in Col A including the header. You need to have headers for the column 2. From menu DataFilterAdvanced FilterCopy to another location 3. In copy to I have selected G1 and check 'Unique records only' 4. Click OK will give you the unique list of reg number in ColG. 6. In H2 apply the below formula and copy down as required =MAX(IF($A$2:$A$100=G2,$B$2:$B$100)) Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
OK. So your data spans across columns A1 to BM379. If you are sure the dates
are in *** excel date format *** and not in text format; let us try the below 1. Suppose you have data in Sheet1. From Sheet2 access the menu DataFilterAutofilter and 'Copy to another location' . In list range type Sheet1!A1:A379 In copy to type/select cell A1 2. For copying headers use the below formula in Sheet2 B1 =Sheet1!B1 and copy the formula to the columns to the right upto BM 3. In cell B2 of Sheet2 apply below formula and copy that down to B379 =MAX(IF(Sheet1!$A$2:$A$379=A2,Sheet1!$B$2:$B$379)) PS: Please note that this is array entered.. 4. In cell C2 of sheet2 apply the below formula and copy across to BM2 =INDEX(Sheet1!C$2:C$379,MATCH(1,(Sheet1!$A$2:$A$37 9=$A2)*(Sheet1!$B$2:$B$379=$B2),0)) PS: Please note that this is array entered.. If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Thanks for the quick response! It's not working for me :( I have the filtered unique entries list but I can't seem to get the array to work. I am using excel 2003, and have rows to 379 and columns across to BM. I substituted 'G' in your formula to BO (as this is where I put the filtered list) and '100' to 379, is that correct? I require all the information from each column I just want to hide the rows wich repeat in column A. Your help is very much appreciated.. :) Thanks "Jacob Skaria" wrote: 1. Select the range in Col A including the header. You need to have headers for the column 2. From menu DataFilterAdvanced FilterCopy to another location 3. In copy to I have selected G1 and check 'Unique records only' 4. Click OK will give you the unique list of reg number in ColG. 6. In H2 apply the below formula and copy down as required =MAX(IF($A$2:$A$100=G2,$B$2:$B$100)) Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
The dates are in date format but not a standard one..
"2009-Sep-29" provided you enter it as 29/09/09 its a custom format of yyyy-mmm-dd;@ 1) ok, step one i guess u mean access Advancedfilter, as Auto filter doesn't work with no data selected. At this point am i meant to also check 'Unique records only'? 2) Step 1 has already copied the header from column A, No problem copying headers across.. 3) To Array enter I press Ctrl+Shift+Enter instead of just Enter, correct? When I do this it gives me a 0 value in most cells... 4) Are there any $ missing at the start of that formula? When I do this it gives me a #N/A in every cell Sorry to be a pain, but I'm pretty sure I'm following the instructions correctly "Jacob Skaria" wrote: OK. So your data spans across columns A1 to BM379. If you are sure the dates are in *** excel date format *** and not in text format; let us try the below 1. Suppose you have data in Sheet1. From Sheet2 access the menu DataFilterAutofilter and 'Copy to another location' . In list range type Sheet1!A1:A379 In copy to type/select cell A1 2. For copying headers use the below formula in Sheet2 B1 =Sheet1!B1 and copy the formula to the columns to the right upto BM 3. In cell B2 of Sheet2 apply below formula and copy that down to B379 =MAX(IF(Sheet1!$A$2:$A$379=A2,Sheet1!$B$2:$B$379)) PS: Please note that this is array entered.. 4. In cell C2 of sheet2 apply the below formula and copy across to BM2 =INDEX(Sheet1!C$2:C$379,MATCH(1,(Sheet1!$A$2:$A$37 9=$A2)*(Sheet1!$B$2:$B$379=$B2),0)) PS: Please note that this is array entered.. If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Thanks for the quick response! It's not working for me :( I have the filtered unique entries list but I can't seem to get the array to work. I am using excel 2003, and have rows to 379 and columns across to BM. I substituted 'G' in your formula to BO (as this is where I put the filtered list) and '100' to 379, is that correct? I require all the information from each column I just want to hide the rows wich repeat in column A. Your help is very much appreciated.. :) Thanks "Jacob Skaria" wrote: 1. Select the range in Col A including the header. You need to have headers for the column 2. From menu DataFilterAdvanced FilterCopy to another location 3. In copy to I have selected G1 and check 'Unique records only' 4. Click OK will give you the unique list of reg number in ColG. 6. In H2 apply the below formula and copy down as required =MAX(IF($A$2:$A$100=G2,$B$2:$B$100)) Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entires in
Hi,
Try this. 1. Suppose your data below is in range A1:B6 (including the headers). Select the range and assign it a name, say rawdata 2. Select the range (A1:B6) once again and convert it to a List (Ctrl+L) 3. Save the file on the desktop 4. Click on any blank cell 5. Go to Data Import External Data New Database Query Excel files and navigate to the Excel file on the desktop and select it 6. Click on Next and you will see a box for "Choose columns" 7. Select rawdata which appears in the left had side and click on the Greater than symbol. The tow columns will appear on the right-hand side box 8. Click on Next 3 times 9. On the last screen, select the second radio button. This will open up Ms Query 10. Now click on any cell in the Date column and click on the Sigma symbol 3 tiems until you see the maximum values show up 11. Now click on File Return Data to MS Office Excel 12. Select the range for the output and click on Finish 13. To update the output, right click Refresh. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sklyn" wrote in message ... Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
1. From sheet2 (blank sheet) try advanced filter. Yes you need to select
unique records In list range type Sheet1!A1:A379 In copy to type/select cell A1 3. Fine let us do this test with array formula. With data as below. enter the below. If it still returns 0 you are having problems with the date format...Try entering the dates manually (just for this test) using Ctrl+semicolon and then edit =MAX(IF($A$2:$A$10=A2,$B$2:$B$10)) ColA ColB ColC Rego# RegoDate =formula 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 4. $ is not there for the first range reference so that when you copy that to right it changes the column...upto BM...I have tried this with sample data.... If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: The dates are in date format but not a standard one.. "2009-Sep-29" provided you enter it as 29/09/09 its a custom format of yyyy-mmm-dd;@ 1) ok, step one i guess u mean access Advancedfilter, as Auto filter doesn't work with no data selected. At this point am i meant to also check 'Unique records only'? 2) Step 1 has already copied the header from column A, No problem copying headers across.. 3) To Array enter I press Ctrl+Shift+Enter instead of just Enter, correct? When I do this it gives me a 0 value in most cells... 4) Are there any $ missing at the start of that formula? When I do this it gives me a #N/A in every cell Sorry to be a pain, but I'm pretty sure I'm following the instructions correctly "Jacob Skaria" wrote: OK. So your data spans across columns A1 to BM379. If you are sure the dates are in *** excel date format *** and not in text format; let us try the below 1. Suppose you have data in Sheet1. From Sheet2 access the menu DataFilterAutofilter and 'Copy to another location' . In list range type Sheet1!A1:A379 In copy to type/select cell A1 2. For copying headers use the below formula in Sheet2 B1 =Sheet1!B1 and copy the formula to the columns to the right upto BM 3. In cell B2 of Sheet2 apply below formula and copy that down to B379 =MAX(IF(Sheet1!$A$2:$A$379=A2,Sheet1!$B$2:$B$379)) PS: Please note that this is array entered.. 4. In cell C2 of sheet2 apply the below formula and copy across to BM2 =INDEX(Sheet1!C$2:C$379,MATCH(1,(Sheet1!$A$2:$A$37 9=$A2)*(Sheet1!$B$2:$B$379=$B2),0)) PS: Please note that this is array entered.. If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Thanks for the quick response! It's not working for me :( I have the filtered unique entries list but I can't seem to get the array to work. I am using excel 2003, and have rows to 379 and columns across to BM. I substituted 'G' in your formula to BO (as this is where I put the filtered list) and '100' to 379, is that correct? I require all the information from each column I just want to hide the rows wich repeat in column A. Your help is very much appreciated.. :) Thanks "Jacob Skaria" wrote: 1. Select the range in Col A including the header. You need to have headers for the column 2. From menu DataFilterAdvanced FilterCopy to another location 3. In copy to I have selected G1 and check 'Unique records only' 4. Click OK will give you the unique list of reg number in ColG. 6. In H2 apply the below formula and copy down as required =MAX(IF($A$2:$A$100=G2,$B$2:$B$100)) Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entires in
Maybe try DataSort:
Select Rego# ascending, then by RegoDate descending, and click OK. Now select Rego# column, DataFilterAdvanced Filter: Check options Filter the list in Place, unique values, and click OK. This will show latest RegoDate for each Rego#. (Do this on a copy of the sheet or add a row identifier to keep original order.) "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
Still not workin,,,,
Seems this array doesn't like dates, I changed the date format as you suggested and still no luck. So I changed the dates to a random numeric value and hey presto it worked fine! Thanks again for all your help Jacob "Jacob Skaria" wrote: 1. From sheet2 (blank sheet) try advanced filter. Yes you need to select unique records In list range type Sheet1!A1:A379 In copy to type/select cell A1 3. Fine let us do this test with array formula. With data as below. enter the below. If it still returns 0 you are having problems with the date format...Try entering the dates manually (just for this test) using Ctrl+semicolon and then edit =MAX(IF($A$2:$A$10=A2,$B$2:$B$10)) ColA ColB ColC Rego# RegoDate =formula 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 4. $ is not there for the first range reference so that when you copy that to right it changes the column...upto BM...I have tried this with sample data.... If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: The dates are in date format but not a standard one.. "2009-Sep-29" provided you enter it as 29/09/09 its a custom format of yyyy-mmm-dd;@ 1) ok, step one i guess u mean access Advancedfilter, as Auto filter doesn't work with no data selected. At this point am i meant to also check 'Unique records only'? 2) Step 1 has already copied the header from column A, No problem copying headers across.. 3) To Array enter I press Ctrl+Shift+Enter instead of just Enter, correct? When I do this it gives me a 0 value in most cells... 4) Are there any $ missing at the start of that formula? When I do this it gives me a #N/A in every cell Sorry to be a pain, but I'm pretty sure I'm following the instructions correctly "Jacob Skaria" wrote: OK. So your data spans across columns A1 to BM379. If you are sure the dates are in *** excel date format *** and not in text format; let us try the below 1. Suppose you have data in Sheet1. From Sheet2 access the menu DataFilterAutofilter and 'Copy to another location' . In list range type Sheet1!A1:A379 In copy to type/select cell A1 2. For copying headers use the below formula in Sheet2 B1 =Sheet1!B1 and copy the formula to the columns to the right upto BM 3. In cell B2 of Sheet2 apply below formula and copy that down to B379 =MAX(IF(Sheet1!$A$2:$A$379=A2,Sheet1!$B$2:$B$379)) PS: Please note that this is array entered.. 4. In cell C2 of sheet2 apply the below formula and copy across to BM2 =INDEX(Sheet1!C$2:C$379,MATCH(1,(Sheet1!$A$2:$A$37 9=$A2)*(Sheet1!$B$2:$B$379=$B2),0)) PS: Please note that this is array entered.. If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Thanks for the quick response! It's not working for me :( I have the filtered unique entries list but I can't seem to get the array to work. I am using excel 2003, and have rows to 379 and columns across to BM. I substituted 'G' in your formula to BO (as this is where I put the filtered list) and '100' to 379, is that correct? I require all the information from each column I just want to hide the rows wich repeat in column A. Your help is very much appreciated.. :) Thanks "Jacob Skaria" wrote: 1. Select the range in Col A including the header. You need to have headers for the column 2. From menu DataFilterAdvanced FilterCopy to another location 3. In copy to I have selected G1 and check 'Unique records only' 4. Click OK will give you the unique list of reg number in ColG. 6. In H2 apply the below formula and copy down as required =MAX(IF($A$2:$A$100=G2,$B$2:$B$100)) Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
Thanks very much Ashish,
This worked perfect on a small scale test, now to try it on the whole spread... "Ashish Mathur" wrote: Hi, Try this. 1. Suppose your data below is in range A1:B6 (including the headers). Select the range and assign it a name, say rawdata 2. Select the range (A1:B6) once again and convert it to a List (Ctrl+L) 3. Save the file on the desktop 4. Click on any blank cell 5. Go to Data Import External Data New Database Query Excel files and navigate to the Excel file on the desktop and select it 6. Click on Next and you will see a box for "Choose columns" 7. Select rawdata which appears in the left had side and click on the Greater than symbol. The tow columns will appear on the right-hand side box 8. Click on Next 3 times 9. On the last screen, select the second radio button. This will open up Ms Query 10. Now click on any cell in the Date column and click on the Sigma symbol 3 tiems until you see the maximum values show up 11. Now click on File Return Data to MS Office Excel 12. Select the range for the output and click on Finish 13. To update the output, right click Refresh. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sklyn" wrote in message ... Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
hmmm, doesn't seem to be working for me..
I got the data into MS query but it did not filter the way i wanted it to... I'll elaborate more on the data in hopes you can help: The data is all related to vehicle records (rego, insurance, leasing, serviceing, etc) The spread looks something like this: VIN#---Rego#----Make---Rego'd on----Rego expires---Purchased---Sold----value A3B43--123456---Ford---01/10/08------30/09/09------01/10/06----01/10/09--$10000 A3B43--123456---Ford---01/10/07------30/09/08------01/10/06----01/10/09--$10000 A3B43--123456---Ford---01/10/06------30/09/07------01/10/06----01/10/09--$10000 B6G56--789456---Audi---22/09/08------21/09/09------21/09/06----NIL---------$12000 B6G56--789456---Audi---22/09/07------21/09/08------21/09/06----NIL---------$12000 B6G56--789456---Audi---22/09/06------21/09/07------21/09/06----NIL---------$12000 There are headings across to column BM and 379 rows of info. I wish to filter this to end up with: VIN#---Rego#----Make---Rego'd on----Rego expires---Purchased---Sold----value A3B43--123456---Ford---01/10/08------30/09/09------01/10/06----01/10/09--$10000 B6G56--789456---Audi---22/09/08------21/09/09------21/09/06----NIL---------$12000 So showing only the most recent entry for each vehicle. I also need to be able to add new rows to the master sheet and have them auto filtered onto the new sheet... "Sklyn" wrote: Thanks very much Ashish, This worked perfect on a small scale test, now to try it on the whole spread... "Ashish Mathur" wrote: Hi, Try this. 1. Suppose your data below is in range A1:B6 (including the headers). Select the range and assign it a name, say rawdata 2. Select the range (A1:B6) once again and convert it to a List (Ctrl+L) 3. Save the file on the desktop 4. Click on any blank cell 5. Go to Data Import External Data New Database Query Excel files and navigate to the Excel file on the desktop and select it 6. Click on Next and you will see a box for "Choose columns" 7. Select rawdata which appears in the left had side and click on the Greater than symbol. The tow columns will appear on the right-hand side box 8. Click on Next 3 times 9. On the last screen, select the second radio button. This will open up Ms Query 10. Now click on any cell in the Date column and click on the Sigma symbol 3 tiems until you see the maximum values show up 11. Now click on File Return Data to MS Office Excel 12. Select the range for the output and click on Finish 13. To update the output, right click Refresh. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sklyn" wrote in message ... Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
Fantastic! thanks and so simple!! I had tried using these functions but
couldn't get it to work , lol.. Now can you help with getting it to auto update when new rows are added?? Cheers "Lori Miller" wrote: Maybe try DataSort: Select Rego# ascending, then by RegoDate descending, and click OK. Now select Rego# column, DataFilterAdvanced Filter: Check options Filter the list in Place, unique values, and click OK. This will show latest RegoDate for each Rego#. (Do this on a copy of the sheet or add a row identifier to keep original order.) "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
Also I am trying to use it from a 2nd sheet.
main data is all on sheet1 and is all visible, on sheet2 each cell is =Sheet1!$A$1 so that it is a direct copy of sheet 1. I wish to apply the filters on sheet2, but it sees each cell as a unique value, sort still works fine just not filter.. is it possible to filter this way?? basically I want to use sheet1 to add my data to and have sheet2 for someone else to view only the recent info they want to see, and I want it to update as I enter new data into sheet1. "Sklyn" wrote: Fantastic! thanks and so simple!! I had tried using these functions but couldn't get it to work , lol.. Now can you help with getting it to auto update when new rows are added?? Cheers "Lori Miller" wrote: Maybe try DataSort: Select Rego# ascending, then by RegoDate descending, and click OK. Now select Rego# column, DataFilterAdvanced Filter: Check options Filter the list in Place, unique values, and click OK. This will show latest RegoDate for each Rego#. (Do this on a copy of the sheet or add a row identifier to keep original order.) "Sklyn" wrote: Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to filter unique entires in a column when differnet entire
Hi,
In step 7, after getting all column on the right hand side, click on the Rego expires field and press the less than symbol. In other words, have all the columns on the right except the Rego expires column. Now follow the procedure and you will get you result. Please note that this will have all the columns except Rego expires. To get rego expires, use the formula below Also, suppose the output (of MS Query) is in range B17:H19 (including the header row) and the source data is in range B3:I9 (including the header row). In cell I18, use the following array formula (Ctrl+Shift+Enter) to get the Reg expiry values =INDEX(B4:I9,MATCH(1,($B$4:$B$9=B18)*($E$4:$E$9=E1 8),0),5) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sklyn" wrote in message ... hmmm, doesn't seem to be working for me.. I got the data into MS query but it did not filter the way i wanted it to... I'll elaborate more on the data in hopes you can help: The data is all related to vehicle records (rego, insurance, leasing, serviceing, etc) The spread looks something like this: VIN#---Rego#----Make---Rego'd on----Rego expires---Purchased---Sold----value A3B43--123456---Ford---01/10/08------30/09/09------01/10/06----01/10/09--$10000 A3B43--123456---Ford---01/10/07------30/09/08------01/10/06----01/10/09--$10000 A3B43--123456---Ford---01/10/06------30/09/07------01/10/06----01/10/09--$10000 B6G56--789456---Audi---22/09/08------21/09/09------21/09/06----NIL---------$12000 B6G56--789456---Audi---22/09/07------21/09/08------21/09/06----NIL---------$12000 B6G56--789456---Audi---22/09/06------21/09/07------21/09/06----NIL---------$12000 There are headings across to column BM and 379 rows of info. I wish to filter this to end up with: VIN#---Rego#----Make---Rego'd on----Rego expires---Purchased---Sold----value A3B43--123456---Ford---01/10/08------30/09/09------01/10/06----01/10/09--$10000 B6G56--789456---Audi---22/09/08------21/09/09------21/09/06----NIL---------$12000 So showing only the most recent entry for each vehicle. I also need to be able to add new rows to the master sheet and have them auto filtered onto the new sheet... "Sklyn" wrote: Thanks very much Ashish, This worked perfect on a small scale test, now to try it on the whole spread... "Ashish Mathur" wrote: Hi, Try this. 1. Suppose your data below is in range A1:B6 (including the headers). Select the range and assign it a name, say rawdata 2. Select the range (A1:B6) once again and convert it to a List (Ctrl+L) 3. Save the file on the desktop 4. Click on any blank cell 5. Go to Data Import External Data New Database Query Excel files and navigate to the Excel file on the desktop and select it 6. Click on Next and you will see a box for "Choose columns" 7. Select rawdata which appears in the left had side and click on the Greater than symbol. The tow columns will appear on the right-hand side box 8. Click on Next 3 times 9. On the last screen, select the second radio button. This will open up Ms Query 10. Now click on any cell in the Date column and click on the Sigma symbol 3 tiems until you see the maximum values show up 11. Now click on File Return Data to MS Office Excel 12. Select the range for the output and click on Finish 13. To update the output, right click Refresh. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sklyn" wrote in message ... Hi, I have multiple entries in column A with different information accross the row (including dates). i want to filter for only the most recent ones.. e.g. Rego# RegoDate 123-456 29/09/09 123-456 29/09/08 123-456 29/09/07 789-123 29/09/09 789-123 29/09/08 I only want to see the most recent entry of each Rego# |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Inconsistant entires bewteen days | Excel Discussion (Misc queries) | |||
need help on count entires with conditions | Excel Worksheet Functions | |||
Stock Quotes #VALUE for some entires on update | Excel Discussion (Misc queries) | |||
How can I check if any of the entires is True in a range? | Excel Worksheet Functions | |||
How to add entires in column based on another column... | Excel Discussion (Misc queries) |