Excel match text in Description

[Slongy]

Hi. I have about 70K rows of data. This is a pull from a CRM system and one of the columns contains Free text which may or may not contain a reference ID.

So basically, I need to be able to identify which (if any) of the 60 or so Reference ID appears somewhere in the text. It may be at the beginning, somewhere in the middle or at the end.

So i created two formulas:

=ISNUMBER(SEARCH("MEBIG3",$P:$P))

I created 60 or so of these columns, all with a different Reference ID (MEBIG3 being the ID in this instance). I put the Reference ID in the Cell 1 of each of the columns. I then got a True in the cells wherever I matched the Ref ID to in the description field.
I then created another column with this:

=IF(AO4=TRUE,$AO$1,IF(AP4=TRUE,$AP$1,IF(AQ4=TRUE,$AQ$1,IF(AR4=TRUE,$AR$1,IF(AS4=TRUE,$AS$1,IF(AT4=TRUE,$AT$1,IF(AU4=TRUE,$AU$1,IF($AV$2=TRUE,AV3,IF($AW$2=TRUE,$AW$1,IF(AX4=TRUE,$AX$1,IF(AY4=TRUE,$AY$1,IF(AZ4=TRUE,$AZ$1,IF(BA4=TRUE,$BA$1,IF(BB4=TRUE,$BB$1,IF(BC4=TRUE,$BC$1,IF(BD4=TRUE,$BD$1,IF(BE4=TRUE,$BE$1,IF(BF4=TRUE,$BF$1,IF(BG4=TRUE,$BG$1,IF(BH4=TRUE,$BH$1,IF(BI4=TRUE,$BI$1,IF(BJ4=TRUE,$BJ$1,IF(BK4=TRUE,$BK$1,IF(BL4=TRUE,$BL$1,IF(BM4=TRUE,$BM$1,IF(BN4=TRUE,$BN$1,IF(BO4=TRUE,$BO$1,IF(BP4=TRUE,$BP$1,IF(BQ4=TRUE,$BQ$1,IF(BR4=TRUE,$BR$1,IF(BS4=TRUE,$BS$1,IF(BT4=TRUE,$BT$1,IF(BU4=TRUE,$BU$1,IF(BV4=TRUE,$BV$1,IF(BW4=TRUE,$BW$1,IF(BX4=TRUE,$BX$1,IF(BY4=TRUE,$BY$1,IF(BZ4=TRUE,$BZ$1,IF(CA4=TRUE,$CA$1,IF(CB4=TRUE,$CB$1,IF(CC4=TRUE,$CC$1,IF(CD4=TRUE,$CD$1,IF(CE4=TRUE,$CE$1,IF(CF4=TRUE,$CF$1,IF(CG4=TRUE,$CG$1,IF(CH4=TRUE,$CH$1,IF(CI4=TRUE,$CI$1,IF(CJ4=TRUE,$CJ$1,IF(CK4=TRUE,$CK$1,IF(CL4=TRUE,$CL$1,IF(CM4=TRUE,$CM$1,IF(CN4=TRUE,$CN$1,IF(CO4=TRUE,$CO$1,IF(CP4=TRUE,$CP$1,IF(CQ4=TRUE,$CQ$1,IF(CR4=TRUE,$CR$1,IF(CS4=TRUE,$CS$1,IF(CT4=TRUE,$CT$1,IF(CU4=TRUE,$CU$1,IF(CV4=TRUE,$CV$1,IF(CW4=TRUE,$CW$1,IF(CX4=TRUE,$CX$1,IF(CY4=TRUE,$CY$1,IF(CZ4=TRUE,$CZ$1,IF(DA4=TRUE,$DA$1)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

So wherever there was a True, it returned the Cell at the top of the column which has the Ref ID in it. I put a pivot table and it worked, but the problem is that Excel just can't really handle the number of calculations. The file keeps crashing and becoming corrupt (There are 70K rows, times 60 or so columns). Also I have actually run out of columns I can add in the nesting, I really need more.

Can anyone suggest a way of doing the whole piece of analysis all in one Cell. So basically it says "If ADM1 appears anywhere in Column P return ADM1, If ADM2 appears anywhere in Column P return ADM2 and so on"

It's probably really easy to do, but I can't work it out

Thanks in advance

Andy

 

[healey21]

Andy show a small amount of your data, adjust anything if sensitive as it is, then show again with the result you want. Copy and Paste will work into your thread.

 

[Slongy]

Andy show a small amount of your data, adjust anything if sensitive as it is, then show again with the result you want. Copy and Paste will work into your thread.

Hi Healey 21, thanks for your response. So basically I need to be able to indentify which of the Reference ID's matches in the column and return that so I can pivot on them, they fomrula would look in the "Name Column" and return the result in the "Ref ID" column. If the Ref ID didn't have other text around it, I would just do a V:Lookup:

Name	Ref ID
CTG - Technologies	CTG
ECR1 - Customised GRS	ECR1
ECR1 - - World Service	ECR1
CTG - Upsell - Stand	CTG
CTG New B Surface	CTG
ECR2 CoUK - CAF	ECR2
ECR6 s data	ECR6
G and International MEBig3	MEBIG3
Inside2 - New - - Various McR and EP	Inside2
PDSC12 - New - Access	PDSC12

<tbody>
</tbody><colgroup><col><col></colgroup>

Andy

 

[healey21]

What I have done is using your list of ID is placed them in a Column and named the range Ref_ID, using that I can then search down Column A and extract any finding within the contents of the cell because there ID I have added an IFERROR rather than show #N/A. See if this helps you.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name		Healey21 Finding						Ref ID
2	CTG - Technologies		CTG						CTG
3	ECR1 - Customised GRS		ECR1						ECR1
4	ECR1 - - World Service		ECR1						ECR1
5	CTG - Upsell - Stand		CTG						CTG
6	CTG New B Surface		CTG						CTG
7	ECR2 CoUK - CAF		ECR2						ECR2
8	ECR6 s data		ECR6						ECR6
9	G and International MEBig3		MEBIG3						MEBIG3
10	Inside2 - New - - Various McR and EP		Inside2						Inside2
11	PDSC12 - New - Access		PDSC12						PDSC12
Sheet2

 

[marious]

What I have done is using your list of ID is placed them in a Column and named the range Ref_ID, using that I can then search down Column A and extract any finding within the contents of the cell because there ID I have added an IFERROR rather than show #N/A. See if this helps you.

Nice formula Healey21 and thank you for sharing. Just to clarify. Long text descriptions or strings for engineering descriptions in which the user can type whatever he wants, might contain typos and/or they might use a reference ID from a material to call another material, component or assembly. This formula will only return the first occurrence starting from left to right of the string. For example if you have the string: ”CTG - Upsell – Stand for a ECR1 component” The formula will still return CTG and not ECR1. It would be a good idea to create a formula that traces these cases too. Typos examples: As you very will know ECR! Might be ecr1 or ECR 1 or ECR-1 etc.
I hope it helps.
Marious

 

[marious]

Please disregard the previous post
Nice formula Healey21 and thank you for sharing. Just to clarify. Long text descriptions or strings for engineering descriptions in which the user can type whatever he wants, might contain typos and/or they might use a reference ID from a material to call another material, component or assembly. This formula will only return the first occurrence as it is listed in the Ref_ID. For example if you have the string: ”CTG - Upsell – Stand for a ECR1 component” The formula will still return CTG and not ECR1. In the other hand if you have: ”ECR1 - Upsell – Stand for a CTG component” The formula will still return CTG and not ECR1. It would be a good idea have a formula that traces these duplicate cases. Typos examples: As you very will know ECR! Might be ecr1 or ECR 1 or ECR-1 etc.
I hope it helps.
Marious

 

[marious]

I tested Healey21 formula and It works for the cases in which there is only one occurrence in the string. I have a question Why do you have duplicates in Ref_ID? I have a formula that will take care of the pattern recognition for multiple values / attributes but only works for a REF_ID with unique references. Let me know if this might work for your company

 

[markmzz]

Maybe this:

Layout:

Name		Ref ID				ListRefID
CTG - Technologies		CTG				CTG
ECR1 - Customised GRS		ECR1				ECR1
ECR1 - - World Service		ECR1				ECR2
CTG - Upsell - Stand		CTG				ECR6
CTG New B Surface ECR2		CTG	ECR2			MEBIG3
ECR2 CoUK - CAF		ECR2				Inside2
ECR6 s data		ECR6				PDSC12
G and Inside2 International MEBig3		MEBIG3	Inside2
Inside2 - New - - Various McR and EP		Inside2
PDSC12 - New - Access C ECR6		ECR6	PDSC12
CTG - Upsell – Stand for a ECR1 component		CTG	ECR1
****************************************	**	********	********	********	**	********

<tbody>
</tbody>

Array formula (use Ctrl+Shift+Enter and not only Enter):

C2-> =IFERROR(INDEX($G$2:$G$8,SMALL(IF(ISNUMBER(SEARCH("*"&$G$2:$G$8&"*",$A2)),ROW($G$2:$G$8)-ROW($G$2)+1),COLUMNS($C2:C2))),"")

Markmzz

 

[marious]

Very close Markmzz. I like the they way that you extend the small index by Column. Thanks for sharing.<title>Excel Jeanie HTML</title>


<!-- ######### Start Created Html Code To Copy ########## -->

<html><head><title>Excel Jeanie HTML</title></head><body>


<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
	A	B	C	D	E
1	Name	Healey21 Formula	Largest indexed Value	Second Largest	Ref ID
2	CTG - Technologies ECR1	CTG	CTG	ECR1	CTG
Patterns

?

?

<!-- ######### End Created Html Code To Copy ########## -->


</body></html>