Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
try something like
for your exAMPLE in C1 enter if(countif($B$1:$B$3,A1)0,A1) and copy down to C3 "HERNAN" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
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 "HERNAN" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
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. "bj" wrote: try something like for your exAMPLE in C1 enter if(countif($B$1:$B$3,A1)0,A1) and copy down to C3 "HERNAN" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
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 "bj" wrote: try something like for your exAMPLE in C1 enter if(countif($B$1:$B$3,A1)0,A1) and copy down to C3 "HERNAN" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
no is not working, but thanks!!
"Biff" wrote: 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 "HERNAN" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
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 "HERNAN" wrote in message ... no is not working, but thanks!! "Biff" wrote: 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 "HERNAN" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP WITH VLOOKUP ASAP!!! THANK YOU
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+RO W()/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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HERNAN" wrote: 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 "bj" wrote: try something like for your exAMPLE in C1 enter if(countif($B$1:$B$3,A1)0,A1) and copy down to C3 "HERNAN" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Vlookup Help needed ASAP | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |