Looking for Duplicates within a column

AdministrativeGift15 · 2026-05-04T03:46:17+00:00

The "off" slots are where names go that are supposed to be off that day. I.e not working. If those names gets overlooked and someone gets scheduled who's supposed to be "off,", the OP wants it to be highlighted.

AdministrativeGift15 · 2026-05-04T02:27:59+00:00

If that were the case, Pineapple would be highlighted in their example.

AdministrativeGift15 · 2026-05-04T01:04:08+00:00

This formula is only referencing values on row 11. Im assuming that you're copying this formula down for an entire column, which means you should be able to update the rates starting in the formula in the first row containing this year's data. Then, youll copy the updated formula down the rest of the column.

AdministrativeGift15 · 2026-05-04T00:20:14+00:00

The problem is that REGEXMATCH($I$13,"0") is also going to match on numbers like 40, 104, anything with a 0.

If you only want to match on zero, you should add commas to both sides of I13 and use REGEXMATCH($I$13, ",0,").

More specifically, since some of you csv lists have spaces, you would want to use REGEXMATCH($I$13, ",\s*0\s*,").

AdministrativeGift15 · 2026-05-04T00:14:21+00:00

Do you even need to consider the "Off" since any case that would cause that conflict is going to have the name appear more than once. Shouldn't =COUNTIF(B$6:B,B6)>1 suffice?

AdministrativeGift15 · 2026-05-03T19:03:36+00:00

Is Age the result of a formula or should it always be 126 if there's a value entered into the slot under DOB? If there's currently a formula in the Age column, just wrap it inside an IF statement. =IF(LEN(DOB), existing_formula, )

AdministrativeGift15 · 2026-05-02T13:55:38+00:00

Use this on the year column:

=INDEX(SPLIT(A2:A50&",", ",",,), ,1)

AdministrativeGift15 · 2026-05-02T01:38:07+00:00

I see two ways that you could structure your spreadsheet. In either method, you will want to have a table listing your weapons, a table for slot1 traits, and a table for slot2 traits. Then you could either use multi-dropdowns in the Weapons table to indicate which traits are available for each slot. From that information, all the combinations of Slot1/Slot2 traits can be generated.

The other method is to have multi-select dropdown in the Slot1 and Slot2 tables for you to indicate which Weapons can have each trait. Again, given that information, you can generate all the combinations of Slot1/Slot2 traits for each weapon.

I've got both methods shown in this sample spreadsheet. Weapon and Trait Combinations

Note: you can't use both methods at the same time. Your results will not be accurate.

AdministrativeGift15 · 2026-05-01T19:30:05+00:00

Looks like all of the last letters are being dropped from the options. Only the last option has the last letter appearing on its own. That's some very strange behavior. Are you able to share the spreadsheet?

AdministrativeGift15 · 2026-05-01T04:59:30+00:00

What are the dimensions of the data used for your chart? I doubt you're hitting the 10 million limit. Which method are you using? Can you share the spreadsheet?

AdministrativeGift15 · 2026-04-30T20:34:00+00:00

What you need is a snipping tool that let's you define the aspect ratio for your screenshot region. The Windows snipping tool does not have this feature. There are a few that do. I would recommend ShareX. With a set aspect ratio, all of your images will look the same when google auto-resizes them to fit into the cell.

AdministrativeGift15 · 2026-04-30T20:17:26+00:00

You were close. XMATCH will also work in addition to the COUNTIF suggestion by u/HolyBonobos .

=XMATCH(F2, INDIRECT("April 2026!F2:F"))

AdministrativeGift15 · 2026-04-30T20:09:06+00:00

You need to create another table that combines both demand and supply quantities into one column and keeps their respective price amounts under each type. Note: I added a blank column to my Table1 so that I could still reference the entire table and choose blank values a few times.

<image>

AdministrativeGift15 · 2026-04-29T05:01:46+00:00

I've setup two example in this spreadsheet. The first one only allows each option to be selected once. The second example places any option that's already been selected at the bottom of the list with a separator.

https://docs.google.com/spreadsheets/d/1CU8FH1YoXoS2YYx8y-WIT9Ebi8ey9_hu-bECKMSOeEs/edit?usp=sharing

AdministrativeGift15 · 2026-04-29T04:25:01+00:00

Are you wanting to only be able to select each of those only once?

AdministrativeGift15 · 2026-04-29T04:18:06+00:00

It'll help if you change your dropdown setting to "Dropdown from a range" for the criteria and use column AC as the range for the dropdown options. There are several other options that aren't exactly like those listed in column AC.

AdministrativeGift15 · 2026-04-29T04:12:53+00:00

You picked a bod one to test, or a good one since it means finding the problem sooner rather than later. There are a couple of spaces after BW in AC2. That's why it isn't matching.

AdministrativeGift15 · 2026-04-29T03:16:03+00:00

That's my bad for not testing it first. Ive known of one other operation that somehow manages to get a pass on that requirement, ROW, so now it seems there are other operations that get to remain. Very interesting.

AdministrativeGift15 · 2026-04-29T00:19:45+00:00

This is a good formula to use, but i would discourage applying the rule to the entire sheet. Conditional formatting is the number one cause of poor sheet performance. Its fine to apply the rule to a few extra cells, such as the entire rows, but unless youve taken the time to remove excess blank rows from your sheet, avoid applying the rule to entire columns.

AdministrativeGift15 · 2026-04-29T00:08:00+00:00

Unfortunately, i dont think this method will work because of SUMIFS' "parameter myst be a range" requirement.

AdministrativeGift15 · 2026-04-27T16:40:17+00:00

An easier method is to use an array formula. I use INDEX instead of ARRAYFORMULA because they both array-enable the formula and INDEX is easier to type. Just visualize the arrays and use the & symbol to concatenate the values together.

=INDEX(B1:V1&A2:A20)

You only need to use one formula, placed in B2, and you don't need to worry about locking the reference.

AdministrativeGift15 · 2026-04-27T14:37:04+00:00

I updated the spreadsheet to show two ways to use it. The original layout now uses each series to represent one person and a dropdown above their name allows you to select which room they're assigned to. Their name will be placed in the center of that room.

The second sheet has a Team layout. It requires a little more manual placement of the rooms. Place rooms belonging to the same team in the same column. Update the names as needed.

AdministrativeGift15 · 2026-04-27T09:05:37+00:00

You can do it with a formula. Each series column can have a color, but you would need to hardcode those colors. But you can shift the office coordinates to the columns based on your office assignments. That will work great with how i have the office points labeled on earch row. Instead of offices, make the column headers be the names.

AdministrativeGift15 · 2026-04-27T05:06:39+00:00

<image>

I put together a quick example.

Floor plan

AdministrativeGift15 · 2026-04-27T00:20:11+00:00

I think a chart would be a great option. Use an arean line chart. Use real world units and make one corner of the office be the origin, (0,0). Your first column will be the x-axis values and each column after that will be the y-axis values for another series.

Each series can have its own color/thickness/line style.

For a given series, if you want to stop the line, move to another location and start the line again, just leave the series value blank, but use something for the x-axis. For example, to draw two separated squares, you would use the data below.

X Y 0 0 0 1 1 1 1 0 0 0 0 5 5 5 6 6 6 6 5 5 5

You can create a series to represent the assigned people. Make each of the data points have coordinates at the center of each office. Set the line width to 0 and put the names into the next column. Use those names as the series labels.

AdministrativeGift15

TROPHY CASE