Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
I searched the posts, but didn't find anything obvious that would work in my
situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Maybe
=LOOKUP(2,1/(A1:A100=C1),B1:B100) Where C1 is the part No you are looking for. Mike "Dave" wrote: I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Maybe this if you want to get rid of the old data and only keep the most
recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Mike,
This "kind of" solved my problem, but not quite. The LOOKUP formula did return the highest revision number (in column C) for the part numbers in column A. I thought then I could filter the part number column and get all the latest revisions for the part numbers. But the formula "broke down" at row 4319 (of 61,228 rows) and started returning each revision in column B again. At part number 1330500 the formula started returning all revisions from column B, instead of the max revision as with part number 1330496: 1330493 A00 A00 1330494 A00 A01 1330494 A01 A01 1330495 A00 A01 1330495 A01 A01 1330496 A00 A01 1330496 A01 A01 1330497 A00 A00 1330498 A00 A00 1330499 A00 A00 1330500 A00 A00 1330500 A01 A01 1330503 100 100 1330503 A00 A00 1330504 100 100 1330504 A00 A00 1330505 100 100 1330505 A00 A00 1330506 100 100 1330506 101 101 Any idea why this happened? Is it my PC's memory? I have 3GB RAM. "Mike H" wrote: Maybe =LOOKUP(2,1/(A1:A100=C1),B1:B100) Where C1 is the part No you are looking for. Mike "Dave" wrote: I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Dave,
The formula I gave you didn't return anything from Column C only Column B. You may have thought it did because of similarities in the data. The formula isn't affected by the size of the range except that in Excel 2003 (Don't know about 2007) it won't work on full columns but this is tested and works on 65535 rows. =LOOKUP(2,1/(A1:A65535=C1),B1:B6535) Mike "Dave" wrote: Mike, This "kind of" solved my problem, but not quite. The LOOKUP formula did return the highest revision number (in column C) for the part numbers in column A. I thought then I could filter the part number column and get all the latest revisions for the part numbers. But the formula "broke down" at row 4319 (of 61,228 rows) and started returning each revision in column B again. At part number 1330500 the formula started returning all revisions from column B, instead of the max revision as with part number 1330496: 1330493 A00 A00 1330494 A00 A01 1330494 A01 A01 1330495 A00 A01 1330495 A01 A01 1330496 A00 A01 1330496 A01 A01 1330497 A00 A00 1330498 A00 A00 1330499 A00 A00 1330500 A00 A00 1330500 A01 A01 1330503 100 100 1330503 A00 A00 1330504 100 100 1330504 A00 A00 1330505 100 100 1330505 A00 A00 1330506 100 100 1330506 101 101 Any idea why this happened? Is it my PC's memory? I have 3GB RAM. "Mike H" wrote: Maybe =LOOKUP(2,1/(A1:A100=C1),B1:B100) Where C1 is the part No you are looking for. Mike "Dave" wrote: I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Biff,
Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Hmmm...
The results I get are not the same as the results you've posted. Here's a small sample file that demonstrates this: xMarkOld.xls 14kb http://cjoint.com/?gzxPE2vUKA In column C I've entered the formula but changed the "x" to "Keep" meaning, keep this record. Column D shows the "x" that you posted in your sample. As you'll see the "keeps" are in the correct places. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Hi, Biff-
I re-did the formula to specify "KEEP" and got the following result. The formula used is depicted in column D: PN REV FORMULA 400078 A KEEP 846261 A00 KEEP =IF(A8<A7,"KEEP","") 876000 A KEEP 876000 B 876001 A KEEP 876001 B 876002 A KEEP 876002 B 876003 A KEEP 876004 A KEEP 876005 A KEEP 876006 A KEEP 876007 A KEEP 876008 A KEEP The formula depicted is copied down thru Part Number 876008, and depicts the relative cells. But you'll notice that for PNs 876000 thru 876002 the wrong revision is depicted as a "KEEP". I dunno what's happening in my sheet. Your sheet looks good. Dave "T. Valko" wrote: Hmmm... The results I get are not the same as the results you've posted. Here's a small sample file that demonstrates this: xMarkOld.xls 14kb http://cjoint.com/?gzxPE2vUKA In column C I've entered the formula but changed the "x" to "Keep" meaning, keep this record. Column D shows the "x" that you posted in your sample. As you'll see the "keeps" are in the correct places. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
I suspect you're putting the formula in the wrong row. If your data starts
in A7 then put the formula in C7 then copy down. =IF(A8<A7,"KEEP","") -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Biff- I re-did the formula to specify "KEEP" and got the following result. The formula used is depicted in column D: PN REV FORMULA 400078 A KEEP 846261 A00 KEEP =IF(A8<A7,"KEEP","") 876000 A KEEP 876000 B 876001 A KEEP 876001 B 876002 A KEEP 876002 B 876003 A KEEP 876004 A KEEP 876005 A KEEP 876006 A KEEP 876007 A KEEP 876008 A KEEP The formula depicted is copied down thru Part Number 876008, and depicts the relative cells. But you'll notice that for PNs 876000 thru 876002 the wrong revision is depicted as a "KEEP". I dunno what's happening in my sheet. Your sheet looks good. Dave "T. Valko" wrote: Hmmm... The results I get are not the same as the results you've posted. Here's a small sample file that demonstrates this: xMarkOld.xls 14kb http://cjoint.com/?gzxPE2vUKA In column C I've entered the formula but changed the "x" to "Keep" meaning, keep this record. Column D shows the "x" that you posted in your sample. As you'll see the "keeps" are in the correct places. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Biff,
No, in my example, I just showed what the value was in cell D8. In cell D7 (for the row containing PN 400078) the formula is =IF (A7<A6,"KEEP",""). All the other rows have the same relative formula. Sorry for making it confusing.... Dave "T. Valko" wrote: I suspect you're putting the formula in the wrong row. If your data starts in A7 then put the formula in C7 then copy down. =IF(A8<A7,"KEEP","") -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Biff- I re-did the formula to specify "KEEP" and got the following result. The formula used is depicted in column D: PN REV FORMULA 400078 A KEEP 846261 A00 KEEP =IF(A8<A7,"KEEP","") 876000 A KEEP 876000 B 876001 A KEEP 876001 B 876002 A KEEP 876002 B 876003 A KEEP 876004 A KEEP 876005 A KEEP 876006 A KEEP 876007 A KEEP 876008 A KEEP The formula depicted is copied down thru Part Number 876008, and depicts the relative cells. But you'll notice that for PNs 876000 thru 876002 the wrong revision is depicted as a "KEEP". I dunno what's happening in my sheet. Your sheet looks good. Dave "T. Valko" wrote: Hmmm... The results I get are not the same as the results you've posted. Here's a small sample file that demonstrates this: xMarkOld.xls 14kb http://cjoint.com/?gzxPE2vUKA In column C I've entered the formula but changed the "x" to "Keep" meaning, keep this record. Column D shows the "x" that you posted in your sample. As you'll see the "keeps" are in the correct places. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Well, I don't know what's happening. At this point I'm out of "efficient"
suggestions. If you have 60,000 rows of data what I'm about to suggest will take forever to calculate. It may even lock-up Excel or your computer. =IF(COUNTIF(A$1:A$60000,A1)=COUNTIF(A$1:A1,A1),"x" ,"") Copied down. Personally, I would not attempt that! What you could do is every so many rows "reset" the formula by resetting the referenced range at a change in the pn. For example: This formula entered in C1:C1000 - =IF(COUNTIF(A$1:A$1000,A1)=COUNTIF(A$1:A1,A1),"x", "") This formula entered in C1001:C2000 - =IF(COUNTIF(A$1001:A$2000,A1001)=COUNTIF(A$1001:A1 001,A1001),"x","") Then follow that pattern until your done. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, No, in my example, I just showed what the value was in cell D8. In cell D7 (for the row containing PN 400078) the formula is =IF (A7<A6,"KEEP",""). All the other rows have the same relative formula. Sorry for making it confusing.... Dave "T. Valko" wrote: I suspect you're putting the formula in the wrong row. If your data starts in A7 then put the formula in C7 then copy down. =IF(A8<A7,"KEEP","") -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Biff- I re-did the formula to specify "KEEP" and got the following result. The formula used is depicted in column D: PN REV FORMULA 400078 A KEEP 846261 A00 KEEP =IF(A8<A7,"KEEP","") 876000 A KEEP 876000 B 876001 A KEEP 876001 B 876002 A KEEP 876002 B 876003 A KEEP 876004 A KEEP 876005 A KEEP 876006 A KEEP 876007 A KEEP 876008 A KEEP The formula depicted is copied down thru Part Number 876008, and depicts the relative cells. But you'll notice that for PNs 876000 thru 876002 the wrong revision is depicted as a "KEEP". I dunno what's happening in my sheet. Your sheet looks good. Dave "T. Valko" wrote: Hmmm... The results I get are not the same as the results you've posted. Here's a small sample file that demonstrates this: xMarkOld.xls 14kb http://cjoint.com/?gzxPE2vUKA In column C I've entered the formula but changed the "x" to "Keep" meaning, keep this record. Column D shows the "x" that you posted in your sample. As you'll see the "keeps" are in the correct places. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
Biff,
You were right! I was putting the formula one row too low. My bad! Works great now. Thanks for your patience! Dave "T. Valko" wrote: I suspect you're putting the formula in the wrong row. If your data starts in A7 then put the formula in C7 then copy down. =IF(A8<A7,"KEEP","") -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Biff- I re-did the formula to specify "KEEP" and got the following result. The formula used is depicted in column D: PN REV FORMULA 400078 A KEEP 846261 A00 KEEP =IF(A8<A7,"KEEP","") 876000 A KEEP 876000 B 876001 A KEEP 876001 B 876002 A KEEP 876002 B 876003 A KEEP 876004 A KEEP 876005 A KEEP 876006 A KEEP 876007 A KEEP 876008 A KEEP The formula depicted is copied down thru Part Number 876008, and depicts the relative cells. But you'll notice that for PNs 876000 thru 876002 the wrong revision is depicted as a "KEEP". I dunno what's happening in my sheet. Your sheet looks good. Dave "T. Valko" wrote: Hmmm... The results I get are not the same as the results you've posted. Here's a small sample file that demonstrates this: xMarkOld.xls 14kb http://cjoint.com/?gzxPE2vUKA In column C I've entered the formula but changed the "x" to "Keep" meaning, keep this record. Column D shows the "x" that you posted in your sample. As you'll see the "keeps" are in the correct places. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max value in one column based on another column
OK, good deal! Thanks for feeding back.
-- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, You were right! I was putting the formula one row too low. My bad! Works great now. Thanks for your patience! Dave "T. Valko" wrote: I suspect you're putting the formula in the wrong row. If your data starts in A7 then put the formula in C7 then copy down. =IF(A8<A7,"KEEP","") -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Biff- I re-did the formula to specify "KEEP" and got the following result. The formula used is depicted in column D: PN REV FORMULA 400078 A KEEP 846261 A00 KEEP =IF(A8<A7,"KEEP","") 876000 A KEEP 876000 B 876001 A KEEP 876001 B 876002 A KEEP 876002 B 876003 A KEEP 876004 A KEEP 876005 A KEEP 876006 A KEEP 876007 A KEEP 876008 A KEEP The formula depicted is copied down thru Part Number 876008, and depicts the relative cells. But you'll notice that for PNs 876000 thru 876002 the wrong revision is depicted as a "KEEP". I dunno what's happening in my sheet. Your sheet looks good. Dave "T. Valko" wrote: Hmmm... The results I get are not the same as the results you've posted. Here's a small sample file that demonstrates this: xMarkOld.xls 14kb http://cjoint.com/?gzxPE2vUKA In column C I've entered the formula but changed the "x" to "Keep" meaning, keep this record. Column D shows the "x" that you posted in your sample. As you'll see the "keeps" are in the correct places. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Biff, Thanks for the input. This works well, except for the part numbers which have only one revision. Then, in those cases, an "X" is placed in the column, making the sort be the same as for lower revisions of a part which has several revisions. Example: TEST A X TEST B TEST A00 400078 A X 846261 A00 X 876000 A X 876000 B 876001 A X 876001 B 876002 A X 876002 B 876003 A X 876004 A X 876005 A X 876006 A X 876007 A X 876008 A X 876008 B In the above exceprt, PNs 876004, 876005, 876006 (which have only one revision)have X's where PN 87008 has an X for the lower revision and a blank for the higher revision (B). I need to have 874004,876005,876006 return the same value in column C as 876008 revision B. Thanks, Dave "T. Valko" wrote: Maybe this if you want to get rid of the old data and only keep the most recent data (assuming that your data is sorted as is shown in your sample). Enter this formula in colun C and copy down to the end of data: =IF(A3<A2,"x","") The "x" will mark the latest revision. Now, convert the formulas to constants: Select the range of formulas in column C. Goto EditCopy Then, EditPaste SpecialValuesOK Now, sort the entire range on column C in descending order. This will put all the latest revisions at the top. Find the last "x" and delete everything after it. Then delete column C. -- Biff Microsoft Excel MVP "Dave" wrote in message ... I searched the posts, but didn't find anything obvious that would work in my situation. I have a list of part numbers in column A, and their revisions in column B. One part number can have 1 or more revisions. I'd like to find the latest revision for each part number in column A. My list looks like: PART NUMBER Revision 1120007 A 1120007 B00 5360120 A00 5360120 A01 5360120 D00 and so on I have 51,000+ rows of data, and would like to summarize by finding only the latest revisions of the parts- in this case it would be B00 for PN 1120007 and D00 for PN 5360120. I can probably boil down that 51,000 part number rows to 3,000 to 4,000. I sense it would be pretty simple, but I'm a relative novice when it comes to Excel. Any help would be greatly appreciated. Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
How to find the most recent date in a column based on other column | Excel Worksheet Functions | |||
Find and sum values based on a column search | Excel Discussion (Misc queries) | |||
Find a time value in one column based on names in another | Excel Discussion (Misc queries) |