Multiple if condition help please

[map6122]

Hello fellas,

I have over 60000 rows of data in excel 2010.
Each row have unique client name

like I have 4 client names I want to keep the same as their real name in Client placeholder to automate column. I have another 40 clients like John and David that I want to assign them Other in Client placeholder to automate column. Is there a way to do it??
Thanks in advance.

Client name	Client Placeholder to automate
ABC	ABC
ABC1	ABC1
ABC2	ABC2
John	Other
David	Other

<tbody>
</tbody>

 

[map6122]

you are right it's not actual names.
Sorry can't post confidential work stuff here. Entire excel data i posted was generic data.

 

[map6122]

yes there are more keeps names.

 

[map6122]

Yes let me explain more in detail.

ABC is main company

David and John is ABC subsidiary so i want excel to output ABC for John and David.

Main company abc have at least 20 small subsidiary. I will add rest 18 after you show me how to add two companies.

 

[FDibbins]

Without realist samples....

	L​	M​
1​	123 abc 456	123 abc 456
2​	234 abc 567	234 abc 567
3​	345 abc 678	345 abc 678
4​	abc	abc
5​	abd	other
6​	acd	other
7​	cad	other

M1=IF(ISNUMBER(SEARCH("abc",L1)),L1,"other")
copied down

 

[FDibbins]

you are right it's not actual names.
Sorry can't post confidential work stuff here. Entire excel data i posted was generic data.

That was why I asked for realist sample data, I fully understand you cannot post real data, but the closer your samples are, the better we can structure answers

 

[Tetra201]

Yes let me explain more in detail.

ABC is main company

David and John is ABC subsidiary so i want excel to output ABC for John and David.

Main company abc have at least 20 small subsidiary. I will add rest 18 after you show me how to add two companies.

Here is an updated formula. Now it has "nested IFs". If you add 18 more names, it can become quite bulky.

=IF(OR(A1="ABC",A1="ABC1",A1="ABC2",A1="ABC3"),A1,IF(OR(A1="John",A1="David"),"ABC","Other"))

 

[map6122]

It gave me error for john and david. and did not produce Other. I did small test batch.

 

[map6122]

Thank you for your help.

 

[Tetra201]

It gave me error for john and david...

What kind of error? -- The formula works OK on my 30-row test dataset.

... and did not produce Other.

What did it produce?

 

[SQUIDD]

map6122

Do you only want to do this just once.
If so, why not autofilter your data and deselect the 4 company names you want to keep the same, then type "other" into the top row and copy down.
then change the filter to company 1, write the company name in the top row, copy down and repeat for the other companies.

Dave