Find

[Zetori]

Hiya!

Can't figure this out! so please help

I have huge mass of text

Like:
A b c d e
1 Text1 Text2 Text3 Text4 Text5
2 Text6 Text7 Text8 Text9 Text10
3 Text1 Text11 Text12 Text13 Text14

I should find dublicates like in A1 and A3 and allways from the second match (A3) get information from the same row, like C3. In the textfile

Thank You!

 

[Akashwani]

Hi, welcome to MrExcel.

Is this what you require?....

Excel Workbook
	A	B	C	D	E
1	Name	Colour	Animal	City	Result
2	Bart Simpson	Red	cat	Bike	*
3	Lisa Simpson	Green	dog	Bus	*
4	Marge Simpson	Black	fish	Train	*
5	Maggie Simpson	White	cat	Plane	*
6	Homer Simpson	Red	Panda	Train	*
7	Ned Flanders	Black	Tiger	Coach	*
8	Homer Simpson	White	Tiger	Bus	Tiger
9	Tod Flanders	Red	cow	trike	*
10	Bart Simpson	Red	cat	Bike	cat
Sheet1

The formula in E2 needs to be copied down as far as you require.
What happens if there are more than 2 items the same in column A, do you want to return the data in Column C for all results over 1?
If so, the formula needs to be.....
=IF(COUNTIF($A$2:A2,A2)>1,C2,"")


Ak

 

[Zetori]

Thank you very much Ak! That was exactly what I was looking for!

 

[Akashwani]

No problem, I'm pleased it worked for you.

Thanks for the feed back.

Ak

 

[Zetori]

Hiya

I have code as shown below.

[FONT=Verdana][COLOR=black]{=INDEX(named range;SMALL(IF(($B$2=Sheet1!B:B);ROW(Sheet1!B:B)-MIN(ROW(Sheet1!B:B))+1;"");ROW(Sheet1!A2));2)}[/COLOR][/FONT]

I’m trying to find multiple same occurrences and list them all as unique values.

What I would like to add... If there's a way to use multiple values to find those targets. In that code it's trying to find B2 cell. How could I use range like B2:B3 there? Something like this: (didn't work )

[FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black]=INDEX(named range;SMALL(IF(([B][COLOR=red]OR($B$2:$B$3)=Sheet1!B:B[/COLOR][/B]);ROW(Sheet1!B:B)-MIN(ROW(Sheet1!B:B))+1;"");ROW(Sheet1!A1));2)[/COLOR][/FONT][/COLOR][/FONT]

Couldn't figure this myself, so please help.

Thank You!

 

[Akashwani]

Hi zetori,

I'm sorry but I'm unable to help you with that problem.

The best thing that you can do, is start a new thread, provide some sample data and give an example of your expected result.

Good luck.

Ak

 

[jasonb75]

Try

{=INDEX(named range;SMALL(IF(($B$2=Sheet1!B:B)*($B$3=Sheet1!B:B);ROW(Sheet1!B:B)-MIN(ROW(Sheet1!B:B))+1;"");ROW(Sheet1!A2));2)}