Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Strong Sauce: Jul 2, 2003; You know I am not really your father.

Is there anyway to highlight a whole row based on whether or not a value in the row is in a list posted in a column?

For example if I have a list in the last Column that has Apple, Orange and Lime I want rows where the name of the fruit is in the row to be highlighted.

Edit: Figured out what I wanted to do with Conditional Formatting.

Strong Sauce fucked around with this message at 02:00 on Jul 30, 2009

# ¿ Jul 30, 2009 00:40

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 11:35

Strong Sauce: Jul 2, 2003; You know I am not really your father.

I hate asking because I keep forgetting what the Excel functions are called.

Basically I have a column of numbers (A), I want to do conditional formatting such that if I have a set of numbers in another column (B) it will highlight in green the values of B that are not in A.

Basically if a value in B is not part of A, highlight it.

Edit: N/m it's VLOOKUP

Strong Sauce fucked around with this message at 03:42 on May 24, 2011

# ¿ May 24, 2011 02:54

Strong Sauce: Jul 2, 2003; You know I am not really your father.

C-Euro posted:

Speaking of SUMIF functions and time, my wife is working up a simple expense sheet for her startup to help her break down costs by type and month-

The SUMIF for breaking down costs based on type is easy enough, but I'm not sure how best to do the by month SUMIF. Is there something I have to add to the second argument of the function to help it recognize months in Column B, or do I need to write out the months in Column I in a certain way?

Put this in J2 and copy it down.

quote:

=SUMIFS($A2:$A, $B2:B, ">=" & DATEVALUE(MONTH($I2&1) & "/1/2022"), $B2:$B, "<=" & DATEVALUE(MONTH($I2&1) & "/31/2022"))

quote:

=MONTH("January" & 1) converts to 1
=MONTH("February" & 1) converts to 2, etc...

Edit: apparently the above formula does not like 2/31/2022 kinda dates, DATEVALUE is not necessary, and you need to use $A$2/$B$2 to anchor those ranges so the correct way apparently is this:

quote:

=SUMIFS($A$2:$A, $B$2:B, ">=" & MONTH($I2&1) & "/1/2022", $B$2:$B, "<=" & EOMONTH(DATEVALUE(MONTH($I2&1) & "/1/2022"), 0))

The last part generates the date for the end of the month

Strong Sauce fucked around with this message at 09:24 on Mar 2, 2022

# ¿ Mar 2, 2022 09:09

Strong Sauce: Jul 2, 2003; You know I am not really your father.

https://twitter.com/benlcollins/status/1562546876024778753

lambdas, xlookup, makearray, scan. no more using weird arrayformulas to try and apply a function to each row/cell!!!

# ¿ Aug 25, 2022 21:20

Strong Sauce: Jul 2, 2003; You know I am not really your father.

https://workspaceupdates.googleblog.com/2022/08/named-functions-google-sheets.html

Rapid Release and Scheduled Release domains: Gradual rollout (up to 15 days for feature visibility) starting on August 24, 2022

# ¿ Aug 25, 2022 23:00

Strong Sauce: Jul 2, 2003; You know I am not really your father.

Busy Bee posted:

I hope a Google Sheet question is okay here

I have a column in a Google Sheet where I copy and paste a date in YYYY-MM-DD format from the data we receive from the supplier. However, since we have many suppliers and thousands of rows of data, sometimes the date format they send to us is incorrect which messes up the automated data extraction.

What would be the best way to ensure that when the data is copy and pasted to have some sort of notification that the provided date format is incorrect? I was looking into conditional formatting but not sure if that would suffice.

I just realized after typing this all up that you might be asking for the date column to auto convert to YYYY-MM-DD...

If you're talking about where one field has the date as "2/2/2023" and another one has "Feb 2, 2024" and you want it formatted to YYYY-MM-DD. Click on the Column Header that has the Date field (so if it is Column B, click on the B in the sheet so that it highlights the entire column), After, go to Format|Number and choose "Custom date and time formats." By default it should already be using YYYY-MM-DD so just click Apply.

If you want to highlight invalid columns to show ones that Google could not format:

Go into your sheet, Click on Format|Conditional Formatting, and click on "Add another rule"
Under, "Apply to Range:" Specify, A1:<X>, where <X> is the last column in your sheet. If you have headers, use A2:<X>

So if you have 5 columns, where the last one ends in E, you would input "A1:E". If you have headers it would be, "A2:E"

Under Format Rules:
Under, "Format cells if...", Choose "Custom Formula is" at the very bottom.
In the Input Box underneath where it now says, "Custom Formula is" put in

code:

=AND($B2<>"", NOT(ISDATE($B2)))

Where B is the column your date is in, and 2 is if you have headers. If you don't have header use 1. Because your range is over multiple columns you must have that $ in there.

Choose a color in the Formatting Style (click on paint bucket or click on the header to choose Green/Yellow/Red

This will now highlight any rows that Google deems to be an "invalid" date.

------

# ¿ Feb 6, 2023 10:17

Strong Sauce: Jul 2, 2003; You know I am not really your father.

Just noticed Google started adding more formulas back in March/April
https://workspaceupdates.googleblog.com/2023/01/google-sheets-powerful-functions-advanced-analysis.html
EPOCHTODATE: Converts a Unix epoch timestamp in seconds, milliseconds, or microseconds to a datetime in UTC.
MARGINOFERROR: Calculates the amount of random sampling error given a range of values and a confidence level.
TOROW: Transforms an array or range of cells into a single row.
TOCOL: Transforms an array or range of cells into a single column.
CHOOSEROWS: Creates a new array from the selected rows in the existing range.
CHOOSECOLS: Creates a new array from the selected columns in the existing range.
WRAPROWS: Wraps the provided row or column of cells by rows after a specified number of elements to form a new array.
WRAPCOLS: Wraps the provided row or column of cells by columns after a specified number of elements to form a new array.
VSTACK: Appends ranges vertically and in sequence to return a larger array.
HSTACK: Appends ranges horizontally and in sequence to return a larger array.
LET: Assigns name with the value_expression results and returns the result of the formula_expression. The formula_expression can use the names defined in the scope of the LET function. The value_expressions are evaluated only once in the LET function even if the following value_expressions or the formula_expression use them multiple times.

Really glad they've added LET

# ¿ Jun 11, 2023 19:59

Strong Sauce: Jul 2, 2003; You know I am not really your father.

excel or google sheets?

what is "output something"?

might be better to show what you want the output to look like. it seems you have both the data from image 1 (order guide) and the data for image 2.. but what is the output?

# ¿ Jul 31, 2023 04:37

Strong Sauce: Jul 2, 2003; You know I am not really your father.

Lester Shy posted:

I am very stupid and I'm trying to figure out if there's a better way to do a tedious job.

Once a month I need to go through and export the hours reported by the people in my department. I'm looking to see 1) how many days off each person had and 2) if they have actually completed their timesheet for the month. Were they sick/on vacation or have they just not filled out their hours? Essentially I am looking for gaps between days worked.

The software we use generates a report like this:

I was trained to manually go through and add a new row for each "missing" day and then fill down until I have a complete month. Then count all of the gap days. If there's more than one day off per week, double check with the separate leave request sheet or reach out to the person and ask them to fill out the missing days.

This sucks because it takes a long time, and there's a lot of room for error because you're doing this for 30+ people, including people who work night shifts, which take up two rows on the report and create more room for confusion.

There has to be a smarter way to do this, right?

What are you trying to do? You want to be able to see which are the gapped days and/or are you also trying to figure out what the gapped days are and to see if this person has an entry in a second sheet that lists their time off? If it's the latter what does the second sheet look like?

# ¿ Aug 6, 2023 22:02

Strong Sauce: Jul 2, 2003; You know I am not really your father.

Lester Shy posted:

What I want is a way to look at a list of dates and quickly see and count which and how many dates are missing instead of going through and manually CTRL + SHIFT + I'ing every time I find a missing date. It would be great if I could add a row for each missing date automatically. I can't just count how many entries there are and compare it to 30/31 because some people work overnight, which generates two entries on the sheet.

Re: your second question. "Sheet" was bad wording on my part; it's a separate website that displays everybody's time off. I could quickly turn it into its own sheet if needed, but I'm relatively unconcerned with leave; only a few people have vacation time each month. When I find a questionable gap in the time sheet it usually means the person just hasn't submitted their hours for the month and I need to bug them about it.

So I can't really offer an automated solution because the only real way to do that is via scripting since using Formulas to add new rows would cause issue once if you try to fill in cells where the formula expects there to be no data.

I tried this out with Google Sheets which should be 1:1 for the same named formulas so it should work in Excel.

To figure out how many days are missing

code:

=SUMIF(BYROW(Sheet1!$A:$A, LAMBDA(row, IF(INDEX(row) <> "", DATEDIF(OFFSET(INDEX(row), -1, 0), INDEX(row), "D") - 1, 0) )), ">0")

Where Sheet1$A:$A references the column of dates you have.

What this does is it goes through each row, looks at the date in the previous row and counts the number of days between them (minus 1)

Also visually if you want to be able to quickly tell, what about using a Conditional Formatting?

In Excel I think it is, first Select the entire worksheet range, go to Home Tab > Styles, click on Conditional Formatting, click New Rule. Go to the last Rule Type, which should be, "Use a formula to determine which cells to format"

then in the text filed under "Formula values where this formula is true:" put in

code:

=DATEDIF(DATEVALUE(OFFSET($A1, -1, 0)), DATEVALUE($A1), "D") > 1

This should highlight the row after a missing date so if the rows were
7/7/2023
7/8/2023
7/10/2023
7/11/2023
it should highlight the row for 7/10/2023. so at the very least you can see at a glance if they're missing a day.

Here's an example of what it looks like in Google Sheets.

# ¿ Aug 6, 2023 23:38

Strong Sauce: Jul 2, 2003; You know I am not really your father.

im so glad when they got XLOOKUP into Google Sheets. I no longer had to generate my own range just so it'd work with VLOOKUP

# ¿ Sep 9, 2023 20:01

Strong Sauce: Jul 2, 2003; You know I am not really your father.

I don't know who Google has working on Sheets but they're kinda loving up my sheets.

I was trying to figure out how to count all occurrences of column N that are in column D and I wrote this...

code:

=ARRAYFORMULA(COUNTIF(N:N, D2:D1000))

now obviously this was a mistake because it's going to iterate over every cell in these two columns, creating an error that'll say its NxD long and that the space you gave it is too small.

except what google sheets is doing now is it keeps creating new rows in the sheet. i deleted the formula once i realized what was happening but by then it had created 11000 more rows.

there's been these kinda small issues for about 1-2 months now. another one, which i can't remember if this was what it did before but if you have two sheets, and you toggle between them using your keyboard (alt + up/down cursor), when you go back to the original sheet the cell that the cursor is in will move down by 1. so if it was in cell A24 on the first sheet, and you jump to the second using the keyboard shortcut, when you comeback to the first sheet the cell will be on A25 now.

i'm not sure if this is as designed because if you just click the sheets regularly, it doesn't move the highlighted cell. i'm pretty sure this change was also pretty recent. i'm not sure if i like it or not though since i think there are some use cases. but it's weird that they're just doing this without mentioning these kinda changes.

# ¿ Jan 11, 2024 06:54

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 11:35

Strong Sauce: Jul 2, 2003; You know I am not really your father.

Hmm I'm guessing Excel doesn't let you glob two columns together into a list or an array? Cause you can do that in Google Sheets.

# ¿ Feb 8, 2024 05:50

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread