Hi! First time poster here. I’ve read through some threads on these forums which have helped me before (Thanks!) but I couldn’t find a solution to my current dilemma, I've been fighting with this issue all day and hoping maybe someone could give me a hand.
I have 2 sets of data I am trying to compare, which will give me multiple returns. My goal is to get a list of the all 3 fields (Document Number, Project, and Filename) on one spreadsheet. (See attachment please)
Sheet 1: Contains the Document Number that ends up being a partial value (search parameter)
Sheet2: Contains the Project and Filenames (filenames contain the document number and random characters) I have to reference
Sheet 3: Needs to show the Document Number, Project #, and associated Filename
Array Formula is as follows in C2 (to find the corresponding filenames):
=INDEX(Sheet2!$B$2:$B$23,SMALL(IF(ISNUMBER((SEARCH(Sheet1!$A$2,Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1)))
Array Formula in B2 (to find the corresponding project):
=INDEX(Sheet2!$A$2:$A$23,SMALL(IF(ISNUMBER((SEARCH(Sheet1!$A$2,Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1)))
This lets me find all “Filename” items in Sheet2 with the “Document Number” in it and lists it, along with the project, in Sheet 3. However, how would I get the next “Document Number” in Sheet1 to become the search parameter when the return is #NUM? Until all the “document numbers” in Sheet1 are depleted?
I’ve tried the following and a few variations of it, which doesn’t work. =ISERROR(INDEX(Sheet2!$B$2:$B$23,SMALL(IF(ISNUMBER((SEARCH(Sheet1!$A$2,Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1))), =INDEX(Sheet2!$B$2:$B$23,SMALL(IF(ISNUMBER((SEARCH((Sheet1!$A$2+ROW()+1),Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1))))
And then on top of that I also need to figure out how to add the “Document Number”(partial value) from Sheet1 into ColumnA in Sheet3 in direct correlation to the value in ColumnC Sheet3.
I may be doing it backwards, and if so, someone please point it out. Maybe I should put all this in a macro instead?
Disclaimer: This is a small sample, this is actually list of 5000+ files.
Thanks for any help you can provide. Sorry if it’s a convoluted post, I’m brain dead at the moment.
Bookmarks