Posts by PJ KARAFFA

OR WITH A LITTLE COEHERSING YOU CAN HAVE MORE THAN 7 NESTED IF'S TAKE A LOOK!!!

http://j-walk.com/ss/excel/usertips/tip080.htm

Or another approach is to use the following code by Dave McRitchie to get the same results!

Sub RemoveAllSpaces()
'David McRitchie 2000-10-24
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Selection.SpecialCells(xlConstants).Replace What:=Chr(32), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

:yes::lol:

Epidemic is right!!

If your going to have a whole bunch of initials or different codes for each word to lookup then why not use V-Lookup!! :bsmile:

Use

=IF(ISNUMBER(SEARCH(B1,$A$2)),1,""):lol:

=IF(OR(A1={"AAA","BBB","CCC","DDD"}),1,"")

THIS WILL PLACE IT NEXT TO THE NUMBER

TO PLACE IT UNDER THE HEADINGS SEE ATTACHMENT :lol:

Chris,

I saw that this morning. I could not believe that that was it. It makes total sence to me though! Aladin is a genious! He does not miss a trick! Thanks for filling me in anyway!!

Chris although I am not quite sure why your formula would not work I did some tests on it using Harland Groves function and it seems to show that it is a range. I took a different approach I used =MCONCAT(THREED(Sheet1:Sheet6!A1)," ,") and this will give you the desired results but I know that this still does not answer our question! I will continue to look into it because I am just as curious now to find out why!!

RadMan

Look at this shortcut this will answer your problem! http://www.ozgrid.com/forum/viewthread.php?tid=66

RadMan,

It took some time but I think this is the answer that you are looking for Compliments of Aladin Akyurek!!

Bearcub,

John's formula works off of a target and from the target it gives you the cell address, which means that if your looking for Mike then you must put Mike in a cell (The Target) and the references will refer to just (The Target) Mike. If I read right you are looking to find all of the duplicate values and get the cell addresses for all of them or just the duplicates! By all of them I mean that if you have Mike in cell A1 and Mike in cell A14, You will see cell the cell address A1 and A14. By just the duplicate you will see cell A14. Which approach are you trying to take!:lol:

Bearcub,

Sorry for not getting back so soon I had a slight case of the flu. After reveiwing what you said and looking at your workbook Probably one of the best ways to analize your data is to use Auto Filter. Chris has already provided you with a way to count duplicates. So the next step is to filter all of the duplicates. This way you can make the neseccary changes for your workbook. I took the process of finding the cell addresses with a formula that I constructed but it defeates the purpose when you already have a formula that Chris has given. Auto Filter!!!:lol:

mscallisto
Even though the OP was looking for something alittle bit different a slightly better approach to your formula would be
This way you do not have as many numbers.

=IF(COUNTIF($A$1:A1,A1)=2,COUNTIF($A$1:$A$100,A1),""):bouncy::D

CAN YOU ATTACH A FILE SO THAT WE CAN SEE EXACTLY WHAY YOUR CTITERIA IS!!:lol::spin:

This method is not the tell all! It will get the job done but uses the formula twice to get the result. This will stop the number error. But as a rule you should always try and get around using functions more than once if possible
{=IF(ISERROR(INDEX($B$1:$B$2150,SMALL(IF(ISNUMBER($B$1:$B$2150),ROW($B$1:$B$2150)),ROW(1:1)))),"",INDEX($B$1:$B$2150,SMALL(IF(ISNUMBER($B$1:$B$2150),ROW($B$1:$B$2150)),ROW(1:1))))}

Array entered (Ctrl+Shift+Enter):lol:

If you would like to get the numbers one aftert the other then use the formula below

{=INDEX($B$1:$B$2150,SMALL(IF(ISNUMBER($B$1:$B$2150),ROW($B$1:$B$2150)),ROW(1:1)))}

:lol::cool:

If your duplicates are in column A and start in A1 then put this in column B

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

:tumble:

You can use this formula and you can adjust it to your needs.

=OFFSET(A$1,(ROW()-1)*4,0) :lol:

Here is one way to answer your question. Although it may not be exactly what you want it is very close. All the credit for this one goes to Aladin Akyurek.:D

Is it possible to post a little of your data so that we can getter a better understanding of what you are actually trying to do!!

Congratulations Dave very cool!! It's very nice to have another forum. Thanks WillR for making me aware of this site from a post you made on Mr Excel.