• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A matching problem

P

Phuzzy

New Member
I have a doozy. At least for me. I am using Excel 2003.


I have a two lists of documents. I want to find out if a document exists on both lists. Not a problem. The problem is that there are two file naming conventions in use.


The old standard: Document Number_Document Name_OurDocumentType.fileextention for example: L2_3_2_This is a document_REFERENCE.pdf


The new standard is Document Name (Document Number) OurDocumentType for example: This is a document (L2_3_2) Reference


The document number can vary in size and length so it could be L3_1 or L3_1_54_34

Document types can be reference, processes, procedures, etc.


Is there a way to reconcile these using a fancy formula? Or am I forced to manually reconcile....


Thanks in advance.
 
Can you use this formula to convert all names to the old style, and then do the comparison check from there?

=IF(ISNUMBER(SEARCH("(",A2)),MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)&"_"&LEFT(A2,FIND("(",A2)-1)&"_"&MID(A2,FIND(")",A2)+1,999),A2)
 
That is an amazing formula. It works kind of... However, the spaces in between words need to be underscores....Is that do-able? I would love to see a formula forensic on this one...
 
Let's wrap the whole thing in a SUBSTITUTE function to get rid of those pesky spaces.

=SUBSTITUTE(IF(ISNUMBER(SEARCH("(",A2)),MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)&"_"&LEFT(A2,FIND("(",A2)-1)&"_"&MID(A2,FIND(")",A2)+1,999),A2)," ","_")


Trying to give a quick explanation. First part of formula checks to see if there's a parenthesis in the formula:

ISNUMBER(SEARCH("(",A2)),


Search looks for a text string, and if found, returns a number. Hence, the ISNUMBER function.


Next, I just started cutting up the parts of the text that I needed using the MID and FIND function. MID lets us pull out portions of text, provided we know where to start. That's where the FIND function helps. Once I know the position of the parenthesis, I can figure out where to start pulling out text and how much text I need to pull.


I'd encourage you to play around with the MID and FIND functions, and maybe use the Formulas - Evaluate Formula tool to see how the formula does each bit.
 
Unfortunately, this formula is not quite working as well as the previous one. Here is an idea. Can we strip the document number, document type and file extention to leave just the base filename. Then I can search for that string within strings on the other sheet.
 
Strange. I was getting some extra spaces, which can be tweaked a little bit. Taking this formula:

=SUBSTITUTE(IF(ISNUMBER(SEARCH("(",A2)),MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)&"_"&LEFT(A2,FIND("(",A2)-1)&"_"&MID(A2,FIND(")",A2)+1,999),A2)," ","_")


With this phrase:

Related to Usage and Billing Inquiry (L1_3_111) Reference.pdf


I get:

L1_3_111_Related_to_Usage_and_Billing_Inquiry_Reference.pdf


Is that not what you get?


We can try and extract the file name, using a similar idea. It just becomes tricky to extract only that portion in the old file name system.
 
You must have read my post before I edited it. My issue was a column width issue. I am getting extra spaces using this latest formula.


Acceptable Forms and Methods of Payment (R1_770) Policy.pdf

becomes

R1_770_Acceptable_Forms_and_Methods_of_Payment___Policy.pdf


I see how it is happening...but now I think, due to the fact that although there are conventions, there is some discrepancy when people name the files so no formula will work 100%. So how to proceed? Tweek this formula or like I said before, strip everything and have a base filename?
 
Getting the name from new convention is easy enough.

=LEFT(A2,FIND("(",A2)-2)


will that give you enough to compare the two lists? Ideally, I see the two lists as being that with old style and those with new style (can distinguish based on the parenthesis use). Then, you can just check if see if the extracted text string using formula above is anywhere in old list. Formula to do that would look like this:

=SUMPRODUCT(1*(ISNUMBER(SEARCH(NewString,OldList))))>0
 
Okay Luke. Let's see how Ninja you are. Using the formulas above I get a true/false result if the text is found in the list. If it is true, how do I get the value from the list that is two columns over? My formula is =SUMPRODUCT(1*(ISNUMBER(SEARCH(C2,'Catalog Mar 14'!A$2:A$10000))))>0


As you can see, my trimmed name exists in C2, and it searches from A2-A10000 on the Catalog Mar 14 sheet. On the Catalog Mar 14, sheet in column C is a category value that I would like transferred over to the current sheet if the outcome is true.


Are you ninja enough? :)


Thanks again.
 
@Phuzzy


Hi!


Look if Luke M is enough ninja... the other day he was seen dressed in black, with the skin slightly yellowed and slanted eyes... walking by Rodeo Drive, of course!


Regards!
 
I've seen many of his postings around the site, and I am sure that he is ninja enough. I like to have a bit of fun and make it look like it is a challenge. I'm pretty confident it would take him about 1/2 a second to have an answer for me.


Perhaps Luke is not so much a ninja as he is a Jedi.
 
You must log in or register to reply here.
Back
Top