Combine Names Based on Criteria

I've searched for how to do this, and I think it should be simple…but it is not coming to me.

I have a list of vendor names that appear multiple times, which I have written my formule to index them all. However, due to Amazon having a transaction code at the end of each purchase, I have multiple instances which give an appearance of different vendors. How do I use the criteria from the name, to bring them all together to appear as the label of "Amazon", as well as ensure all transaction totals come through only as Amazon.

Here is a snipped of my sheet. Unfortunately the option to use a numerical ID does not align, so I have to use name.

I also need to be sure that the data can be sorted without loosing the formula. Right now, I'm using this formula for the names: =INDEX(DISTINCT({Vendor Name}), 1 + COUNT(Vendor$1:Vendor5))

Thank You

Find more posts tagged with

Accepted answers

All comments

Kerry St. Thomas

Can you add a helper column with =IF(CONTAINS("Amzn",[Vendor Name]@row),"Amazon",[Vendor Name]@row) as a column formula, and then apply your formula to that helper column?

TracyS

There are a few different versions of this in a transaction report of over 8k. I'd need to identify the 3-4 ways the name pulls. So Amzn, Amazon, Amazon.com, so probably an or statement, or a similar indexing to pull into a new sheet

TracyS

I tried this as a formula

=IF(OR(Vendor@row = "Amzn Mktp US"), "Amazon", IF(OR(Vendor@row = "Amazon.com"), "Amazon", IF(OR(Vendor@row = "Amazon Ret"), "Amazon", Vendor@row)))

But it still pulls in the full detail for all Amzn Mkpt, so the return becomes Amzn Mktp US A65q90go3

Kerry St. Thomas

The difference between what I originally suggested and your formulas is I'm suggesting using CONTAINS . That looks at any part of the cell in question, instead of the entire cell. That'll give you a bit more scalability and won't result in something slipping through when inevitably a new vendor name variation pops up.

This formula will capture ALL instances where "Amazon" or "Amzn" are in the Vendor name, and bucket them into your "Amazon." =IF(OR(CONTAINS("Amazon", Vendor@row), CONTAINS("Amzn", Vendor@row)), "Amazon", Vendor@row)

TracyS

Thanks so much, I was attempting a similar formula that day, but looks like I forgot to enclose my contains sections. This was very helpful, I always appreciate this group

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.