Hello,
I am bit stuck with my formula and hoping you guys can help me find a solution
I have 2 columns containing strings of text - for example:
<tbody>
</tbody>
Please note that cell c should be linked to each region in cell D: Abcd15/22 is linked to Jpxxx, CNxxx, DExxx, EPxx, CNxxx etc...
I have a vba code to clean and split both columns as per below:
Sheet1
<tbody>
</tbody>
Sheet2
<tbody>
</tbody>
I have a sheet counting the occurence of each region using =(LEN(C4)-LEN(SUBSTITUTE(C4; $D$2; "")))/LEN($D$2)
Now, I am trying to sum the region occurence for each code and I am using: SUMIF('Region count'!$C:$C;"*"&A3&"*";'Region count'!$D:$D) -> Array formula (where col C is the "unclean" string of text, Col A is the list of all codes cleaned and col D the count of occurence for region JP)
<tbody>
</tbody>I had to do a few "arrangements" already... counting the number of charcters to add a space as some of the codes are too similar (ex: G01S), now i have an issue with codes like G01S1 suming up number of occurence from G01S13, G01S17, etc...
Is there a way to use sumif for exact match? If any other suggestion please let me know.
Also, as I am trying to create a template report hence I need everything to be linked and reference.
Last thing, my file is already running slow but i only have 7 sheets, formulas & array and 2 short vba code. Is there anything I can do to make if run better?
Hope my explanations are clear, if not please let me.
Thanks a lot for your help,
CORALIE
<tbody>
</tbody>
I am bit stuck with my formula and hoping you guys can help me find a solution
I have 2 columns containing strings of text - for example:
C | D |
Code | Region |
Abcd15/22 | Bcd2/16 | Bcd21/04 | Bcd22/04 | def6H/04 | Bcd/04 |H01q1/00 | Gos1/44 | JP4685349B2 | CN1316677C | DE60222772D1 | EP1407511A1 | CN1524318A |
<tbody>
</tbody>
Please note that cell c should be linked to each region in cell D: Abcd15/22 is linked to Jpxxx, CNxxx, DExxx, EPxx, CNxxx etc...
I have a vba code to clean and split both columns as per below:
Sheet1
c | d | e | f | g | h | i | j |
code 1 | code 2 | code 3 | code 4 | code 5 | code 6 | code 7 | code 8 |
Abcd15 | Bcd2 | Bcd21 | Bcd22 | def6H | Bcd | H01q1 | Gos1 |
<tbody>
</tbody>
Sheet2
c | d | e | f | g |
region1 | region2 | region3 | region4 | region5 |
JP | CN | DE | EP | CN |
<tbody>
</tbody>
I have a sheet counting the occurence of each region using =(LEN(C4)-LEN(SUBSTITUTE(C4; $D$2; "")))/LEN($D$2)
Now, I am trying to sum the region occurence for each code and I am using: SUMIF('Region count'!$C:$C;"*"&A3&"*";'Region count'!$D:$D) -> Array formula (where col C is the "unclean" string of text, Col A is the list of all codes cleaned and col D the count of occurence for region JP)
<tbody>
</tbody>
Is there a way to use sumif for exact match? If any other suggestion please let me know.
Also, as I am trying to create a template report hence I need everything to be linked and reference.
Last thing, my file is already running slow but i only have 7 sheets, formulas & array and 2 short vba code. Is there anything I can do to make if run better?
Hope my explanations are clear, if not please let me.
Thanks a lot for your help,
CORALIE
<tbody>
</tbody>