2

I have an inventory spreadsheet created in really old Excel (2000!) with two pages; Inventory and Master. Everything looks fine in Google Sheets, until it comes to the ConcatIf argument on my Master page. The second page calls for words from the first page, using a fairly simple concatenation argument. Here is the formula for the first row on the Master page:

=ConcatIf(Inventory!A:A,"*"&Master!A4&"*",Inventory!B:B,", ")

It is matching the name on line 4 in Master with the name in Inventory on column A, then drawing out the words related to that name, found in column B of Inventory, and returning them as a list.

When I look at this spreadsheet in Google Sheets, the results on the Master page fail. It says I'm using four arguments, when only two are expected.

How can that formula be translated to CONCAT? I'm not a master of this language, the formula was written for me by one.

Some clarification:

This is about a spreadsheet for people and the names of photographs related to them. Inventory page has column A with various person's names, and column B with the names of images they appear in. Personal names are frequently repeated.

Master has column A with all personal names in alphabetical order, and column B seeks to concatenate all the image names relevant to the person named in A. For every name in Master A, there will be corresponding instances in Inventory A. I'm just trying to gather all the image names from Inventory B to Master B.

So concatenate still seems like the answer to me. It's only a question of translating it from Excel to Google.

Here is a link to an example spreadsheet created in Google, using the formula suggested by zaq. https://docs.google.com/spreadsheets/d/1kfxHnm5nHHrwLBCFCGLxoyuBqEFKj1l_AqCzIcVFYew/edit?usp=sharing

4

This is more dynamic if you also use regex instead of wild cards as the specific way in which your using the wildcard is different in google sheets compared to excel .... but this works:

=join(", ", filter(Inventory!B:B, istext(regexextract(Inventory!A:A,Master!A2))))

enter image description here

Note: you should also finally wrap your whole function with =iferror() so that you dont have to see that ugle #N/A when there are no matches... secondly , occasionally i convert everything to lowercase inside of the matching portion of the formula so as to avoid any one of many complications that can happen when you user generated content or input.

That would end up looking like this:

=iferror(join(", ", filter(Inventory!B:B, istext(regexextract(arrayformula(lower(Inventory!A:A)),lower(Master!A2))))))

enter image description here

0

To my knowledge, ConcatIf is not a standard Excel function but a custom function written by someone in VBA. My guess for what it means:

=ConcatIf(filter_range, value, concat_range, separator)

joins the entries of concat_range where the corresponding filter_range entry matches value, placing separator in between.

This logic can be expressed in Google Sheets by the built-in functions filter and join as follows:

=join(separator, filter(concat_range, filter_range = value))

In your case this would be

=join(", ", filter(Inventory!B:B, Inventory!A:A = "*"&Master!A4&"*"))

By the way, in Google Sheets one rarely needs concat function, which is for joining two strings, and is equivalent to "&" operator. The functions to use are concatenate and the aforementioned join.

  • Thank you for this. I applied that formula to a test cell, but the results are "No matches are found in FILTER evaluation". – Steve Mills Dec 18 '16 at 18:36
  • So, nothing in Inventory!A:A matches "*"&Master!A4&"*" in your spreadsheet. – user135384 Dec 18 '16 at 18:43
  • Maybe I didn't clarify my spreadsheet well enough. The formula could have been misleading. – Steve Mills Dec 18 '16 at 18:53
  • I'm having difficulty with this conversation. Editing my comments is impossible at my level. I will try to be more explicit. – Steve Mills Dec 18 '16 at 18:57
  • Inventory has two columns that are at issue. Column A has first names of individuals, column B has words associated with them. For each instance of a name appearing in column A (they all appear multiple times), I am trying to gather the words from column B into a new cell in Master. – Steve Mills Dec 18 '16 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.