Building Vendor profiles and establishing master vendor name for match/index

Emily McNeeley

We manage a lot of vendor information in Smartsheet for various projects. We are working on building a master list of outcomes for a client and the vendor name can vary project to project. As an example, one project may say, ABC Corp and another will list the same vendor as ABC Corporation. We want to analyze the outcomes for both ABC Corp and ABC Corporation on one master sheet and have written index/match formulas but this would require so many vendor name columns. Is there a better formula or way to manage these vendor name variations?

Brett Wyrick

I take it that getting users to input information in the same format (i.e. always using "ABC corp" instead of "ABC Corporation") is probably out of the question, right?

If I was trying to solve this, here's what I'd do:

Create a new sheet named "Vendor Names & Variations". This will be used for an easy Index/Match function on the initial sheet you screenshotted (and can be utilized on any other Sheet).

Columns for "Vendor Names & Variations":

1. Vendor Name Inputted
2. Vendor Name Actual

This sheet will hold all variations of spelling and such for each Vendor Name. You'll have to do some manual input, but you'd only have to do it each time a variation of the name would come up. Here's an example:

Then, going back to your initial sheet...

I'd rename your "Vendor Name" to "Vendor Name Input", then create a new column named "VendorName" (just to keep it different). Then I'd hide your "Vendor Name Input" column, if possible.

Then you could utilize a formula to compare the "Vendor Name Input" column from the initial Sheet to the newly created Vendor Names & Variations sheet using Index/Match, then pull in the "Vendor Name Actual" value from that sheet.

So, your initial sheet would have something like this:

To get the values for the new VendorName column on your initial sheet, you'd need to create 2 references to the newly created Vendor Names & Variations sheet:

1. One reference for the "Vendor Name Inputted" column (name it "VendorNameActualColumn")
2. One reference for the "Vendor Name Actual" column (name it "VendorNameInputtedColumn").

Then the formula for VendorName would go something like this:

=INDEX({VendorNameActualColumn}, MATCH([Vendor Name Input]@row, {VendorNameInputtedColumn}, 0))

Make that a column formula, and then you're finished.

I hope this all makes sense. Let me know if this helps!

Krissia B.

Hello @Emily McNeeley ,

Hope all is well! Do you have something setup so far? May you provide us screenshots of what your sheet looks like & the setup (please block out sensitive data) so we can reference it with building a formula for what you are trying to accomplish? Thank you!

Cheers~

Krissia

Emily McNeeley

XYZ Final Disposition and ABC Final Disposition are matching against Vendor Name BUT the issue is on the source document for ABC Final Disposition, the vendor names may be different. I'm trying not to have a ton of columns.

Brett Wyrick

I take it that getting users to input information in the same format (i.e. always using "ABC corp" instead of "ABC Corporation") is probably out of the question, right?

If I was trying to solve this, here's what I'd do:

Create a new sheet named "Vendor Names & Variations". This will be used for an easy Index/Match function on the initial sheet you screenshotted (and can be utilized on any other Sheet).

Columns for "Vendor Names & Variations":

1. Vendor Name Inputted
2. Vendor Name Actual

This sheet will hold all variations of spelling and such for each Vendor Name. You'll have to do some manual input, but you'd only have to do it each time a variation of the name would come up. Here's an example:

Then, going back to your initial sheet...

I'd rename your "Vendor Name" to "Vendor Name Input", then create a new column named "VendorName" (just to keep it different). Then I'd hide your "Vendor Name Input" column, if possible.

Then you could utilize a formula to compare the "Vendor Name Input" column from the initial Sheet to the newly created Vendor Names & Variations sheet using Index/Match, then pull in the "Vendor Name Actual" value from that sheet.

So, your initial sheet would have something like this:

To get the values for the new VendorName column on your initial sheet, you'd need to create 2 references to the newly created Vendor Names & Variations sheet:

1. One reference for the "Vendor Name Inputted" column (name it "VendorNameActualColumn")
2. One reference for the "Vendor Name Actual" column (name it "VendorNameInputtedColumn").

Then the formula for VendorName would go something like this:

=INDEX({VendorNameActualColumn}, MATCH([Vendor Name Input]@row, {VendorNameInputtedColumn}, 0))

Make that a column formula, and then you're finished.

I hope this all makes sense. Let me know if this helps!

Emily McNeeley

This makes sense and you are right that I can't get them to change how they input it. I like the idea of having a reference sheet just to manage names - thank you!

Brett Wyrick

https://community.smartsheet.com/discussion/comment/309395#Comment_309395

You are very welcome! Let me know if you get stuck and I'll try and help again! Glad this works for you!