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
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