Log In
Register
Home
Active Member
February 26, 2019
Offline
I have a worksheet which has a column of suppliers and a column of centres where suppliers completed work.
Each combination of centre and supplier yields a short code which has to be entered manually after browsing the list of codes on the second sheet.
I would like to populate the short code row automatically.
With basic VBA experience I had hoped to use something along the lines of conditional statements but I am limited to the number of conditional statements I can make, even if nested. The number of combinations exceed 300 and will increase exponentially as new centres open.
Ideally, I can run either a macro or a piece of VBA code that will produce something along the lines of...
IF 'centre' in Col A = x and 'supplier' in Col C = y THEN return 'code' from "Codes Sheet" in Col B.
What would be the best option that doesn't use conditional statements? Is VBA the best solution?
I have attached the two sheets below.
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Steve
Maybe you could try using Index and Match.
Good luck.
Sunny
Active Member
February 26, 2019
Offline
Thanks Sunny,
I have added my codes to the front sheet and tried using Index and Match. I must have an error somewhere however.
I have created a tool that should return the short code and placed it next to the input sheet.
The code is...
=INDEX(Sheet1!H2:J868, MATCH(1,(F3=Sheet1!H2:H868)*(F4=Sheet1!I2:I868),0))
then Ctrl + Shift + Enter/Return to make it an array.
I'm not sure if I have understood this correctly however as it generates an error.
Also, when working with Index and Match. Is it possible to reference cell ranges across multiple sheets?
Thanks again,
VIP
Trusted Members
December 7, 2016
Offline
Hello Steve,
The INDEX/MATCH formula that Sunny has provided to you works just fine, using the helper column in Codes sheet. The reason to the #N/A results is because of inconsistent or missing data. For example in Sheet1!C2 you have supplier A.C.K but in Codes sheet you have a supplier A.C.K., with an ending point. In Sheet1!C4 and C5 you have supplier ABATE COUNSELLING &EAP LIMITED, but in Codes sheet you have only ABATE COUNSELLING etc.
What you write about in above comment seems to be different data from the initial post. Without the file it is difficult to say if your formula should work or not. If you are trying to achieve the same, do try using Sunny's approach.
Answers Post
1 Guest(s)
