Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
I have a list of Dates in Col. A
Column B contains both numerical and text values. I need to define a value in column B, and create a list of the dates that these occured on, on another sheet. Auto filter doesn't work because there are several different columns. If I try to use it I also get the values in the other columns. A B 1/2 8 1/3 4 1/4 Vac 1/5 8 1/6 7 1/7 Vac 1/8 8 Value needed = Vac Solution 1/4 1/7 Thanks for any and all help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Hi!
Entered as an array with the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(Sheet1!B$2:B$8,"vac"),INDE X(Sheet1!A$2:A$8,SMALL(IF(Sheet1!B$2:B$8="vac",ROW (Sheet1!A$2:A$8)-ROW(Sheet1!A$2)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "smoore" wrote in message oups.com... I have a list of Dates in Col. A Column B contains both numerical and text values. I need to define a value in column B, and create a list of the dates that these occured on, on another sheet. Auto filter doesn't work because there are several different columns. If I try to use it I also get the values in the other columns. A B 1/2 8 1/3 4 1/4 Vac 1/5 8 1/6 7 1/7 Vac 1/8 8 Value needed = Vac Solution 1/4 1/7 Thanks for any and all help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
P.S.
Format the cells as DATE Biff "Biff" wrote in message ... Hi! Entered as an array with the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(Sheet1!B$2:B$8,"vac"),INDE X(Sheet1!A$2:A$8,SMALL(IF(Sheet1!B$2:B$8="vac",ROW (Sheet1!A$2:A$8)-ROW(Sheet1!A$2)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "smoore" wrote in message oups.com... I have a list of Dates in Col. A Column B contains both numerical and text values. I need to define a value in column B, and create a list of the dates that these occured on, on another sheet. Auto filter doesn't work because there are several different columns. If I try to use it I also get the values in the other columns. A B 1/2 8 1/3 4 1/4 Vac 1/5 8 1/6 7 1/7 Vac 1/8 8 Value needed = Vac Solution 1/4 1/7 Thanks for any and all help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Biff, Thanks for trying to help me. I've been scratching various
parts of my anatomy for some time over this. I'm glad someone has finally come up with a solution. Now need a little more help with the tweaking to get it to work in my real worksheet. I've entered the following function in a sheet called "Summary", all my data is in a sheet called "Attendance" . You will see I've attempted to rewrite your function for this , but I'm still not getting it right. Can you steer a rookie a little further? Thanks. IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"") When I go to implement this it returns an error and highlights the double quotation marks at the very end. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
smoore,
looks like it wants a comma before " ". Beege "smoore" wrote in message oups.com... Biff, Thanks for trying to help me. I've been scratching various parts of my anatomy for some time over this. I'm glad someone has finally come up with a solution. Now need a little more help with the tweaking to get it to work in my real worksheet. I've entered the following function in a sheet called "Summary", all my data is in a sheet called "Attendance" . You will see I've attempted to rewrite your function for this , but I'm still not getting it right. Can you steer a rookie a little further? Thanks. IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"") When I go to implement this it returns an error and highlights the double quotation marks at the very end. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Hi!
Change this portion: ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2) To be that of your actual range: ROW(Attendance!B$3:B$317)-ROW(Attendance!B$3) And, make sure you enter the formula as an array. That means, instead of just hitting the ENTER key you MUST use the 3 key combination of CTRL,SHIFT,ENTER. Hold down both the CTRL key and the SHIFT key then hit ENTER. When done properly Excel will place squiggly braces { } around the formula. Also, if you edit the formula it must be re-entered as an array. Biff "smoore" wrote in message oups.com... Biff, Thanks for trying to help me. I've been scratching various parts of my anatomy for some time over this. I'm glad someone has finally come up with a solution. Now need a little more help with the tweaking to get it to work in my real worksheet. I've entered the following function in a sheet called "Summary", all my data is in a sheet called "Attendance" . You will see I've attempted to rewrite your function for this , but I'm still not getting it right. Can you steer a rookie a little further? Thanks. IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"") When I go to implement this it returns an error and highlights the double quotation marks at the very end. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Beege, your absolutely right, I'd just missed it. Thank you!
Biff, your a miracle worker! I would have never come up with this solution on my own. Thank you again! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Good eyes! I didn't see that!
Also, be sure to see my other follow-up about changing the ROW() references. Biff "Beege" wrote in message ... smoore, looks like it wants a comma before " ". Beege "smoore" wrote in message oups.com... Biff, Thanks for trying to help me. I've been scratching various parts of my anatomy for some time over this. I'm glad someone has finally come up with a solution. Now need a little more help with the tweaking to get it to work in my real worksheet. I've entered the following function in a sheet called "Summary", all my data is in a sheet called "Attendance" . You will see I've attempted to rewrite your function for this , but I'm still not getting it right. Can you steer a rookie a little further? Thanks. IF(ROWS($1:1)<=COUNTIF(Attendance!D$3:D$317,"Vac") ,Index(Attendance!B$3:B$317,SMALL(IF(Attendance!D$ 3:D$317="Vac",ROW(Attendance!A$2:A$8)-ROW(Attendance!A$2)+1),ROWS($1:1)))"") When I go to implement this it returns an error and highlights the double quotation marks at the very end. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Biff, after I had posted my rework, i noticed the same section of the
function you caught. It's working like a chwrm. If you have the time can you give me a quick synopsis of how these functions interact. On there own merit I can figure them out, but I haven't been able to wrap my ittle mind around what they cause each other to do. Thanks very much again. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Sure, I'll catch ya after Survivor!
Biff "smoore" wrote in message oups.com... Biff, after I had posted my rework, i noticed the same section of the function you caught. It's working like a chwrm. If you have the time can you give me a quick synopsis of how these functions interact. On there own merit I can figure them out, but I haven't been able to wrap my ittle mind around what they cause each other to do. Thanks very much again. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Sorry, I forgot about your follow-up!
Ok, here goes: I'll shorten the formula and make it easier to read by eliminating the sheet references: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "pusedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 ... A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 ... 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff "Biff" wrote in message ... Sure, I'll catch ya after Survivor! Biff "smoore" wrote in message oups.com... Biff, after I had posted my rework, i noticed the same section of the function you caught. It's working like a chwrm. If you have the time can you give me a quick synopsis of how these functions interact. On there own merit I can figure them out, but I haven't been able to wrap my ittle mind around what they cause each other to do. Thanks very much again. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Putting it all together. When copied down this is what you get:
INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) This may be a better representation: =IF(1<=2,INDEX(A$2:A$8,3)) = the 3 rd element of the indexed array = 1/4 =IF(2<=2,INDEX(A$2:A$8,6)) = the 6 th element of the indexed array = 1/7 =IF(3<=2,,"") = "" (blank) Biff "Biff" wrote in message ... Sorry, I forgot about your follow-up! Ok, here goes: I'll shorten the formula and make it easier to read by eliminating the sheet references: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"") The only part of the formula that you actually need is this: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) However, if you drag copy down once the data that meets the criteria is exhausted the next cell(s) will return #NUM! errors. Errors are unsightly and can cause problems in any downstream calculations. We can build an error trap in the formula that catches these errors so that they're not displayed and won't affect any downstream calculations. Excel has some error testing functions like : Iserror, Isna, Error.Type. Using the Iserror function to test for errors and "trap" them, the formula would look like this: =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:A$8,SMALL(IF (B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) As you can see, this makes the formula about twice as long and, if I was still using the Sheet references, this would make it even longer! Long formulas tend to "scare" people! Not only is the formula long but when the error trap evaluates to FALSE (no error) the formula has to process the data twice. So naturally, that takes twice as long. I used a "pusedo" error trap that effectively does the same thing but is much shorter to express and is more efficient: =IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac") =IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))) With the error trap I've used the formula only has to process the data once. The logic is that you count the number of instances that meet the criteria: COUNTIF(B$2:B$8,"vac") Then compare that to the number of cells that the formula is being copied to: ROWS($1:1) When you drag copy down to more cells the ROWS($1:1) function will increment to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this: =IF(1<=2,value_if_true,value_if_false) =IF(2<=2,value_if_true,value_if_false) =IF(3<=2,value_if_true,value_if_false) etc The value_if_true argument is: INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) The value_if_false argument is: "" Returns a blank cell instead of an error, #NUM! Now, let's see what's happening when the value_if_true argument is met. INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))) Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates. There are a total of 7 elements in the range A$2:A$8. The Index function holds these elements in a relative order. That order is the total number of elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe A2 = 1 A3 = 2 A4 = 3 .. A8 = 7 Now we need to tell the formula which elements of that range to return based on meeting the criteria. That criteria is: IF(B$2:B$8="vac" This will return an array of TRUE's or FALSE's. Based on the posted example that would be: FALSE FALSE TRUE FALSE FALSE TRUE FALSE Ok, the value_if_true argument is: ROW(A$2:A$8)-ROW(A$2)+1 And the value_if_false argument is nothing. No value_if_false argument was defined and when that happens the default return is FALSE. We'll see how that comes into play later on. Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1 Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7), we need a means of generating an array of numbers from 1 to 7 that correspond to the indexed elements. That's where ROW comes in handy. ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8 and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To take care of that we subtract the offset then add 1: -ROW(A$2)+1 This is how that is processed in the formula: 2 - 2 +1 = 1 3 - 2 + 1 =2 4 - 2 + 1 =3 5 - 2 + 1 =4 .. 8 - 2 + 1 =7 Now we have our array from 1 to 7 that correspond to the indexed array of 1 to 7. There are other ways to generate that array but this is the most "foolproof". So, now we put this all together to generate yet another array: If TRUE = ROW number, if FALSE = FALSE: B2 = vac = FALSE = FALSE B3 = vac = FALSE = FALSE B4 = vac = TRUE = 3 B5 = vac = FALSE = FALSE B6 = vac = FALSE = FALSE B7 = vac = TRUE = 6 B8 = vac = FALSE = FALSE That array is then passed to the SMALL function: SMALL({F,F,3,F,F,6,F},ROWS($1:1)) As is, that evaluates to the first smallest value which is 3. When drag copied down the ROWS function will increment to $1:2 for the second smallest, $1:3 for the third smallest, etc. Since there is no third smallest that would generate a #NUM! error but remember, we have that taken care of using our "psuedo" error trap. Putting it all together. When copied down this is what you get: INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4 INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7 INDEX(A$2:A$8,#NUM!) = "" (blank) There you have it! Biff "Biff" wrote in message ... Sure, I'll catch ya after Survivor! Biff "smoore" wrote in message oups.com... Biff, after I had posted my rework, i noticed the same section of the function you caught. It's working like a chwrm. If you have the time can you give me a quick synopsis of how these functions interact. On there own merit I can figure them out, but I haven't been able to wrap my ittle mind around what they cause each other to do. Thanks very much again. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List date adjacent to duplicates?
Biff, Thanks for an excellent tutorial. I'm printing this off and
filing it for future use. Thanks, again for all the help. Scotty |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Make a list that excludes duplicates | Excel Discussion (Misc queries) | |||
need help finding a Date range within long list | Excel Worksheet Functions | |||
grab max date from list | Excel Worksheet Functions | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |