| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
question
I have normal excel document
consisted of 2 columns A and B. In each columns there are 4000 rows. In each row is url adress in form: http://www.studiotatjana.com I want new list of urls in column C which is sum of values in column A and B. So there will be 8000 urls in column C. But there needs to be only one rule. If value in one row of 4000 row in column A is identical to any value in any row in column B then that value do not go to colum C. I want that if one url adress can be fund in column A and B that that url is deleted and do not go to column C. All values in colum A are diferent. And all values in column B are diferent. But there may be 1000 identical adreses and i dont want them to come in columnt C Please can you help with some funkction or formula and I am new user so can you give me step by step explenation how to like: file - open etc. Best regards and thank you Ivanko Perišić |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
question
I read it that what you want is to extract a list of unique items from the
items listed in cols A and B. One simple way is to use Advanced Filter Uniques on a combined col Copy data in col B, paste below data in col A Insert a new top row, enter a col label into A1 (if required) Select col A, click Data Filter Advanced Filter In the dialog: Check "Copy to another location" & "Unique records only" Copy to: C1 Click OK, and the uniques list will appear in col C -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Ivanko" wrote: I have normal excel document consisted of 2 columns A and B. In each columns there are 4000 rows. In each row is url adress in form: http://www.studiotatjana.com I want new list of urls in column C which is sum of values in column A and B. So there will be 8000 urls in column C. But there needs to be only one rule. If value in one row of 4000 row in column A is identical to any value in any row in column B then that value do not go to colum C. I want that if one url adress can be fund in column A and B that that url is deleted and do not go to column C. All values in colum A are diferent. And all values in column B are diferent. But there may be 1000 identical adreses and i dont want them to come in columnt C Please can you help with some funkction or formula and I am new user so can you give me step by step explenation how to like: file - open etc. Best regards and thank you Ivanko Perišić |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
question
Thank you very much Max for you reply.
I think that your method will insert ... i will make a sketch: COLUMN A COLUM B COLUM C have. http://www.abc.com http://www.abc.com http://www.cba.com http://www.cba.com http://www.ccc.com http://www.bca.com http://www.bca.com http://www.bbb.com http://www.ccc.com http://www.bbb.com So only 1 item is not in co C: http://www.abc.com becouse this item is located in co A and co B. Is this your method doing? If not do you know method that can do this? Thank you very much againg Best regards Ivanko Perišić "Max" wrote: I read it that what you want is to extract a list of unique items from the items listed in cols A and B. One simple way is to use Advanced Filter Uniques on a combined col Copy data in col B, paste below data in col A Insert a new top row, enter a col label into A1 (if required) Select col A, click Data Filter Advanced Filter In the dialog: Check "Copy to another location" & "Unique records only" Copy to: C1 Click OK, and the uniques list will appear in col C -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Ivanko" wrote: I have normal excel document consisted of 2 columns A and B. In each columns there are 4000 rows. In each row is url adress in form: http://www.studiotatjana.com I want new list of urls in column C which is sum of values in column A and B. So there will be 8000 urls in column C. But there needs to be only one rule. If value in one row of 4000 row in column A is identical to any value in any row in column B then that value do not go to colum C. I want that if one url adress can be fund in column A and B that that url is deleted and do not go to column C. All values in colum A are diferent. And all values in column B are diferent. But there may be 1000 identical adreses and i dont want them to come in columnt C Please can you help with some funkction or formula and I am new user so can you give me step by step explenation how to like: file - open etc. Best regards and thank you Ivanko Perišić |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
question
Ok, I'll take it that what you really want is what you've now shown in your
col C If so, try it this way First, combine* it in col A like befo *copy col B's data, paste below col A's data The combined data will look like this: MyURL (just a col header) http://www.abc.com http://www.cba.com http://www.bca.com http://www.bbb.com http://www.abc.com http://www.ccc.com http://www.bbb.com Then place in C2: =COUNTIF(A:A,A2) Copy C2 down. Then select col C, click Data Filter Autofilter Choose: 1 from the droplist. The filtered cells (in col A) would be what you want. You can simply select & copy n paste these filtered cells elsewhere. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Ivanko" wrote: Thank you very much Max for you reply. I think that your method will insert ... i will make a sketch: COLUMN A COLUM B COLUM C have. http://www.abc.com http://www.abc.com http://www.cba.com http://www.cba.com http://www.ccc.com http://www.bca.com http://www.bca.com http://www.bbb.com http://www.ccc.com http://www.bbb.com So only 1 item is not in co C: http://www.abc.com becouse this item is located in co A and co B. Is this your method doing? If not do you know method that can do this? Thank you very much again Best regards Ivanko Perišić |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
question
Ok.
COLUMN A looks like this: MyURL (just a col header) http://www.abc.com http://www.cba.com http://www.bca.com http://www.bbb.com http://www.abc.com http://www.ccc.com http://www.bbb.com Then i write formula in column C row 2 =COUNTIF(A:A,A2) and then when i click enter mesage comes out: The formula you typed contains an error And the other thing that i dont uderstand: "copy C2 down" - is this mean copy formula in c2 to c3, c4 etc? "The filtered cells (in col A) would be what you want. You can simply select & copy n paste these filtered cells elsewhere." - is this mean that - this i realy dont understand? Please help and thank you very much! "Max" wrote: Ok, I'll take it that what you really want is what you've now shown in your col C If so, try it this way First, combine* it in col A like befo *copy col B's data, paste below col A's data The combined data will look like this: MyURL (just a col header) http://www.abc.com http://www.cba.com http://www.bca.com http://www.bbb.com http://www.abc.com http://www.ccc.com http://www.bbb.com Then place in C2: =COUNTIF(A:A,A2) Copy C2 down. Then select col C, click Data Filter Autofilter Choose: 1 from the droplist. The filtered cells (in col A) would be what you want. You can simply select & copy n paste these filtered cells elsewhere. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Ivanko" wrote: Thank you very much Max for you reply. I think that your method will insert ... i will make a sketch: COLUMN A COLUM B COLUM C have. http://www.abc.com http://www.abc.com http://www.cba.com http://www.cba.com http://www.ccc.com http://www.bca.com http://www.bca.com http://www.bbb.com http://www.ccc.com http://www.bbb.com So only 1 item is not in co C: http://www.abc.com becouse this item is located in co A and co B. Is this your method doing? If not do you know method that can do this? Thank you very much again Best regards Ivanko Perišić |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
question
One more thing. When I write formula
=COUNTIF(A:A;A2) in C2 and pres enter number 2 pops in c2 field then when i click data - filter - autofilter and then when i chose first option form dropdown menu clikck ok then in column A only thing is www.abc.com this is not what i want i want that in column A stay all other in the end all but www.abc.com so that in the end column A looks like this: MyURL (just a col header) http://www.cba.com http://www.bca.com http://www.bbb.com http://www.ccc.com http://www.bbb.com Regards and thanks "Max" wrote: Ok, I'll take it that what you really want is what you've now shown in your col C If so, try it this way First, combine* it in col A like befo *copy col B's data, paste below col A's data The combined data will look like this: MyURL (just a col header) http://www.abc.com http://www.cba.com http://www.bca.com http://www.bbb.com http://www.abc.com http://www.ccc.com http://www.bbb.com Then place in C2: =COUNTIF(A:A,A2) Copy C2 down. Then select col C, click Data Filter Autofilter Choose: 1 from the droplist. The filtered cells (in col A) would be what you want. You can simply select & copy n paste these filtered cells elsewhere. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Ivanko" wrote: Thank you very much Max for you reply. I think that your method will insert ... i will make a sketch: COLUMN A COLUM B COLUM C have. http://www.abc.com http://www.abc.com http://www.cba.com http://www.cba.com http://www.ccc.com http://www.bca.com http://www.bca.com http://www.bbb.com http://www.ccc.com http://www.bbb.com So only 1 item is not in co C: http://www.abc.com becouse this item is located in co A and co B. Is this your method doing? If not do you know method that can do this? Thank you very much again Best regards Ivanko Perišić |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
question
=COUNTIF(A:A;A2)
in C2 and pres enter Ah, good. You finally got the above going over there (Your Excel setting apparently uses semicolons as separators, mine uses commas.) But wait a minute, ... you forgot my step to copy C2 down after you have entered the formula in C2 Do this 1st before you do the data - filter - autofilter steps ... and you should get the results that you seek (Col C is a helper col for the autofiltering) One request, do press the "Yes" buttons (like the one below) in all responses which helped you -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| question | Excel Discussion (Misc queries) | |||
| where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
| The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
| iif question | Excel Discussion (Misc queries) | |||
Hybrid Mode
