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 »

FreshFeesh: Jun 3, 2007; Drum Solo

I probably could, but I got it sorted out using the multi-COUNTIFS above. Not scalable beyond the ~8 columns I'm currently checking, but for now it was workable enough.

Thanks!

# ? May 5, 2023 09:50

Adbot: ADBOT LOVES YOU

# ? May 13, 2024 05:19

HootTheOwl: May 13, 2012; Hootin and shootin

FreshFeesh posted:

I probably could, but I got it sorted out using the multi-COUNTIFS above. Not scalable beyond the ~8 columns I'm currently checking, but for now it was workable enough.

Thanks!

It scales linearly, Adding X columns will add some fixed multiple of X work each time.
The concat+search will only require editing a range when you add columns, but SEARCH returns an error if the text isn't found which you can account for but it's annoying.

Glad you got what you needed though, even if only for now!

# ? May 5, 2023 12:58

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

Not sure if this counts as "small" but here we go

Also not a programmer by any stretch of the imagination, but hopefully can explain this clearly.

I'm starting off with a 2 column list, basically web pages that link to each other like a sitemap. What I need to do is find all the iterations of navigation paths.

So if this is the starting sample list:

pre:

Origin  	Target
index_01	target_01
index_01	target_02
index_01	target_03
index_01	target_04
index_01	target_05
target_01	target_02
target_01	target_09
target_01	target_10
target_02	target_11
target_02	target_01
target_03	target_09
target_03	target_10
target_03	target_12

What I need to do is loop through each pair of Source and Targets to find a complete path from each start page to an end page.

This is the desired output, so one line per possible path:
(manually put together example)

pre:

index_01	target_01	target_02	target_11
index_01	target_01	target_09	
index_01	target_01	target_10	
index_01	target_02	target_11	
index_01	target_02	target_01	
index_01	target_03	target_09	
index_01	target_03	target_10	
index_01	target_03	target_12

The one saving grace is that each path can be considered a separate item, and only going forward through the list. So in the first line of the output, even though target_02 does link back to target_01, we're not concerned about that until we deal what that starting point. Otherwise the first would just be an infinite regress of those 2 pages pointing to each other.

# ? Jun 2, 2023 21:15

HootTheOwl: May 13, 2012; Hootin and shootin

This isn't something you should be doing in excel imo, but what you're asking is a remake of dykstras shortest path algorithm with each pair being a path with a distance of 1
E: phone posting, but I feel that VBA might support the graph type and what you're trying to do is make a graph so that and it's inherent functions would be everything you need
https://learn.microsoft.com/en-us/dotnet/api/microsoft.visualstudio.graphmodel.graph?view=visualstudiosdk-2022

HootTheOwl fucked around with this message at 22:36 on Jun 2, 2023

# ? Jun 2, 2023 21:44

nielsm: Jun 1, 2009

HootTheOwl posted:

https://learn.microsoft.com/en-us/dotnet/api/microsoft.visualstudio.graphmodel.graph?view=visualstudiosdk-2022

This is most definitely not relevant for Excel development. That class exists in the Visual Studio SDK and is only meant to be used for developing extensions for Visual Studio (which has no relation to VBA in Office.)

You can probably find some code examples of graph data structures for Visual Basic (VBA or VB6) that can be useful for starting from, but I do agree that having that kind of problem is a good indication that Excel is the wrong tool.

# ? Jun 2, 2023 22:58

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

Well, Excel and/or Access is pretty much what I have access :haw:

to, but I'll check out some of the results I searched under graph data structures to see if there's a better way of breaking it down.

# ? Jun 5, 2023 13:30

HootTheOwl: May 13, 2012; Hootin and shootin

nielsm posted:

This is most definitely not relevant for Excel development. That class exists in the Visual Studio SDK and is only meant to be used for developing extensions for Visual Studio (which has no relation to VBA in Office.)

I shared it because the MSDN includes all libraries Microsoft makes available, including the one that includes Graphs. The hope was that this vb.net Class and library would both explain to the poster what they were looking for and also maybe there was a VBA equivilant.

# ? Jun 5, 2023 15:25

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

I think what I'm after is a Depth-First Search?
I saw a good visualization on a CS prof's site here: https://www.cs.usfca.edu/~galles/visualization/DFS.html

The only thing I can see wrong with this implementation is that is the simple graph example generated is that it ignores Node 5 because it was already visited, whereas I would want to keep it because it's still a valid unique path. Starting with Node 0

His Output:

pre:

I would want all explicit connections:

pre:

# ? Jun 7, 2023 16:12

HootTheOwl: May 13, 2012; Hootin and shootin

A depth first search is for finding individual nodes, and it does so using a fixed pattern (think, trying to solve a maze by always turning left) which means any time there's a way to hit a node using the algorithm, you'll never know the alt ways of hitting that node. And "Depth first" refers to what it's prioritizing, always going deeper

code:

START
 / \
A   B
| \ |
C   D

Depth first, to find D will go:
Start->A->C->D Ever time, because it's designed to go as deep as it can until it hits a dead end, and then up a level and find the next path until it hits another dead end (repeat forever until it finds what it's looking for)
But that's not what you want, is it? You want

code:

Start -> A (shortest path to A)
Start -> A -> C
Start -> A -> D
Start -> B
Start -> B -> D
Start -> B -> D -> A (A by way of D)
....

right?
E: and then you want to repeat it for every node in the graph?

HootTheOwl fucked around with this message at 16:57 on Jun 7, 2023

# ? Jun 7, 2023 16:54

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

Yeah! Although we could skip the Start -> A and Start -> B as outputs because both have further nodes on their paths.

# ? Jun 7, 2023 18:15

nielsm: Jun 1, 2009

Chris Knight posted:

Yeah! Although we could skip the Start -> A and Start -> B as outputs because both have further nodes on their paths.

If the graph is undirected, then only C is a "leaf" node, every other node has two or more edges going out from it. Unless you have some other criteria that determines that node D is a destination node and A or B are not.

I think what you want may be something like this:

code:

type Path: List of Node

variable FoundPaths: List of Path

procedure AllPaths (CurrentPath: Path, CurrentNode: Node)
    Let NewPath be CurrentPath with CurrentNode appended
    Append NewPath to FoundPaths
    For every edge E from CurrentNode:
        Let NewNode be the node at the other end of E
        If NewNode is not contained in CurrentPath:
            Call AllPaths (NewPath, NewNode)

Begin:
    Clear FoundPaths
    Call AllPaths(empty path, start node)

I've only tested this in my head, but I think it should produce a depth-first search for all paths from a start node, and find all paths that only visit each node at most once (so it can't loop).

# ? Jun 7, 2023 19:13

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

(Turns out I can login on my work machine!)

I've had a look more at the graph theory examples, and maybe this can help explain better.

Image, the different colours are just to make it more legible.

The data I have is basically structured like this, in 2 columns:

pre:

A	B
A	C
B	D
B	E
B	F
B	G
B	H
C	D
C	E
C	I
C	J
C	K
D	E
D	L
D	M
E	N
E	D
F	L
F	M
F	O
G	D
G	P
G	Q
G	R
H	I
H	S
I	J
I	T

Going by the Depth-First Search example I saw, I would figure the output would start looking something like this, where we try to follow each path down to the end node, with no backtracking (hence going A>B>D>E>N and A>B>E>D>L are OK, but we'd never go A>B>D>E>D or A>B>E>D>E). It's directed in that we don't go back on the path as we visit each node, but we can have many visits to each node depening on how many paths lead into it.

My manually-created attempt for the left side of the pic (only A>B paths)

pre:

Chris Knight fucked around with this message at 21:19 on Jun 8, 2023

# ? Jun 8, 2023 21:17

HootTheOwl: May 13, 2012; Hootin and shootin

Chris Knight posted:

(Turns out I can login on my work machine!)

I've had a look more at the graph theory examples, and maybe this can help explain better.

Image, the different colours are just to make it more legible.

The data I have is basically structured like this, in 2 columns:
pre:
A	B
A	C
B	D
B	E
B	F
B	G
B	H
C	D
C	E
C	I
C	J
C	K
D	E
D	L
D	M
E	N
E	D
F	L
F	M
F	O
G	D
G	P
G	Q
G	R
H	I
H	S
I	J
I	T
Going by the Depth-First Search example I saw, I would figure the output would start looking something like this, where we try to follow each path down to the end node, with no backtracking (hence going A>B>D>E>N and A>B>E>D>L are OK, but we'd never go A>B>D>E>D or A>B>E>D>E). It's directed in that we don't go back on the path as we visit each node, but we can have many visits to each node depening on how many paths lead into it.

My manually-created attempt for the left side of the pic (only A>B paths)
pre:
A					
	B				
		D			
			L		
			M		
			E		
				N	
		E			
			N		
			D		
				L	
				M	
		F			
			L		
			M		
			O		
		G			
			P		
			Q		
			R		
			D	
                                L
	                        M
				E	
					N
		H			
			S		
			I		
				T	
				J
	C
		D-tree
		E-tree
		F-tree
		I-tree
		J
		K

Depth-first traversal dictates you go down a level before moving to a node on the same level
You've already solved the D, E, F, and I paths, so because your graph is directional, you can copy+paste the results from the B path because it doesnt' matter how a level 1 node gets to D, the paths forward will always be the same. The only exception is when you have lateral movement within nodes.

HootTheOwl fucked around with this message at 21:31 on Jun 8, 2023

# ? Jun 8, 2023 21:26

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

HootTheOwl posted:

Depth-first traversal dictates you go down a level before moving to a node on the same level
You've already solved the D, E, F, and I paths, so because your graph is directional, you can copy+paste the results from the B path because it doesnt' matter how a level 1 node gets to D, the paths forward will always be the same. The only exception is when you have lateral movement within nodes.

Yeah that's the tricky part: Other than, say, A, B and C, I have no explicit depth information within the data.

If it re-visits any already found paths that's OK, so if it finds A>C>D>L and A>C>D>M even though we've technically found the D-trees under B already, it's OK.

IDK if that makes it better or worse, I don't really know enough about this stuff, which is why I'm here

# ? Jun 9, 2023 18:37

HootTheOwl: May 13, 2012; Hootin and shootin

Is this something you're going to have to run more than once?
Or would making your desired output one time be enough?

# ? Jun 9, 2023 21:45

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

Just the one time, thank goodness!

Chris Knight fucked around with this message at 18:37 on Jun 10, 2023

# ? Jun 10, 2023 18:32

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

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

Hey, thread. I've got... actually, more than a few questions I'd love to get answered, but let's start small-ish. Also, please tell me if this does, in fact, actually deserve its own thread, and I'll go post one.

At work, we've got SAP to track our work orders and inventory. The issue with it is that it's full of custom transaction modules that were all programmed by a revolving door of short-term coders over a span of years, and basically none of it works in a way that's not frustrating. I am unable to affect any changes with SAP, but I do have access to Excel, and I want to try making a thing to help us track inventory more efficiently than we currently are.

So, to that end, I've been tasked with whipping up a tool that'll take the excel-formatted-inventory list from SAP, and allow us to check parts out of stock, alert us when a part gets below the reorder point threshold, and basically act as a go-between for our other mechanics, so they spend less time getting frustrated on the computer.

I want to start with the reorder point threshold. When I export the inventory list, column F is for reorder point, column G is for maximum stock level, and column H is for total stock. The list is currently about 1500 rows long, and will continue to grow as time goes on. I would like to get a formula, or a macro, that will figure out if the value in column H is less than or equal to the value of column F, and if it is, then I'd like to output that entire row of the inventory list to a separate sheet in the workbook. Along with that, for any rows that do get picked up, I'd like to be able to subtract column H from column G, to figure out how many need to be ordered, and output that to the same destination sheet, preferably next to the appropriate row output.

What would be the "best" way to do that?

# ? Jun 13, 2023 23:14

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

I�m not sure what the best way but the simplest way you could do it is on your new sheet with
=FILTER(sheet1!A:G, Sheet1!H:H < Sheet1!F:F)
That gets you the list of lines you want, then in column I you could do
=G1-H1

If you want to go more complicated I think you could do it with a pivot table and a couple of calculated fields, one for a true/false filter to show the right rows and one for your reorder amount. You�d just have to refresh it when you paste in new data. Then you could do it in code too, it�d be a loop from row 1 to last row, IF H < F, copy the row to the new sheet, add formula for reorder amount.

# ? Jun 14, 2023 01:27

HootTheOwl: May 13, 2012; Hootin and shootin

This is a database you're describing, op

# ? Jun 14, 2023 01:32

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

DRINK ME posted:

I�m not sure what the best way but the simplest way you could do it is on your new sheet with
=FILTER(sheet1!A:G, Sheet1!H:H <= Sheet1!F:F)
That gets you the list of lines you want, then in column I you could do
=G1-H1

If you want to go more complicated I think you could do it with a pivot table and a couple of calculated fields, one for a true/false filter to show the right rows and one for your reorder amount. You�d just have to refresh it when you paste in new data. Then you could do it in code too, it�d be a loop from row 1 to last row, IF H < F, copy the row to the new sheet, add formula for reorder amount.

Thanks for this. I'll start from there tomorrow, see how it develops, and report back after I get it to work.

HootTheOwl posted:

This is a database you're describing, op

Pretty much, yeah. The short version is that our joke of an SAP :airquote:

team

is supposed to have been working 'round the clock for over a year now on improving this hunk-of-junk-code, but instead of bug fixes, they just unveiled their plans for a shiny Brand New^tm MOBILE APP!!! :imunfunny:

And then this week hits, and we get an email that the senior lead of that team has left out of the blue, and my entire team is sick of waiting. No one wants to use SAP anymore, not even me. It's just.... it's loving terrible. I'm at the point where I'm doing things on pen and paper and just fudging it into the system to make it look good, and I've run out of hands to juggle all the plates. I've got access to SAP and Excel, and I'm exercising my options to try and make my job work instead of be work.

I could talk on this more, but it's off topic, so unless someone asks me to, I'll just finish that rant off with the fact that it's not just inventory. Work orders are also a gently caress. The same team pushed universal work orders to all of the facilities, but didn't take into account that different facilities have different equipment. So we've got PMs that are for stuff we don't even have, that we're expected to keep logs showing we did that work. And for the stuff we do have, there's steps that aren't listed in the actual manufacturer-supplied manuals, or there's steps for options we do/n't have. And we can't make changes to the work orders, because they just get overwritten. But us mechanics need to find a way to make that work? gently caress that.

Ok, rant over. gently caress.

neogeo0823 fucked around with this message at 02:11 on Jun 14, 2023

# ? Jun 14, 2023 02:07

HootTheOwl: May 13, 2012; Hootin and shootin

This is a job for Microsoft Fox pro, and it's so old it's it off support and you can get it for free

# ? Jun 14, 2023 03:20

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

Found this Python code which looks like it'll do the trick: https://www.geeksforgeeks.org/find-paths-given-source-destination/

I tested at home with my sample cases in Python, and it seems to be doing the business, eg. for sample case A to M (or 1 to 13 as I had to sub in numbers for letters that the code needed), and having added a new dummy path from 0 to A at the top so that there's only 1 entry point to the graph, the rest is as before.

Following are all different paths from 0 to 13 :
[0, 1, 2, 4, 13]
[0, 1, 2, 5, 4, 13]
[0, 1, 2, 6, 13]
[0, 1, 2, 7, 4, 13]
[0, 1, 3, 4, 13]
[0, 1, 3, 5, 4, 13]

which translates to
Following are all different paths from 0 to M :
[0, A, B, D, M]
[0, A, B, E, D, M]
[0, A, B, F, M]
[0, A, B, G, D, M]
[0, A, C, D, M]
[0, A, C, E, D, M]

I modified the code at the bottom to loop through all paths from 0 to 21:
for d in range (21):
s = 0
print ("Following are all different paths from % d to % d :" %(s, d))
g.printAllPaths(s, d)

There's a c# version on the page I'm going to try at work, as I just recently found out you can call c# code from PowerShell.

# ? Jun 15, 2023 14:41

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

DRINK ME posted:

I�m not sure what the best way but the simplest way you could do it is on your new sheet with
=FILTER(sheet1!A:G, Sheet1!H:H < Sheet1!F:F)
That gets you the list of lines you want, then in column I you could do
=G1-H1

If you want to go more complicated I think you could do it with a pivot table and a couple of calculated fields, one for a true/false filter to show the right rows and one for your reorder amount. You�d just have to refresh it when you paste in new data. Then you could do it in code too, it�d be a loop from row 1 to last row, IF H < F, copy the row to the new sheet, add formula for reorder amount.

I'm re-quoting this for the trip report. I ended up with three different chunks of formulas to do the different things I wanted. I started with the freshly exported inventory on a sheet titled "Inventory", and a second sheet, titled "To Order".

First, is the code in question, which came out as:

code:

=FILTER(Inventory!A2:L2000,(Inventory!H2:H2000<=Inventory!F2:F2000)*(Inventory!E2:E2000<>"")*(Inventory!H2:H2000<>Inventory!G2:G2000),)

What that does is, it searches the whole inventory sheet and finds all the rows where the actual stock level(H) is at or below the reorder point(F), and also where that row has a storage location listing(E), and also where the actual stock level is not also equal to the maximum stock level(G). The first because duh, the second because we're never allowed to delete entries with SAP, so if we need to retire a part listing for whatever reason, we just remove its storage location listing and set the min/max levels to 0. Finally, there's a lot of parts we order where the min/max is 1/1, and our stock levels are also 1, and so that last part eliminates anything that matches that criteria.

Next, I needed a column to tell me how many of each thing to order, so I took the output of that formula, and in a new column I have this formula:

code:

=IF(H2-I2=0,"",H2-I2)

Which just subtracts total stock(I) from the maximum stock level(H) and outputs that into column A on the same row as the information. The IF portion of that code shouldn't ever trigger, but hey, better to prepare for weird edge cases.

Finally, I've got pricing for the parts, which is handled in the N column as:

code:

=IFERROR(IF(A2*M2=0,"",A2*M2),"")

That just takes the quantity to order(A), and multiplies it by the rolling price(M).

Now, the real fun was today, when I was figuring out how to do a somewhat dynamic form on the first sheet, to allow the other mechanics to easily log parts that they use within this thing. I didn't copy down the code for that, as I finished it like 10 minutes before the end of the day, but I can transcribe that tomorrow after I get home. I still need to do a bit of cleanup with it anyway, but the quick version is that I've got a sheet that's protected except for the specific cells that I want people to fill in. They fill them in with like, their name, a task description, a list of SAP part numbers, quantities, etc etc etc, and hit a Submit button, and that copies the info to a separate sheet and clears the form. Later on, I or another qualified person can take the info off the second sheet and use it to create the actual work orders that need to be made. I'm even proud of myself for figuring out how to seemingly-elegantly append the new data to the first blank row of the second sheet.

And once that's all taken care of, for an encore I'm going to merge some other tools I've made over the last couple of months into this thing, to make one home location for all of this poo poo.

# ? Jun 16, 2023 00:24

Chris Knight: Jun 5, 2002; me @ ur posts; Fun Shoe

Chris Knight posted:

There's a c# version on the page I'm going to try at work, as I just recently found out you can call c# code from PowerShell.

Couldn't understand how that worked, so tried the Python version at home again.

Good news is that it runs; bad news is that with the number of nodes in my list, it'll take multiple times the age of the universe to finish running :v:

# ? Jun 19, 2023 13:49

HootTheOwl: May 13, 2012; Hootin and shootin

Chris Knight posted:

Couldn't understand how that worked, so tried the Python version at home again.

Good news is that it runs; bad news is that with the number of nodes in my list, it'll take multiple times the age of the universe to finish running

How many nodes are on your list?
And what is this even trying to accomplish. I get you make this giant rear end list, but then what?

# ? Jun 19, 2023 14:50

Count Roland: Oct 6, 2013

My question is for Google Sheets, but I'll ask it here. Apologies if this isn't the place for it.

I'm using Sheets as a way to track lessons that I teach. Each lesson has its own tab. Each tab includes dumb text, and a few cells with identifiers like Title, Date, etc.

To keep track of all of them I have another tab called Index. It is a list of all the other tabs. Each is listed by number, and includes the identifiers in seperate columns. So it will look like this:

Index
# - Date - Title
1 - Jan 1 - Apples
2 - Jan 2 - Oranges
3 - Jan 3 - Pears

These values are grabbed from the individual tabs. Below are the formulae that give the above values. As you see, only the tab number changes.

Index
# - Date - Title
1 - ='1'!B32 - ='1'!C32
2 - ='2'!B32 - ='2'!C32
3 - ='3'!B32 - ='3'!C32

My problem is that I have a lot of lessons. I write the lessons one at a time, and Index them later. I'd like a way to fill out this Index more automatically. Currently, when I add a new lesson I'd copy-paste ='3'!B32 and manually change it to ='4'!B32 to get data from the 4th tab. I've tried to use a formula to iterate this other formula without success. I'm aware this isn't really what Sheets was designed for. I'd still though like a way to do this more easily.

Any thoughts?

# ? Jun 22, 2023 01:19

esquilax: Jan 3, 2003

You can use the =INDIRECT() function, and all you need to do is put your tab names in the first column, either via formula or any other method. The =INDIRECT() formulas should also all copy down fine.

code:

1	=INDIRECT(A1&"!B32")	=INDIRECT(A1&"!C32")
2	=INDIRECT(A2&"!B32")	=INDIRECT(A2&"!C32")
3	=INDIRECT(A3&"!B32")	=INDIRECT(A3&"!C32")

# ? Jun 22, 2023 01:35

Count Roland: Oct 6, 2013

Hot drat. I even had found the Indirect function but was too dumb to make it work. Thanks a bunch.

# ? Jun 22, 2023 01:47

timp: Sep 19, 2007; Everything is in my control; Lipstick Apathy

Protip: Ask ChatGPT your Excel questions. Word them exactly like you would a post in this thread and they�ll give you the exact functions you need in like 2 seconds.

Not that there couldn�t potentially be a question that ChatGPT couldn�t handle, but if you�re like me and a lot of your questions are relatively basic in nature, ChatGPT makes for an excellent Smart Excel Friend

# ? Jun 22, 2023 02:07

Sad Panda: Sep 22, 2004; I'm a Sad Panda.

I've got an Excel file that's saved as a macro enabled worksheet. When I look in the developer tab and press macros it is empty. If I try to save it as a regular workbook it says that VB projects can't be saved in macro-free workbooks. Where would I find thaf as I'm not convinced I've got one?

# ? Jul 13, 2023 15:55

Harminoff: Oct 24, 2005; ð½

Sounds like it wasn't saved as an xlsm but just xls.

# ? Jul 13, 2023 16:23

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

Maybe it�s worksheet/workbook functions, like on_open, on_change macros. I don�t think those show up in the macro list (not in front of Excel). Hit the Visual Basic button, expand the browser on the left of it (where it says Project in the image), and then the items within - you should see a list of sheets, the workbook, and if they exist, forms and modules.

If there are forms or modules, take a look in there for code, otherwise double click on one of the sheets and Control+F (for Find) and search for the word �sub� - any of those worksheet macros will include that word. If you don�t find anything it�s probably safe to ignore that warning.

# ? Jul 13, 2023 20:32

Banjo Bones: Mar 28, 2003

I've got some Multiple Choice questions in a single sheet like this:

Each question has a header of "Easy", "Medium", or "Hard".

How do I sort these so the different question difficulties are grouped together?

# ? Jul 22, 2023 08:47

nielsm: Jun 1, 2009

Does every question have exactly four answers with exactly one of them correct?

# ? Jul 22, 2023 10:17

Banjo Bones: Mar 28, 2003

nielsm posted:

Does every question have exactly four answers with exactly one of them correct?

There are some True/False questions with only 2. But yes, the rest are 4 with one correct answer.

# ? Jul 22, 2023 12:55

HootTheOwl: May 13, 2012; Hootin and shootin

The quick and dirty way is to add two columns to the right, one for difficulty and one for question number then you can sort on the two columns. It would be alphabetical so it would go Easy -> Hard -> medium
So youd represent difficulty in the new column with a number
E: then hide the columns

# ? Jul 22, 2023 13:01

Banjo Bones: Mar 28, 2003

What do I populate the colums with? I dont get how to tell excel to sort 6 rows at a time to keep the questions in their original format.

# ? Jul 23, 2023 08:13

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

Put the difficulty in one column, and if you want to keep the list the same, title the question row as 1 and each answer as 1.1, 1.2, and so on. Then you can sort ascending and by difficulty

# ? Jul 23, 2023 15:43

Adbot: ADBOT LOVES YOU

# ? May 13, 2024 05:19

HootTheOwl: May 13, 2012; Hootin and shootin

Banjo Bones posted:

What do I populate the colums with? I dont get how to tell excel to sort 6 rows at a time to keep the questions in their original format.

One column for the answer's difficulty and one column for the answer's question number.
So it appears as:

code:

A          B                   C D
Easy.                          1 1
Question 1 This is a question? 1 1
*          True.               1 1
           False.              1 1
Hard.                          1 1
Question 2 This is false? 2 3
*          True.               2 3
           False.              2 3
Easy.                          2 3
Question 3 I'm cool?           3 1
           True.               3 1
*          False.              3 1

Then you can sort by the difficulty column, which will group everything together, then by question so the questions stay together

E: now that I'm at a computer I can elaborate
So:
Column A is the difficulty, question number, and correct answer mark
Column B is the question and the answers
Column C is new, and it's the question number (as a number for easy sorting)
Column D is new, it's the difficulty expressed as a number so that you can order it better, since words are sorted alphabetically but if you say Easy =1 Medium =2, etc you can group them easier

HootTheOwl fucked around with this message at 15:43 on Jul 24, 2023

# ? Jul 23, 2023 16:19

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

«‹›82 »