| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help
I am trying to do the following. Assume data starting in row A1:
AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the
"Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
I need a formula that does this. I realize that this can be done by data
filter, however, is the formula that can do this? "Dave Peterson" wrote: I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the "Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
Couple of options:
Enter this formula in B1, and copy down as needed: =IF(COUNTIF(A$1:A1,A1)=1,A1,"") HOWEVER, this will *not* produce a contiguous list. There will be blank rows where it finds duplicates in the adjoining Column A. To display a contiguous list, with B1 containing =A1 try this *array* formula in *B2* =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A$1:$A$200),"",$A$1:$A$200 ),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *After* the CSE entry, copy down until you get a blank cell. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "A.S." wrote in message ... I need a formula that does this. I realize that this can be done by data filter, however, is the formula that can do this? "Dave Peterson" wrote: I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the "Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
Thanks! I think that array did it.
"RagDyer" wrote: Couple of options: Enter this formula in B1, and copy down as needed: =IF(COUNTIF(A$1:A1,A1)=1,A1,"") HOWEVER, this will *not* produce a contiguous list. There will be blank rows where it finds duplicates in the adjoining Column A. To display a contiguous list, with B1 containing =A1 try this *array* formula in *B2* =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A$1:$A$200),"",$A$1:$A$200 ),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *After* the CSE entry, copy down until you get a blank cell. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "A.S." wrote in message ... I need a formula that does this. I realize that this can be done by data filter, however, is the formula that can do this? "Dave Peterson" wrote: I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the "Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
Given the initial sample data, is there a formula that can take the max of
the column nex to duplicates, with the fact that the number of duplicates could vary)? So original data was, and let's assume there is data next to it in the next column: AB 2 AB-C 3 DEF 4 DEF 6 DEF-GH 3.4 DEF-GHI 3.8 DEF-GHI 4.5 JKL 7 JKL-M 7 JKL-MN 7.9 JKL-MN 7.9 JKL-MN 8.5 So with a formula the data then takes out the non-duplicates as we discussed but also gives back the max. Now max part cna be a separat formula because I can go back to the data to grab it but I just need to know how I can do this in a contiguous way. Sample answer to that data would be: AB 2 AB-C 3 DEF 6 DEF-GH 3.4 DEF-GHI 4.5 JKL 7 JKL-M 7 JKL-MN 8.5 "RagDyer" wrote: Couple of options: Enter this formula in B1, and copy down as needed: =IF(COUNTIF(A$1:A1,A1)=1,A1,"") HOWEVER, this will *not* produce a contiguous list. There will be blank rows where it finds duplicates in the adjoining Column A. To display a contiguous list, with B1 containing =A1 try this *array* formula in *B2* =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A$1:$A$200),"",$A$1:$A$200 ),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *After* the CSE entry, copy down until you get a blank cell. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "A.S." wrote in message ... I need a formula that does this. I realize that this can be done by data filter, however, is the formula that can do this? "Dave Peterson" wrote: I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the "Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
If you now have some values in column B next to your duplicates, then
the formula that Ragdyer gave you would have to be in column C and your derived maxima in column D, so put this array* formula in D1: =MAX(IF(A$1:A$200=C1,B$1:B$200)) and copy this down as required. * As this is an array formula, then you will have to commit it using CTRL-SHIFT-ENTER, as described earlier. Hope this helps. Pete On Mar 11, 10:40*pm, A.S. wrote: Given the initial sample data, is there a formula that can take the max of the column nex to duplicates, with the fact that the number of duplicates could vary)? So original data was, and let's assume there is data next to it in the next column: AB * * * * * * *2 AB-C * * * * * 3 DEF * * * * * *4 DEF * * * * * *6 DEF-GH * * * 3.4 DEF-GHI * * *3.8 DEF-GHI * * *4.5 JKL * * * * * * 7 JKL-M * * * * 7 JKL-MN * * * 7.9 JKL-MN * * * 7.9 JKL-MN * * * 8.5 So with a formula the data then takes out the non-duplicates as we discussed but also gives back the max. Now max part cna be a separat formula because I can go back to the data to grab it but I just need to know how I can do this in a contiguous way. Sample answer to that data would be: AB * * * * * * *2 AB-C * * * * * 3 DEF * * * * * *6 DEF-GH * * * 3.4 DEF-GHI * * *4.5 JKL * * * * * * 7 JKL-M * * * * 7 JKL-MN * * * 8.5 "RagDyer" wrote: Couple of options: Enter this formula in B1, and copy down as needed: =IF(COUNTIF(A$1:A1,A1)=1,A1,"") HOWEVER, this will *not* produce a contiguous list. There will be blank rows where it finds duplicates in the adjoining Column A. To display a contiguous list, with B1 containing =A1 try this *array* formula in *B2* =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A*$1:$A$200),"",$A$1:$A$20 0),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. *Also, you must use CSE when revising the formula. *After* the CSE entry, copy down until you get a blank cell. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "A.S." wrote in message ... I need a formula that does this. I realize that this can be done by data filter, however, is the formula that can do this? "Dave Peterson" wrote: I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the "Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
I think this did it. Thank You!
"Pete_UK" wrote: If you now have some values in column B next to your duplicates, then the formula that Ragdyer gave you would have to be in column C and your derived maxima in column D, so put this array* formula in D1: =MAX(IF(A$1:A$200=C1,B$1:B$200)) and copy this down as required. * As this is an array formula, then you will have to commit it using CTRL-SHIFT-ENTER, as described earlier. Hope this helps. Pete On Mar 11, 10:40 pm, A.S. wrote: Given the initial sample data, is there a formula that can take the max of the column nex to duplicates, with the fact that the number of duplicates could vary)? So original data was, and let's assume there is data next to it in the next column: AB 2 AB-C 3 DEF 4 DEF 6 DEF-GH 3.4 DEF-GHI 3.8 DEF-GHI 4.5 JKL 7 JKL-M 7 JKL-MN 7.9 JKL-MN 7.9 JKL-MN 8.5 So with a formula the data then takes out the non-duplicates as we discussed but also gives back the max. Now max part cna be a separat formula because I can go back to the data to grab it but I just need to know how I can do this in a contiguous way. Sample answer to that data would be: AB 2 AB-C 3 DEF 6 DEF-GH 3.4 DEF-GHI 4.5 JKL 7 JKL-M 7 JKL-MN 8.5 "RagDyer" wrote: Couple of options: Enter this formula in B1, and copy down as needed: =IF(COUNTIF(A$1:A1,A1)=1,A1,"") HOWEVER, this will *not* produce a contiguous list. There will be blank rows where it finds duplicates in the adjoining Column A. To display a contiguous list, with B1 containing =A1 try this *array* formula in *B2* =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($AÂ*$1:$A$200),"",$A$1:$A$2 00),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *After* the CSE entry, copy down until you get a blank cell. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "A.S." wrote in message ... I need a formula that does this. I realize that this can be done by data filter, however, is the formula that can do this? "Dave Peterson" wrote: I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the "Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help
You're welcome - thanks for feeding back.
Pete On Mar 12, 7:31*pm, A.S. wrote: I think this did it. Thank You! "Pete_UK" wrote: If you now have some values in column B next to your duplicates, then the formula that Ragdyer gave you would have to be in column C and your derived maxima in column D, so put this array* formula in D1: =MAX(IF(A$1:A$200=C1,B$1:B$200)) and copy this down as required. * As this is an array formula, then you will have to commit it using CTRL-SHIFT-ENTER, as described earlier. Hope this helps. Pete On Mar 11, 10:40 pm, A.S. wrote: Given the initial sample data, is there a formula that can take the max of the column nex to duplicates, with the fact that the number of duplicates could vary)? So original data was, and let's assume there is data next to it in the next column: AB * * * * * * *2 AB-C * * * * * 3 DEF * * * * * *4 DEF * * * * * *6 DEF-GH * * * 3.4 DEF-GHI * * *3.8 DEF-GHI * * *4.5 JKL * * * * * * 7 JKL-M * * * * 7 JKL-MN * * * 7.9 JKL-MN * * * 7.9 JKL-MN * * * 8.5 So with a formula the data then takes out the non-duplicates as we discussed but also gives back the max. Now max part cna be a separat formula because I can go back to the data to grab it but I just need to know how I can do this in a contiguous way. Sample answer to that data would be: AB * * * * * * *2 AB-C * * * * * 3 DEF * * * * * *6 DEF-GH * * * 3.4 DEF-GHI * * *4.5 JKL * * * * * * 7 JKL-M * * * * 7 JKL-MN * * * 8.5 "RagDyer" wrote: Couple of options: Enter this formula in B1, and copy down as needed: =IF(COUNTIF(A$1:A1,A1)=1,A1,"") HOWEVER, this will *not* produce a contiguous list. There will be blank rows where it finds duplicates in the adjoining Column A. To display a contiguous list, with B1 containing =A1 try this *array* formula in *B2* =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A**$1:$A$200),"",$A$1:$A$2 00),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. *Also, you must use CSE when revising the formula. *After* the CSE entry, copy down until you get a blank cell. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "A.S." wrote in message ... I need a formula that does this. I realize that this can be done by data filter, however, is the formula that can do this? "Dave Peterson" wrote: I'd a a header in row 1 and then use Data|Filter|Advanced filter and use the "Unique records only" checkbox. Debra Dalgleish describes it he http://contextures.com/xladvfilter01.html#FilterUR A.S. wrote: I am trying to do the following. Assume data starting in row A1: AB AB-C DEF DEF DEF-GH DEF-GHI DEF-GHI JKL JKL-M JKL-MN JKL-MN JKL-MN Basically, what I would like to happen starting in B2 (since B1 will alwaysequal to A1), is that it looks at B2, and if B2 is not a duplicate of anything above it puts B2, otherwise, if it is a duplicate it moves on and puts B3, so long as B3 is not a duplicate of anything from above, and so and so forth. So the answer to the above data would look like: AB AB-C DEF DEF-GH DEF-GHI JKL JKL-M JKL-MN -- Dave Peterson- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
