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

«‹›82 »

Banjo Bones: Mar 28, 2003

Ohhh! Thank you guys =)

# ? Jul 25, 2023 05:57

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 19:33

Disappointing Pie: Feb 7, 2006; Words cannot describe what a disaster the pie was.

Ok I have what I hope is a pretty easy one... I work for a grocery store that has a couple hundred locations. We have some tools but we have to come up with our own ways of parsing data a lot of the time. I am able to get data each day for sales for a list of UPC's or items. I also have a master list of every item our supplier offers, but in some categories it is hundreds of items.

I want to do something like look at my current canned dog food section on the aisle, pull out all the UPC's from the master guide for every canned dog food I am able to order and get, compare that data to the current movement from my store and find what items do not have movement so I can fill gaps in for items I am missing.

So basically our order guide looks like the following, this is a small sample of that section:

And the sales data I get looks like this, as you can see it only returned 3 items, the rest are items I am not currently carrying:

-----

So if that makes sense I essentially just need to take a row that has quantity and a UPC, find the exact match in another set of data for that UPC and if it can find it, it outputs something to show me that I already have that item and here is what that quantity is. I hope that makes sense? I'm sure this is something with vLookups but I cannot wrap my head around how those work myself for some reason.

# ? Jul 31, 2023 01:04

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

Fingerless Gloves: May 21, 2011; ... aaand also go away and don't come back

You can use an xlookup to get that information, syntax is

=Xlookup(sheet1upc, sheet2upccolumn, quantitycolumn)

But the rest of the request is a little vague as mentioned above

# ? Jul 31, 2023 08:02

HootTheOwl: May 13, 2012; Hootin and shootin

If you have a couple hundred locations surely there's a database out there to query against?
VLookup is fine for exact matches and returning one row of data but once you start trying to find multiple then you're trying to use a spreadsheet as a database

# ? Jul 31, 2023 11:35

Lester Shy: May 1, 2002; Goodness no, now that wouldn't do at all!

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?

Lester Shy fucked around with this message at 20:33 on Aug 5, 2023

# ? Aug 5, 2023 20:28

HootTheOwl: May 13, 2012; Hootin and shootin

You can make a blank sheet with the dates, and then use a v lookup to port the hours from the first sheet. It will be reusable and help you quickly identify the blanks

There's a countif function you can use to quickly count the blanks.

I don't think excel has a built-in way to detect breaks in a pattern

# ? Aug 5, 2023 20:49

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

Lester Shy: May 1, 2002; Goodness no, now that wouldn't do at all!

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.

# ? Aug 6, 2023 22:33

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

HootTheOwl: May 13, 2012; Hootin and shootin

Start with the output from your machine, there on the left.
Make a second worksheet which covers your months (middle column)
Add a formula which looksup if the date has any hours on the left there (formula bottom, output right)

Now that days off have been identified you want to locate the ones within a week?
That depends how you define a week, seven days starting at a specific day? Or exactly Sunday through Saturday?

# ? Aug 7, 2023 18:07

Fingerless Gloves: May 21, 2011; ... aaand also go away and don't come back

I'm trying to create an email tool in VBA for sending out reminders for people to input their god drat kpis in our online capture system, and I've got most of the details sorted, but I'm facing an annoying issue which I'm struggling with.

Part of the email will be to send a link to each KPI's input, which I've stored in Excel was a hyperlink. When this gets input into the email, the link doesn't get carried across, it just becomes plain text. Is there any way to keep that link? This is on my work laptop so I can't get the code, but it's currently like this (Only the bit with the link since the rest doesn't seem important):

Excel:
Cell A2 - KPI ENTRY HYPERLINK

vba extract:
with outlookmail
.htmlbody = WS1.range("A2")
end with

Resulting Email:
KPI ENTRY HYPERLINK (This is plain text and no hyperlink is carried in)

# ? Aug 24, 2023 12:32

HootTheOwl: May 13, 2012; Hootin and shootin

Fingerless Gloves posted:

I'm trying to create an email tool in VBA for sending out reminders for people to input their god drat kpis in our online capture system, and I've got most of the details sorted, but I'm facing an annoying issue which I'm struggling with.

Part of the email will be to send a link to each KPI's input, which I've stored in Excel was a hyperlink. When this gets input into the email, the link doesn't get carried across, it just becomes plain text. Is there any way to keep that link? This is on my work laptop so I can't get the code, but it's currently like this (Only the bit with the link since the rest doesn't seem important):

Excel:
Cell A2 - KPI ENTRY HYPERLINK

vba extract:
with outlookmail
.htmlbody = WS1.range("A2")
end with

Resulting Email:
KPI ENTRY HYPERLINK (This is plain text and no hyperlink is carried in)

My guess is the problem is that you're not providing the html version of a hyperlink, you're only providing the text of one. So when converted to html is just displays the text as-is.
So wrapping it in the proper html will make it hyperlink.

code:

.htmlbody = "<a href=" & WS1.range("A2") & ">TEXT</a>"

Remember, this makes it an element, so the TEXT is what will be displayed to the user, replace it with whatever you want. and the href tag will tell outlook to treat it as a hyperlink element.

HootTheOwl fucked around with this message at 12:55 on Aug 24, 2023

# ? Aug 24, 2023 12:53

nielsm: Jun 1, 2009

If you mean that A2 has a clickable hyperlink in Excel, showing "KPI ENTRY HYPERLINK" as text, and then having some URL as destination, and you want to make that into an HTML link, you can extract the link destination with the Range.Hyperlinks(n).Address property:

Visual Basic .NET code:

.htmlbody = "<a href=" & WS1.range("A2").Hyperlinks(1).Address & ">" & WS1.Range("A2") & "</a>"

# ? Aug 24, 2023 13:29

Fingerless Gloves: May 21, 2011; ... aaand also go away and don't come back

I've used both of those and they have both worked for different bits I needed, thank you both so much for your help!

# ? Aug 24, 2023 14:27

internet inc: Jun 13, 2005; brb
_{taking pictures

of ur house}

I have an Excel sheet that's 150 lines by 30 columns and the data is mostly text. It is shared between 5 users and they all use it for different purposes. One user hides certain columns and rows, another user sorts the table differently, one often forgets to close the file (and it sometimes messes up the syncing) and then everyone's upset when their view of the sheet changes.

What's the best/easiest way to make each user a copy that pulls the data from the main file? Power Query seems to work alright, but it doesn't transfer the formatting and it resets hidden columns and column width every time it refreshes.

There are a few formulas in the main file so I'd like to keep using Excel, but it would be nice if it were something else for the users. The simplest read-only interface that allows them to filter, sort and hide rows/columns, maybe?

# ? Aug 29, 2023 14:37

nielsm: Jun 1, 2009

Maybe that should be a SharePoint List instead of an Excel file.
When you say formulas, what is it more precisely?

# ? Aug 29, 2023 15:17

internet inc: Jun 13, 2005; brb
_{taking pictures

of ur house}

It's all basic stuff like age/date calculations, conditional formatting and joining text cells. I guess none of it is really necessary if each "view" of the data is custom made.

Another option would be to automate printing each view to a separate PDF and have the users access those, but I feel like there has to be a better way to do this, haha.

I'll take a look at SharePoint Lists, thanks!

# ? Aug 29, 2023 15:54

HootTheOwl: May 13, 2012; Hootin and shootin

Give each user their own sheet which is equal to the master sheet whom no one could touch.
Or you remove their save permissions so their changes don't persist.

# ? Aug 29, 2023 17:20

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

Hey, thread.

I'm looking for a way to highlight duplicate values in cells without using conditional formatting.

This is for a cycle counting tool I'm working on. I have a list of bin locations in column A, and I have a macro I found that let's me hit a button to generate a random list of bins from that to count in column B. Hitting the button again will generate another list in colum C, D, etc etc etc. Due to the way I'm designing the tool, I'd like to highlight the cells in column A that have been listed in columns B, C, D, etc., in green to help visually track what's been counted already. Further, I want to also highlight any duplicates amongst the generated lists in red. So like, if the same bin location is entered in columns B and E, then it'll highlight those in red to show that that bin has been counted already. This is because there might be times where we have to count specific bins, and a generated list wouldn't be needed.

My main issue is that I don't want to use conditional formatting for this, because the generated lists will end up being deleted each quarter, which fucks up which cells are effected by the conditional formatting. Is there a formulaic way to accomplish this?

# ? Sep 6, 2023 15:08

HootTheOwl: May 13, 2012; Hootin and shootin

neogeo0823 posted:

Hey, thread.

I'm looking for a way to highlight duplicate values in cells without using conditional formatting.

This is for a cycle counting tool I'm working on. I have a list of bin locations in column A, and I have a macro I found that let's me hit a button to generate a random list of bins from that to count in column B. Hitting the button again will generate another list in colum C, D, etc etc etc. Due to the way I'm designing the tool, I'd like to highlight the cells in column A that have been listed in columns B, C, D, etc., in green to help visually track what's been counted already. Further, I want to also highlight any duplicates amongst the generated lists in red. So like, if the same bin location is entered in columns B and E, then it'll highlight those in red to show that that bin has been counted already. This is because there might be times where we have to count specific bins, and a generated list wouldn't be needed.

My main issue is that I don't want to use conditional formatting for this, because the generated lists will end up being deleted each quarter, which fucks up which cells are effected by the conditional formatting. Is there a formulaic way to accomplish this?

No.

What you are asking for is the definition of conditional formatting: You have a cell, and you want to format the cell (apply colors, highlights) based on a condition.

# ? Sep 6, 2023 15:28

HootTheOwl: May 13, 2012; Hootin and shootin

But, i think if I understand your problem right, you can take these deleting into account when you create the conditions.
When you say columns B are deleted, does something take their place?

# ? Sep 6, 2023 15:29

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

Welp, I just realized that the macro I'm using has a couple of lines to delete and shift cells that it's done with. So that's what seems to be changing which cells the rule applies to. I changed that to merely clearing the contents of the cells, and that seems better.

I really like the ease of use of conditional formatting, but I really hate how easy it is to gently caress with which cells the rules apply to.

# ? Sep 6, 2023 16:42

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

How about this one, then? The first reply here is the macro I'm adapting for my uses. In the bottom chunk of code, there's the line to delete the entire column and shift to the left. What's the best way to modify that line to clear out everything in the target column except for the first row, without shifting anything? I know about ClearContents, but given the rest of the code, I'm not sure what the proper way to implement it is here.

# ? Sep 6, 2023 17:04

HootTheOwl: May 13, 2012; Hootin and shootin

neogeo0823 posted:

How about this one, then? The first reply here is the macro I'm adapting for my uses. In the bottom chunk of code, there's the line to delete the entire column and shift to the left. What's the best way to modify that line to clear out everything in the target column except for the first row, without shifting anything? I know about ClearContents, but given the rest of the code, I'm not sure what the proper way to implement it is here.

something like:

code:
Dim i As Integer Dim X As Integer //the last row you want to apply to For i = 2 To X Cells(Y, i).Value = "" Next i
You'll need to solve for X.
Y is the column index you want to clear. That might be hardcoded or you'll have to solve for it.
I'm pretty sure cells are 1-indexed instead of zero indexed, so you just want to start your for loop counting at 2. Alternatively the loop can start at 1 and your Cell reference can be i+1.

Actually just use the ClearContents Function:

code:

Dim oldValue as string = Cells(X,1).Value
Range(X:X).ClearContents
Cells(X,1).Value = oldValue

Solve for X where X is the column you want to clear.

HootTheOwl fucked around with this message at 17:17 on Sep 6, 2023

# ? Sep 6, 2023 17:12

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

You could add conditional formatting to the macro. Simplest way is just record a macro, select the range, add the conditional formatting. Then append that code to your existing after the column deletion/manipulation.

edit:
Bored at work so I trimmed it down to the bare minimum to highlight duplicates red in column A

code:

Range(�A:A�).FormatConditions.AddUniqueValues
Range(�A:A�). FormatConditions(1).DupeUnique = xlDuplicate
Range(�A:A�). FormatConditions(1).Interior.Color = 13551615

DRINK ME fucked around with this message at 01:42 on Sep 7, 2023

# ? Sep 7, 2023 01:33

Busy Bee: Jul 13, 2004

Hi all, I have such a small and basic Excel question that I'm hoping you can help me with.

I'm organizing my finances and I have one main table with all accounts and a second one with only a few select ones that points to the various cells in the main table. When I make changes in the main table, it automatically changes in the second table.

However, the issue is that when I sort the main table, of course the second table gets messed up because it uses a basic "=A2" formula to get the main table cell value.

What is the solution here? I remember learning about absolute references but not sure if this is relevant.

# ? Sep 9, 2023 08:52

nielsm: Jun 1, 2009

Two ideas come to mind, depending on how your data actually look and what kind of view you are making:

a) Use a pivot table to filter/summarize the data in a dynamic way.

b) Use the VLOOKUP function to find the appropriate rows by some key, and extract data from them.

# ? Sep 9, 2023 09:27

Busy Bee: Jul 13, 2004

nielsm posted:

Two ideas come to mind, depending on how your data actually look and what kind of view you are making:

a) Use a pivot table to filter/summarize the data in a dynamic way.

b) Use the VLOOKUP function to find the appropriate rows by some key, and extract data from them.

I took the template from this Google Sheet here under the "Holdings" tab - https://docs.google.com/spreadsheets/d/1U7HoZMaPDIaq-4EeXqCljoQhkGm9T0Zt8WZPaYj6KKE/edit?usp=sharing

What you see in the Holdings sheet is the main table, and then I copied it to the second table to only include the relevant accounts I want to see. For the second table, it links to the cells in Column A, B and F (Account, Symbol and Shares).

# ? Sep 9, 2023 09:35

HootTheOwl: May 13, 2012; Hootin and shootin

What's your question then? Both of their suggestions should perfect

# ? Sep 9, 2023 12:57

Busy Bee: Jul 13, 2004

My question is, if I were to use VLOOKUP, how can I use it to have multiple data points?

Column A has "Individual Account" and "Retirement Account" while Column B both has the same ticker name "S&P500".

For example: If Column A = "Individual Account" and Column B = "S&P500", show data in Column C.

Or would it be easier to have one unique ID for each row to link to?

# ? Sep 9, 2023 14:12

HootTheOwl: May 13, 2012; Hootin and shootin

Oh, vlookup can't be used on composite keys.
You'll need to do index/ match or do what you said and add a third column with a unique key.

# ? Sep 9, 2023 15:43

Harminoff: Oct 24, 2005; ð½

Just a heads up but vlookups have been replaced with the much better xlookup. Soo much easier to use and can return value on either left or right

# ? Sep 9, 2023 16:08

Combat Pretzel: Jun 23, 2004; No, seriously... what kurds?!

No question, really. Just a complaint as to why these jerks at Microsoft won't modernize the goddamn charts, and add more types. This complaint mostly stems from writing a bunch of webapps using ECharts for charting and seeing nicer things over there.

(--edit: Also, I just watched some dude on Youtube do some wind speed experiments and chart them out, just to spot these horrid default colors.)

Combat Pretzel fucked around with this message at 16:16 on Sep 9, 2023

# ? Sep 9, 2023 16:13

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Harminoff posted:

Just a heads up but vlookups have been replaced with the much better xlookup. Soo much easier to use and can return value on either left or right

XLOOKUP broke the meta and cost my boy MAKRO a chance at the gold. :mad:

# ? Sep 9, 2023 16:48

Lib and let die: Aug 26, 2004

Hughmoris posted:

XLOOKUP broke the meta and cost my boy MAKRO a chance at the gold.

Does native python support in Excel going to be considered some sort of PED for this type of stuff?

# ? Sep 9, 2023 19:36

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

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Lib and let die posted:

Does native python support in Excel going to be considered some sort of PED for this type of stuff?

At that point they might as well turn Excel into a mobile game for the masses!

# ? Sep 10, 2023 00:35

Xenoborg: Mar 10, 2007

Is ther a way to resolve all of the references in a formula? For budgeting, I have a cell of all my credit cards bills due that is =$F$19+$F$20+$F$21+$F$22. When all statements for a month are in, I paste values, and it becomes a flat -2500, but what I really want to see is for it to bring in all the refs and make it like =-1000-1000-200-300, which would make checking a lot easier when something is wonky.

# ? Oct 6, 2023 15:44

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 19:33

DarkHorse: Dec 13, 2006; Nap Ghost

Xenoborg posted:

Is ther a way to resolve all of the references in a formula? For budgeting, I have a cell of all my credit cards bills due that is =$F$19+$F$20+$F$21+$F$22. When all statements for a month are in, I paste values, and it becomes a flat -2500, but what I really want to see is for it to bring in all the refs and make it like =-1000-1000-200-300, which would make checking a lot easier when something is wonky.

Probably not exactly what you're looking for, but you can use the Evaluate Formula to step through the calculation and see where things break

# ? Oct 6, 2023 16:24

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

«‹›82 »