3

I am using some data created by someone else in Google Sheets. I have it in my document in its original structure in a sheet called "Data", which is formatted as follows:

A B
1 Name Category
2 Item 1 Category A
3 Item 1 Category B
4 Item 1 Category C
5 Item 2 Category B
6 Item 2 Category D

I am creating another sheet that has a category in each row and an item in each column:

A B C
1 Item 1 Item 2
2 Category A X
3 Category B X X
4 Category C X
5 Category D X

What I'm trying to write is a formula for each of the cells in B2..C6 that would output the letter "X" if a matching row exists in the Data sheet such that both its name and category match. The above is an example of test data and expected output data.

There may be any number of rows, any number of items, and any number of categories in the Data sheet. The second sheet may have any number of rows and columns, one column per item and one row per category.

I am not at all certain where to begin looking for how to solve this, including what I should even search for.

What would be a formula that achieves the desired output?

Improve this question

4 Answers 4

Reset to default
4

Formula

Dynamically adjusts for any combination of items and categories.

=LET(iRng,Data!A2:A, cRng,Data!B2:B, iUnq,TOROW(UNIQUE(iRng),1), cUnq,TOCOL(UNIQUE(cRng),1), array,BYCOL(iUnq, LAMBDA(i, BYROW(cUnq, LAMBDA(c, IF( COUNTIFS(iRng,i,cRng,c),"x",))))), {{""; cUnq}, {iUnq; array}})

Explanation

  1. The Items and categories ranges, Col A and Col B, are stored in iRng and cRng.
  2. Unique items are stored as a row in iUnq and unique categories are stored as a column in cUnq. TOROW and TOCOL are both set to skip empty cells.
  3. BYCOL passes each unique item one-by-one into a LAMBDA function that stores the current item in i.
  4. The LAMBDA's formula is itself a BYROW function that passes each category in cUnq one-by-one into another LAMBDA function that stores the current category in c.
  5. This LAMBDA's formula returns "x" if COUNTIFS matches both i and c on the same row in the original ranges, iRng and cRng
  6. Once the second LAMBDA has applied its formula to each category, the first LAMBDA's formula is passed the second item, and the second LAMBDA's formula then repeats the same process for the next item, and the resulting array of blank and "x" is stored in the variable array.
  7. Finaly a new array is built and returned that includes the categories in column 1, the items in row 1, and the array of blank and "x" starts in row 2, column 2, expanding its width and height dynamically as required.
Improve this answer
3
  • 1
    Here's this solution using the sample data: docs.google.com/spreadsheets/d/…. As you can see, the item columns are duplicated when they shouldn't be. Commented Feb 19, 2024 at 18:15
  • @M.Justin It requires nesting the UNIQUE within TOROW not the other way around. I caught that originally but then reintroduced it. It's now corrected. Ty. Commented Feb 19, 2024 at 21:06
  • 1
    Thanks for the fix. Confirmed that this now works as desired. Commented Feb 20, 2024 at 0:11
1

I'm not sure this is anywhere near an optimal answer, but here's what I currently have working.

I've created another sheet ("Data (concat)"), which is based on the values in the Data sheet. It has a row per row in the Data sheet, with cell values being equal to the concatenation of the corresponding name and category. For instance, A2 is =CONCATENATE(Data!A2,"|",Data!B2).

A
1 Name|Category
2 Item 1|Category A
3 Item 1|Category B
4 Item 1|Category C
5 Item 2|Category B
6 Item 2|Category D

In my original sheet, I do a VLOOKUP on these cell values based on the concatenation of the corresponding name and category. For instance, B2 is =IF(ISNA(VLOOKUP(CONCATENATE(B$1,"|",$A2),'Data (concat)'!$A$2:$A,1,false)),"","X").

This results in the desired output:

A B C
1 Item 1 Item 2
2 Category A X
3 Category B X X
4 Category C X
5 Category D X
Improve this answer
0

Select the table in the Data worksheet, Select Insert|Pivot table and create the table into a new worksheet. In the Pivot table editor, Add Category to Rows, Name to Columns and Category to Values. The Values aggregation defaults to COUNT correctly. Uncheck Show totals check box for Rows and Columns.

The desired results (if you can accept a value of '1' instead of 'X') are shown in the table. As the OP points out, Adding a Calculated field with the formula ="X" provides the desired result.

While the other proposed solutions will work, they all involve cell level calculations that are likely less efficient, and both are substantially more difficult to explain.

Improve this answer
4
  • Thanks! I just tested this out, and it doesn't work quite as described, but with a few tweaks it gets exactly what I want. First, there is no default to "Values" (you stated that it defaulted to COUNT. Second, making Values the calculated field ="X" will use X, exactly as desired. And finally, one should uncheck "Show totals" from both Rows and Columns, as they are checked by default and give "Grand Total". Commented Feb 18, 2024 at 23:23
  • Here's what the pivot table solution looks like with the sample data, with the changes I suggested: docs.google.com/spreadsheets/d/…. Commented Feb 19, 2024 at 18:19
  • 1
    You're right. I see the answer had bad formatting when I assigned the Category column to Values. This is what resulted in the COUNT default. Your change to the Values formula is a neat way to change the shown active cells. I also skipped clearing the totals check boxes although I had done this in my test. I'll update the answer. The sample you provide shows the result, but the read-only status means I could not see the Pivot table editor. Commented Feb 19, 2024 at 19:04
  • 1
    You could save it as a local copy (File -> Make a copy) to see the pivot table. Commented Feb 19, 2024 at 19:11
0

For each non-header cell in the original sheet, the following formula outputs "X" if the item and column matches one of the values in the Data sheet, and "" if it doesn't (replacing B$1 and $A2 with the matching column/row, such as automatically happens with a standard copy/paste):

=IF(COUNTIFS(Data!$A$2:$A, B$1, Data!$B$2:$B, $A2)<>0,"X","")

After creating a header row for each category and a header column for each item, this results in the desired output when the formula is applied to each cell in the grid:

A B C
1 Item 1 Item 2
2 Category A X
3 Category B X X
4 Category C X
5 Category D X
Improve this answer
3
  • Google sheet with this solution: docs.google.com/spreadsheets/d/… Commented Feb 19, 2024 at 19:49
  • IF function will interpret 0 as FALSE and any non-zero number as TRUE, and the value_if_true or value_if_false can be left empty to return an empty value. So your formula can be simplified to IF(COUNTIFS(rangeA, criteriaA, rangeB, criteriaB), "X",) Commented Feb 19, 2024 at 21:33
  • 1
    @Blindspots I don't necessarily intend for that Google doc to be permanent, so I'm OK with it being in the transient comment as a temporary example that this does in fact work. Commented Feb 20, 2024 at 0:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.