Matching values from column B&A and returning C

[Jon_R]

Hi All

I have the following excel document. You can download it from here

I would like to be able to populate the Version information from worksheets Terminal Inventory and Cashier Inventory into the Summary worksheet.

As you can see i've had some partial success but I have a problem were data isn't constant. I have data in Terminal Inventory that relates to terminal name, shop location, and software version. The problem is that terminal name should follow a naming convention where the shop location is part of the terminal name, but in some cases i have the incorrect shop location as part of the terminal name. My next column along contains the correct shop location. Therefore I would like to be able to find the shop location, then identify in column A if the terminal is in postion 1-4 (a number will always be present), and then display the terminal version from colum C. You can see i'm currently cutting this data down to only display from character 18 for 13 characters as I only need this information. I dont have this problem with cashiers as we only have 1 cashier in each shop.

formula currently being used is: =MID(CELL("Contents",INDEX('Terminal Inventory'!$C:$C,MATCH("*"&A2&"*1*",'Terminal Inventory'!$A:$A,0))),18,13)

Any help or advice please?

This workbook does have a macro i made to delete all enteries in the 2 worksheets where we have OLD data that I dont want to interpret.

Cheers.

Jon.

 

[Rijnsent]

Hi Jon,
I found only a couple of lines where I couldn't find column B in column A (as in: shop numbers not matching), but what I would do in this case is create an extra column in your data sheet with a formula with the "corrected" name:
=LEFT(A1;4)&B1&RIGHT(A1;LEN(A1)-10)
And then use that one in your formula...
G'luck,
Koen

 

[Jon_R]

Hi Koen

Your right this is the easiest option. I was making it much more complicated than it needed to be .

Thanks.

Jon.