question

  • Thread starter Thread starter Ivanko
  • Start date Start date
I

Ivanko

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ć
 
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
 
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ć
 
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 before:
*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
 
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!
 
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
 
=COUNTIF(A:A;A2)
in C2 and pres enter
Click to expand...

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
---
 
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!
 
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
 
Back
Top