Search and Finding between 2 workbooks

[benjaminpott]

I have a large database that contains information layed out as follows:

(column G)

CallDirect_CDR-790seu_approval.doc
CallDirect_CDR-790seu_approval.pdf
CallDirect_CDR-790seu_approval_A.doc
Motorola_V975.doc
Motorola_V9x.html
Motorola_V9x.pdf

etc... (it has 4000 lines)

I need a program or function to search through each entity and pick up the brand (the words before the underscore.

so for example if we take the RED example, it should pick up just the following:

CallDirect

Then I need it to find the model, in the same example it should find the following:

CDR-790seu

So now it has gained 2 vales; the name: CallDirect and the model:CDR-790seu .

The next stage is for it to look through a different workbook (this workbook only contains 1 sheet) - (I can arrange this sheet to be put in the same workbook as the the first database) and firstly search the name, then product... the 2.d database it needs to look through wout look like this:

(all *'s indicate a new column)

A1 * A2 * A3 * A4 * A5

114 * closed * Call Direct * router * CDR-790
153 * closed * Calyptech * FWT * iFWT
35 * closed * Calyptech * FWT * iFWT

It would find the red row and take just what value is in A1, in this case 114.

This 114 value would now be placed back in the original sheet next to the line it was searching from to now make:

CallDirect_CDR-790seu_approval.doc * 114


If anyone could help me with this that would be amazing...

thanks guys

benjaminpott

[rylo]

Hi

1) Any way that the models in both documents can have the same value? In this example you have CDR-790 and CDR-790seu being the same...
2) If the model issue can be resolved, would you expect the 114 to go against the first 3 example items?
3) What happens to those items that don't have a model between two hyphens? Like the last 3 examples?


rylo

[foxguy]

Hi benjaminpott;

One additional question. Can a new column be added to the 2nd workbook?

[benjaminpott]

Yes a new column could be added, there is no way of changing the information within the cells as there are masses of information (thousands of lines).

Is this maybe something that should be done in VBA instead of a function.

[foxguy]

Hi benjaminpott;

My feeling is that if a new column could be added to the 2nd file (if it's a sheet in the 1st file, everything would probably be faster).

F1 : =C1&E1.
Then after the 1st file pulls out the modal and brand, it could easily find the match in the 2nd file, by doing a search on Column F

or
F1 : =D1&"_"&E5
Then the 1st file wouldn't necessary have to find both the brand and model. It would just look for the 2nd "_" and do a search in the 2nd file in column F.
This has the added benefit of being faster, by not wasting time on a 2nd find in the 1st file.

Still have to deal with Rylo's questions.


by the way:
In your 1st message

A1 * A2 * A3 * A4 * A5

Should be

A1 * B1 * C1 * D1 * E1