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 --- |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
question
Again i have problem. Now we got formula located in C2.
And I copy formula to c3 c4 etc. to C8 where data in COA finishes end then when i click data - filter etc. and select first option from drop down menu which comes out in begining of CO C nothing hapends. CO A is identical as it was on the beging.... CoA 1.MyURL (just a col header) 2.http://www.abc.com 3.http://www.cba.com 4.http://www.bca.com 5.http://www.bbb.com 6.http://www.abc.com 7.http://www.ccc.com 8.http://www.bbb.com .... one more thing. This you say copy down formula from C2. Do you meen copy to c3 c4 etc? If this is the case it would be a problem becouse in real document i have 8000 items in CO A so copy paste will be enormus job. Do you have some trick to bypas that? Thanks! "Max" wrote: =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 --- |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
question
a. When you copy down the formula in col C,
you should see this result appearing: MyURL http://www.abc.com 2 http://www.cba.com 1 http://www.bca.com 1 http://www.bbb.com 2 http://www.abc.com 2 http://www.ccc.com 1 http://www.bbb.com 2 Note: If you don't see it happening as above, then the calc mode may be inadvertently set to Manual. Press F9 to re-calc. Click Tools Options Calculation tab to reset the calc mode to Automatic. So when you select: 1, from the autofilter dropdown for col C, you should get the desired results, viz.: http://www.cba.com 1 http://www.bca.com 1 http://www.ccc.com 1 b. To copy down large ranges where the adjacent col may not be fully populated, you could do it like this: Click inside the namebox*, type the range: C2:C8000 Press ENTER, to select the range *the box with the dropdown just to the left of the formula bar Then paste your earlier formula for C2 into the formula bar, press CTRL+ENTER, to enter the formula at one go into the entire range Note: If the adjacent col is fully populated, you could simply enter the formula in the top cell C2, then point to the bottom right corner of C2 (that's the fill handle, looks like a solid black square), and double click on it. The formula will fill all the way down the entire range in col C to the last data row in the adjacent col. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,100 Files:360 Subscribers:56 xdemechanik --- "Ivanko" wrote: Again i have problem. Now we got formula located in C2. And I copy formula to c3 c4 etc. to C8 where data in COA finishes end then when i click data - filter etc. and select first option from drop down menu which comes out in begining of CO C nothing hapends. CO A is identical as it was on the beging.... CoA 1.MyURL (just a col header) 2.http://www.abc.com 3.http://www.cba.com 4.http://www.bca.com 5.http://www.bbb.com 6.http://www.abc.com 7.http://www.ccc.com 8.http://www.bbb.com ... one more thing. This you say copy down formula from C2. Do you meen copy to c3 c4 etc? If this is the case it would be a problem becouse in real document i have 8000 items in CO A so copy paste will be enormus job. Do you have some trick to bypas that? Thanks! |
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) |