Compare and add Data from one WB to another and add results

[chris25151459]

Hi guys, I have a rather specific task i would like to automate to make workflow at work as efficient as possible. First let me put you in the picture.

As part of my job i have to test certain equipment and complete a workbook with the test results ready fro submission. I have automated most of this process already using VBA but my skills are limited to say the least. Here is what i want to achieve:

I have one workbook that contains the test equipment's information and results (Workbook "A") and another that acts as the report i need to submit (Workbook "B").

Example Workbook A (Contains the test results)

Cell C4 = Equipment "NAME" Cell G4 = Test Result

Example Workbook B (where the results are going to go)

Cell AN20 = Equipment "LONGER NAME" Cell AP20 = Test Result

The names are not exactly the same and they are not always in the same location as projects differ from one to another, so referencing is out i think.

What i would like to try and do is figure out a way in VBA that looks at the cell AN20 in WB "B" and only take the =Right(A1, 18) information in that cell.
Then compares this with the information in WB "A" and finds the result that belongs to the information provided.
Once excel has found the info it then prints the result in cell AP20 in WB "B". I need to run the VBA for all the equipment names in WB "B" which are always in different columns but they are always 3 columns back from the end result after that there is no more information, so maybe there is a way to find the last cell of a row that has info in it and reference it from there?

I would really appreciate any help, Thanks.

Chris

 

[tdh777]

Hi Chris,

Based on what you have written (which would have been clearer with some sample data ) you don't need VBA.

You could just use the following formula in your workbook B , AP column and copy the formula down as needed :

=VLOOKUP('[WorkBookName]SourceSheetName'!$C:$G,RIGHT(AN1,18),5,FALSE)

.. which means that you are simply looking up the right 18 characters of the Long name in Workbook B into Workbook A column C, and then returning Column G (the result) because you are searching Column C and then Column G in WorkBook A is the 5th column across.

Now admittedly this is based on assuming that you have accurately described your situation, but it's not clear whether the results data in WorkBook A are varying widths (ie result not always in Column G) or whether you were referring to the location of the equipment name in Workbook B as being in a varying column.

To get the 3rd-last column in a row you can use

=INDEX($5:$5,1,COUNTA($5:$5)-2)

which will return the 3rd-last column in eg Row 5. (Just change the row number as needed). This formula assumes that there are no blank cells in the row.

This should be enough to get you close to the solution you need and you can make the final adjustments yourself.

If you are still stuck after trying then by all means ask for some more help here.

Cheers,

Warren K.

 

[chris25151459]

Hi Warren,

Thanks for your solution, i really appreciate your help however i am getting the #VALUE ! error in the cell in WB B where the results should be I have added the correct workbook but cant understand why i am getting this message?

=VLOOKUP('[WB A.xlsx]Table 1'!$C$4:$H$32,RIGHT(AN20,18),5,FALSE)


Thanks

 

[tdh777]

Sorry Chris, my (quite embarrassing) bad !

Should be :

[COLOR=#333333]=VLOOKUP([/COLOR][COLOR=#333333]RIGHT(AN20,18),[/COLOR][COLOR=#333333]'[WB A.xlsx]Table 1'!$C$4:$H$32,5,FALSE)[/COLOR]

Must have been asleep when I typed that the first time !

(The syntax is VLOOKUP(lookup-value<lookup-value>, lookup-area (column 1 is searched) <lookup area="">, return-column-number, match-type<column number=""> (false=exact)<match-type>)

Cheers,
Waz.</match-type></column></lookup></lookup-value>