Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
My sheet contains in A1:B500 lots of pairs, some of which are repeated
............A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
When you say to remove duplicate rows does that mean you want to retain the
first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ............A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message oups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
On 16 jun, 22:41, "T. Valko" wrote:
When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message oups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, Yes, I want to retain the first instance and delete all the rest. The example you have given is not possible. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(rngA&rngB,rngA& rngB,0),MATCH(rngA&rngB,rngA&rngB,0))0,ROW(INDIRE CT("1:"&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA& "
"&rngB,SMALL(IF(FREQUENCY(MATCH(rngA&rngB,rngA&rng B,0),MATCH(rngA&rngB,rngA&rngB,0))0,ROW(INDIRECT( "1:"&ROWS(rngA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down "vsoler" wrote: My sheet contains in A1:B500 lots of pairs, some of which are repeated ............A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
This handles TEXT only.
A1:An = rng1 B1:Bn = rng2 Assuming you want to extract the uniques and sort ascending. Enter this formula in D1: =LOOKUP(2,1/(COUNTIF(rng1,"<"&rng1)=0)/ISTEXT(rng1),rng1) Enter this array formula** in D2: =IF(ROWS($1:2)<=SUM((rng1<"")/COUNTIF(rng1,rng1&"")),INDEX(rng1,MATCH(0,COUNTIF( rng1,"<"&rng1)-SUM(COUNTIF(rng1,D$1:D1)),0)),"") Copy down until you get blanks. Enter this formula in E1: =IF(D1="","",INDEX(rng2,MATCH(D1,rng1,0))) Copy down until you get blanks. Based on your sample data the result will be: ............D.............E 1.......cup........water 2.......light........team 3.......table.......cup 4.......water......wine 5............................ Biff "vsoler" wrote in message oups.com... On 16 jun, 22:41, "T. Valko" wrote: When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message oups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, Yes, I want to retain the first instance and delete all the rest. The example you have given is not possible. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
Another tinker to try, using non-array formulas ..
Source data assumed in cols A and B, from row1 down In C1: =IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT(($A$1:A1=A1)* ($B$1:B1=B1))1,"",CODE(LEFT(A1))+ROW()/10^10)) In D1: =IF(ROW()COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C :$C,ROW()),$C:$C,0))) Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of data in cols A and B, say down to E100? Hide away col C. Cols D and E will return the required results all neatly bunched at the top, viz. for the posted sample source data, you'd get: cup water light team table cup water wine -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
That might be good enough but it doesn't do a real sort.
Biff "Max" wrote in message ... Another tinker to try, using non-array formulas .. Source data assumed in cols A and B, from row1 down In C1: =IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT(($A$1:A1=A1)* ($B$1:B1=B1))1,"",CODE(LEFT(A1))+ROW()/10^10)) In D1: =IF(ROW()COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C :$C,ROW()),$C:$C,0))) Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of data in cols A and B, say down to E100? Hide away col C. Cols D and E will return the required results all neatly bunched at the top, viz. for the posted sample source data, you'd get: cup water light team table cup water wine -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
On 17 jun, 03:58, "T. Valko" wrote:
This handles TEXT only. A1:An = rng1 B1:Bn = rng2 Assuming you want to extract the uniques and sort ascending. Enter this formula in D1: =LOOKUP(2,1/(COUNTIF(rng1,"<"&rng1)=0)/ISTEXT(rng1),rng1) Enter this array formula** in D2: =IF(ROWS($1:2)<=SUM((rng1<"")/COUNTIF(rng1,rng1&"")),INDEX(rng1,MATCH(0,CO*UNTIF (rng1,"<"&rng1)-SUM(COUNTIF(rng1,D$1:D1)),0)),"") Copy down until you get blanks. Enter this formula in E1: =IF(D1="","",INDEX(rng2,MATCH(D1,rng1,0))) Copy down until you get blanks. Based on your sample data the result will be: ...........D.............E 1.......cup........water 2.......light........team 3.......table.......cup 4.......water......wine 5............................ Biff "vsoler" wrote in message oups.com... On 16 jun, 22:41, "T. Valko" wrote: When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message groups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, Yes, I want to retain the first instance and delete all the rest. The example you have given is not possible.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, Your solution is super! excellent! It assumes that both members of the pair are non null, but it is far more of what I would have expected to get in this newsgroup. It will take me a while to study how it works, and also trying to adapt it to a combination of text and numbers. Again, congratulations |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
On 17 jun, 12:35, Max wrote:
Another tinker to try, using non-array formulas .. Source data assumed in cols A and B, from row1 down In C1: =IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT(($A$1:A1=A1)* ($B$1:B1=B1))1,"",CODE(L*EFT(A1))+ROW()/10^10)) In D1: =IF(ROW()COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C :$C,ROW()),$C:$C,0))) Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of data in cols A and B, say down to E100? Hide away col C. Cols D and E will return the required results all neatly bunched at the top, viz. for the posted sample source data, you'd get: cup water light team table cup water wine -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- Hello Max, Thank you very much for your solution. It works very well. I am going to study in detail how it works |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
On 17 jun, 12:35, Max wrote:
Another tinker to try, using non-array formulas .. Source data assumed in cols A and B, from row1 down In C1: =IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT(($A$1:A1=A1)* ($B$1:B1=B1))1,"",CODE(L*EFT(A1))+ROW()/10^10)) In D1: =IF(ROW()COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C :$C,ROW()),$C:$C,0))) Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of data in cols A and B, say down to E100? Hide away col C. Cols D and E will return the required results all neatly bunched at the top, viz. for the posted sample source data, you'd get: cup water light team table cup water wine -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- Max, I've just realized it does not do a real sort, it works only on first letter. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
On 17 jun, 03:58, "T. Valko" wrote:
This handles TEXT only. A1:An = rng1 B1:Bn = rng2 Assuming you want to extract the uniques and sort ascending. Enter this formula in D1: =LOOKUP(2,1/(COUNTIF(rng1,"<"&rng1)=0)/ISTEXT(rng1),rng1) Enter this array formula** in D2: =IF(ROWS($1:2)<=SUM((rng1<"")/COUNTIF(rng1,rng1&"")),INDEX(rng1,MATCH(0,CO*UNTIF (rng1,"<"&rng1)-SUM(COUNTIF(rng1,D$1:D1)),0)),"") Copy down until you get blanks. Enter this formula in E1: =IF(D1="","",INDEX(rng2,MATCH(D1,rng1,0))) Copy down until you get blanks. Based on your sample data the result will be: ...........D.............E 1.......cup........water 2.......light........team 3.......table.......cup 4.......water......wine 5............................ Biff "vsoler" wrote in message oups.com... On 16 jun, 22:41, "T. Valko" wrote: When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message groups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, Yes, I want to retain the first instance and delete all the rest. The example you have given is not possible.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, I just discovered one important problem with your formula: it finds unique texts in column A and then joins the corresponding element in column B. However, I am trying to find unique pairs, which is something a bit different. Say, for example, that my list contained table .... cup table.....chair In this case, I'd like the 2 pairs to appear in the final list, because each one is unique. I am also trying to avoid, as much as possible, concatenating A&B in order to distingish between, say some.......how so..........mehow which are 2 different combinations of elements. I appreciate your help and effort |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
"vsoler" wrote in message oups.com... On 17 jun, 03:58, "T. Valko" wrote: This handles TEXT only. A1:An = rng1 B1:Bn = rng2 Assuming you want to extract the uniques and sort ascending. Enter this formula in D1: =LOOKUP(2,1/(COUNTIF(rng1,"<"&rng1)=0)/ISTEXT(rng1),rng1) Enter this array formula** in D2: =IF(ROWS($1:2)<=SUM((rng1<"")/COUNTIF(rng1,rng1&"")),INDEX(rng1,MATCH(0,CO*UNTIF (rng1,"<"&rng1)-SUM(COUNTIF(rng1,D$1:D1)),0)),"") Copy down until you get blanks. Enter this formula in E1: =IF(D1="","",INDEX(rng2,MATCH(D1,rng1,0))) Copy down until you get blanks. Based on your sample data the result will be: ...........D.............E 1.......cup........water 2.......light........team 3.......table.......cup 4.......water......wine 5............................ Biff "vsoler" wrote in message oups.com... On 16 jun, 22:41, "T. Valko" wrote: When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message groups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, Yes, I want to retain the first instance and delete all the rest. The example you have given is not possible.- Ocultar texto de la cita - - Mostrar texto de la cita - ****************************** T. Valko, I just discovered one important problem with your formula: it finds unique texts in column A and then joins the corresponding element in column B. However, I am trying to find unique pairs, which is something a bit different. Say, for example, that my list contained table .... cup table.....chair In this case, I'd like the 2 pairs to appear in the final list, because each one is unique. I am also trying to avoid, as much as possible, concatenating A&B in order to distingish between, say some.......how so..........mehow which are 2 different combinations of elements. I appreciate your help and effort *************************************** Say, for example, that my list contained table .... cup table.....chair Well, I asked you if that was a possibility: Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine And you said: The example you have given is not possible. So now *it is possible* ? About the only way to do this is to concatenate, find and sort the uniques, then unconcatenate. Let me see what I can come up with. No guarantees! Don't change the "rules" anymore!!! <BG Or better yet, let us know *ALL* the rules before we attempt a solution. Biff |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
Yes, that's the limitation of the criteria formula suggested in col C. The
set-up will return only & all the unique pairs that you seek from cols A and B, arranged in an ascending sort order based on the 1st character in col A. I'm not sure whether it's possible to emulate via formulas a "real" sort order as per Excel's native Data Sort functionality. Perhaps it's easier to consider bolting-on a recorded macro to copy n paste as values the results returned in cols D and E into cols F and G, and then sort cols F and G using Data Sort? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "vsoler" wrote Max, I've just realized it does not do a real sort, it works only on first letter. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
Yes, it doesn't do a "real" sort, only an approx sort based on the 1st
character in col A. And I guess this wasn't good enough for the OP <g. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote in message ... That might be good enough but it doesn't do a real sort. Biff |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
"T. Valko" wrote in message ... "vsoler" wrote in message oups.com... On 17 jun, 03:58, "T. Valko" wrote: This handles TEXT only. A1:An = rng1 B1:Bn = rng2 Assuming you want to extract the uniques and sort ascending. Enter this formula in D1: =LOOKUP(2,1/(COUNTIF(rng1,"<"&rng1)=0)/ISTEXT(rng1),rng1) Enter this array formula** in D2: =IF(ROWS($1:2)<=SUM((rng1<"")/COUNTIF(rng1,rng1&"")),INDEX(rng1,MATCH(0,CO*UNTIF (rng1,"<"&rng1)-SUM(COUNTIF(rng1,D$1:D1)),0)),"") Copy down until you get blanks. Enter this formula in E1: =IF(D1="","",INDEX(rng2,MATCH(D1,rng1,0))) Copy down until you get blanks. Based on your sample data the result will be: ...........D.............E 1.......cup........water 2.......light........team 3.......table.......cup 4.......water......wine 5............................ Biff "vsoler" wrote in message oups.com... On 16 jun, 22:41, "T. Valko" wrote: When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message groups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, Yes, I want to retain the first instance and delete all the rest. The example you have given is not possible.- Ocultar texto de la cita - - Mostrar texto de la cita - ****************************** T. Valko, I just discovered one important problem with your formula: it finds unique texts in column A and then joins the corresponding element in column B. However, I am trying to find unique pairs, which is something a bit different. Say, for example, that my list contained table .... cup table.....chair In this case, I'd like the 2 pairs to appear in the final list, because each one is unique. I am also trying to avoid, as much as possible, concatenating A&B in order to distingish between, say some.......how so..........mehow which are 2 different combinations of elements. I appreciate your help and effort *************************************** Say, for example, that my list contained table .... cup table.....chair Well, I asked you if that was a possibility: Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine And you said: The example you have given is not possible. So now *it is possible* ? About the only way to do this is to concatenate, find and sort the uniques, then unconcatenate. Let me see what I can come up with. No guarantees! Don't change the "rules" anymore!!! <BG Or better yet, let us know *ALL* the rules before we attempt a solution. Biff Ok, this is about the best I can come up with. It *requires* that you concatenate column A and column B. It will *not* handle empty cells. A1:Bn = rng1 C1:Cn = rng2 C1: =A1&" "&B1 Copied down as needed E1 copied across to F1: =INDEX(rng1,MATCH(LOOKUP(2,1/(COUNTIF(rng2,"<"&rng2)=0)/ISTEXT(rng2),rng2),rng2,0),COLUMNS($A:A)) Array formula entered in E2 and copied across to F2: =IF(ROWS($1:2)<=SUM((rng2<"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,MATCH(0,COUNTIF( rng2,"<"&rng2)-SUM(COUNTIF(rng2,$E$1:$E1&" "&$F$1:$F1)),0),COLUMNS($A:A)),"") Select both E2 and F2 and copy down until you get blanks. You can hide column C if you'd like. Biff |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Working with pairs of cells
On 16 jun, 22:41, "T. Valko" wrote:
When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff "vsoler" wrote in message oups.com... My sheet contains in A1:B500 lots of pairs, some of which are repeated ...........A.............B 1........light........team 2........table.........cup 3........cup.........water 4.........light........team 5.........water......wine I am interested in removing the duplicated rows (in the case above light -- team), pushing the rest of the pairs up. If possible, although it can complicate things, I want to sort the pairs by column A. Is it possible to do all this only with formulas? (I don't want to use advanced filter) It would help a lot. Thank you in advance.- Ocultar texto de la cita - - Mostrar texto de la cita - T. Valko, I apologize I there has been a misunderstanding although I still believe I have not changed the specs. When you say to remove duplicate rows does that mean you want to retain the first instance and exclude all other instances? For example, based on your data you want to keep row 1 and exclude row 4. Is this possible: ...........A.............B 1........light........team 2........light........ice 3........light........water 4........light........team 5........water.....wine Biff My answer to this example was that it was not possible, because row 1 is exactly the same as row 4; I want to retain row 1 and delete row 4; however, rows 2, 3 and 5 are different pairs, and I would like to keep them all. After the deletion of row 4, row 5 will move up to become the new row 4. I hope this time there no doubt about the definition of the problem. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create pairs of addresses? | Excel Discussion (Misc queries) | |||
how do I count pairs of cells when each matches a condition? | Excel Discussion (Misc queries) | |||
Labeling Data Pairs | Charts and Charting in Excel | |||
Numbering data pairs | Charts and Charting in Excel | |||
Merge file pairs | Excel Discussion (Misc queries) |