PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU

H

HERNAN

I have 2 columns with numbers Column A and B.
I need to COPY on column "C" the ones that are duplicates.
That's it.
(I don't need to find them using conditional formating, I need to copy them)
Please Im gettin crazy with this. THANK YOU IN ADVANCE!!!!

A B
10 30
15 50
20 15
 
B

bj

try something like
for your exAMPLE in C1 enter
if(countif($B$1:$B$3,A1)>0,A1)
and copy down to C3
 
B

Biff

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A$1:A$4,SMALL(IF(ISNUMBER(MATCH(A$1:A$4,B$1:B$4,0)),ROW(A$1:A$4)-ROW(A$1)+1),ROW(A1)))

Copy down until you get #NUM! errors meaning all matches have been returned.

Biff
 
H

HERNAN

Thank you!, that was what I ask for..., but I realize I made a mistake in the
question, because I want to delete in column "A" the ones that are NOT
duplicate.
In other words I need to keep the information of the duplicates. Do you know
how can I do that?
Here is the thing I have a lot of data on each row, I work on a spread with
2000 rows, now they gave me the numbers of the ones that they want me to keep
(850) So I put the new column in "B"
If you can help me I would be really apreciate!!!!!
Thank you anyway.
 
H

HERNAN

Thank u!! that was what I ask for, but I formulate the question wrongly, I
need to delete the ones that are not duplicate, on Column A how can I do that?
What would be great if I can locate the ones duplicated in column A, sort
them and then keep them.
They gave me a new list on my job, I work with 2000 rows, they want me to
keep just 800, I have a lot of data attached to each number
Thank you
 
H

HERNAN

no is not working, but thanks!!

Biff said:
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A$1:A$4,SMALL(IF(ISNUMBER(MATCH(A$1:A$4,B$1:B$4,0)),ROW(A$1:A$4)-ROW(A$1)+1),ROW(A1)))

Copy down until you get #NUM! errors meaning all matches have been returned.

Biff
Click to expand...
 
B

Biff

What does "no is not working" mean? Getting an error? Incorrect results? Did
you enter the formula as an array? It does work but I'll bet you either
didn't enter it as an array or the references aren't correct. But none of
that matters as I've read your follow-up to BJ and what you want to do based
on the additional info will require the use of a macro and VBA code. I can't
help with that, sorry.

Biff
 
M

Max

Venturing a go here ..

Assuming source data is in sheet: X,
cols A to C, data from row1 down

10 Text1 Data1
15 Text2 Data2
20 Text3 Data3
15 Text4 Data4
11 Text5 Data5
20 Text6 Data6
etc

and what you want to extract are lines which are duplicates, going by the
number in the key col A, with the duplicates extract sorted in say, ascending
order by the key col A viz:

15 Text2 Data2
15 Text4 Data4
20 Text3 Data3
20 Text6 Data6

Try this to get the above extracts in another sheet

In a new sheet,

Put in A1:
=IF(X!A1="","",IF(COUNTIF(X!A:A,X!A1)=1,"",X!A1+ROW()/10^10))

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL($A:$A,ROW()),$A:$A,0)))

Copy B1 across to D1 (or more, if your actual source data spans more than 3
cols). Then just select A1:D1 and copy down to cover the max expected extent
of data in X, eg down to D2000. Hide away col A. Cols B to D will return the
required extracts from X, with all duplicate lines neatly bunched at the top,
sorted in ascending order by the value in X's col A.

For easy try-out of the above, simply make a copy of your actual source
sheet, name it as: X, ensure that the source data in X starts in row1, then
plug in the formulas as-is and breeze away. You can always rename the sheet
later (if required) and leave it to Excel to auto-change the name in the
formulas.
 
Top