Multiple matches using Index Match - Prompt user to select which match to use as input

[rudenstam]

In a client workbook I am categorizing the client's suppliers based on previously collected information.

Basically, I will receive a set of supplier names together with total annual expenditure.

This information goes into on sheet with three columns: (1) Supplier name, (2) total annual expenditure and (3) Purchasing category.
In column 3 I am using a standard Index Match formula to look up the category for each individual supplier. This information is listed in another sheet functioning as a register/database with information collected over several years from different clients.

The formula is working fine. My issue is that there are instances where a supplier is listed 2 or 3 times in the register with different categories wich then results in the formula getting multiple matches and using the first match.
An example would be a supplier that sells both software and hardware.

What I would like to have happen is that the user in one way or the other would be prompted to select which match to use.
Ideally the user would be presented with a drop down list in the target cell to choose category from. However, if that's not possible, the cell being highlighted would suffice.

I have attached an example file in which Supplier B is listed both as a provider of Computer hardware and Computer software.
I am using office 365 in Swedish. I don't know if formulas are translated automatically based on language settings used by the user, if not, I want to clarify that for the Index match formula the Swedish version replaces 'MATCH' with 'PASSA'.

Grateful for any help I can get!

[kvsrinivasamurthy]

Work sheet event is used. Drop down is provided in column C of Supplier data sheet. Helper column AB is used.
Code

Please Login or Register  to view this content.

To paste the code
Right click on Sheet tab --> view code
VB window opens.
Paste the code
Close the window.

[Glenn Kennedy]

In the register, in your sample, the data was sorted by supplier (the sole duplicate... supplier B 's entries were in adjacent rows). Is that ALWAYS the case?

[rudenstam]

Thank you, this works brilliantly! However, do you know if it's possible to have the script combine your function with highlighting cells with multiple matches?
The actual workbooks I am working with sometimes include thousands of suppliers. Checking each cell to see if there are multiple matches would therefore make it very time consuming.
If the cells are highlighted, for instance by changing fill color, it would make my life a lot easier

[rudenstam]

In the register, in your sample, the data was sorted by supplier (the sole duplicate... supplier B 's entries were in adjacent rows). Is that ALWAYS the case?

No this is not always the case, I just threw together the example file quickly before posting. The data is almost always sorted by expenditure.

[rudenstam]

Work sheet event is used. Drop down is provided in column C of Supplier data sheet. Helper column AB is used.
Code

To paste the code
Right click on Sheet tab --> view code
VB window opens.
Paste the code
Close the window.

Thank you, this works brilliantly! However, do you know if it's possible to have the script combine your function with highlighting cells with multiple matches?
The actual workbooks I am working with sometimes include thousands of suppliers. Checking each cell to see if there are multiple matches would therefore make it very time consuming.
If the cells are highlighted, for instance by changing fill color, it would make my life a lot easier

[rudenstam]

Fixed the issue with providing an indication to the user for cells with 2 or more mathces using COUNTIF and IF formulas. Thanks for your help!

[kvsrinivasamurthy]

In Sheet Register columns E to M I have given Formulas. Column E gives Names in alphabetical order. Columns F to M gives the category names for that Supplier. 1600 rows are covered. As you add names in column A automatically it up dates In E to M columns.
code changed for worksheet event. This works very fast irrespective of number rows (even in thousands).

Please Login or Register  to view this content.