The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Another Excel question
V1m
Registered User regular
Registered User regular
NB: I realise that this problem is definitely better treated with Access or some other database program, but because Reasons™, that's simply not going to happen
I am working with an Excel table. The table is structured thus:
{Column P} {Column S} {Column T}
Process1 [Count] -> Sub-Process1a [Count] -> Type 1 [count]
Process1 [Count] -> Sub-Process1a [Count] -> Type 2 [count]
Process1 [Count] -> Sub-Process1b [Count] -> Type 1 [count]
Process1 [Count] -> Sub-Process1b [Count] -> Type 2 [count]
Process2 [Count] -> Sub-Process2c [Count] -> Type 1 [count]
Process2 [Count] -> Sub-Process2c [Count] -> Type 2 [count]
Process2 [Count] -> Sub-Process2d [Count] -> Type 1 [count]
Process2 [Count] -> Sub-Process2d [Count] -> Type 2 [count]
... etc.
There are about 20 or 25 processes, I can't remember exactly, and each process has between 1 and 18 subprocess, mostly unique to that process (Except there are about 5 or 6 instances where the process has no subprocess, so the subprocess name is <blank>), each of which is split into one of two types. (Except 1 special snowflake asshole subprocess that has it's own special snowflake subtype. Luckily that subtype is the only subprocess for that particular process, so I'm going to deal with case that by brute-forcing it.) It's a big table, because it's collating date for a complex environment.
What I want is to be able to make a dynamic list of the sub-processes for a selected Process. Obviously, I can just use Data Validation(list) to pick a Process, that's trivial enough even for me. What I need is something that looks like a cell where one selects the process of one's choice, and then below it, a dynamic list of the sub-processes relevent to that process appear. Ideally if possible, only the sub-processes with a count above zero will appear. Not a dealbreaker if I can't have this, but it would be super nice.
[Select Process from List]
First Unique Sub-Process1 [Count]
Next Unique Sub-Process2 [Count]
...
Last Unique Sub-ProcessN [Count]
I got as far as being able to find the first subprocess with VLOOKUP, then realised I have NFI how to pull the 2nd-nth sub-process. After googling, I started to use INDEX-MATCH but tbh I don't really understand that function too well, and I don't know how to make it not pull up irrelevent subprocess. (ie: I made an 18-row list table, made INDEX-MATCH lines for rows 1-18, but then it pulls through irrelevent Sub-Process lines that don't belong to the Process.)
I am working with an Excel table. The table is structured thus:
{Column P} {Column S} {Column T}
Process1 [Count] -> Sub-Process1a [Count] -> Type 1 [count]
Process1 [Count] -> Sub-Process1a [Count] -> Type 2 [count]
Process1 [Count] -> Sub-Process1b [Count] -> Type 1 [count]
Process1 [Count] -> Sub-Process1b [Count] -> Type 2 [count]
Process2 [Count] -> Sub-Process2c [Count] -> Type 1 [count]
Process2 [Count] -> Sub-Process2c [Count] -> Type 2 [count]
Process2 [Count] -> Sub-Process2d [Count] -> Type 1 [count]
Process2 [Count] -> Sub-Process2d [Count] -> Type 2 [count]
... etc.
There are about 20 or 25 processes, I can't remember exactly, and each process has between 1 and 18 subprocess, mostly unique to that process (Except there are about 5 or 6 instances where the process has no subprocess, so the subprocess name is <blank>), each of which is split into one of two types. (Except 1 special snowflake asshole subprocess that has it's own special snowflake subtype. Luckily that subtype is the only subprocess for that particular process, so I'm going to deal with case that by brute-forcing it.) It's a big table, because it's collating date for a complex environment.
What I want is to be able to make a dynamic list of the sub-processes for a selected Process. Obviously, I can just use Data Validation(list) to pick a Process, that's trivial enough even for me. What I need is something that looks like a cell where one selects the process of one's choice, and then below it, a dynamic list of the sub-processes relevent to that process appear. Ideally if possible, only the sub-processes with a count above zero will appear. Not a dealbreaker if I can't have this, but it would be super nice.
[Select Process from List]
First Unique Sub-Process1 [Count]
Next Unique Sub-Process2 [Count]
...
Last Unique Sub-ProcessN [Count]
I got as far as being able to find the first subprocess with VLOOKUP, then realised I have NFI how to pull the 2nd-nth sub-process. After googling, I started to use INDEX-MATCH but tbh I don't really understand that function too well, and I don't know how to make it not pull up irrelevent subprocess. (ie: I made an 18-row list table, made INDEX-MATCH lines for rows 1-18, but then it pulls through irrelevent Sub-Process lines that don't belong to the Process.)
V1m on
0
Posts
And as for suppressing duplicates, and thinking about it probably the count-0 thing too, look up the countif function.
Actually, ignore all of that. This is actually one of the prime cases where you'd want to just use a pivot table.
Pick the process you're interested in from the report filter drop down, and the table will include only the sub processes from that process.
This may be unsuitable depending on what you're trying to do with the list.
I don't know what a pivot table is, but it seems like it's the answer in the vast majority of Excel threads I don't have an Excel solution for. I should really give that a poke some day.
(It seems that when the answer should have been "pivot table," I default to "external script!" and have thereby never learned the why, when, or how of pivot tables.)
Now I'm on to a new, related crisis. How do I make charts completely ignore 0-values?
Like if I have a table like this:
TREATS: | Yesterday | Today | Total
Cup cakes | 76 | 14 | 90
Cookies | 34 | 0 | 34
Donuts | 0 | 17 | 17
Cannollis | 6 | 32 | 38
Macarons | 0 | 0 | 0
Ginger snaps | 18 | 11 | 29
Lemon bars | 11 | 103 | 124
Fruit cake | 0 | 6 | 6
Jam tarts | 64 | 73 | 137
Flapjacks | 0 | 54 | 54
I would like to use this to feed a stacked bar chart (Yesterday + Today) that will only show treats from that list with a >0 value. The list is dynamic, so the specific rows that have a 0 value will constantly change, and each treat has 3 associated values (of which 2 are used for the stacked bars, the 3rd is used for something else) so a simple sort high> low isn't going to do it.
Failing that, I would accept a chart for "Yesterday" values and a seperate chart for "Today" values (I can always use "Total" to provide the context), as long as the 0-value items are not visible (in the real charts, there are almost always only 5-6 out of 21 values which aren't 0 for Today or Yesterday - and *mostly* they're the same but there's way to many exceptions to assume that's always going to be the case - so including them all makes the charts an unreadable mess)
Edit: if it will make it easier, I can just make the entries, including the treat type, blank instead of 0, but that leaves a gap and that seems to throw the chart wizard into a tantrum
I am n00bzor with excel charts and I fear and hate them. Normally there's a dude who knows chart-sorcery I can ask, but by a massive failure of incompetent and unaccountable management he's been allowed on holiday for 2 weeks.
1. Make a column that doesn't increment if the row is only 0 entries IF(AND(Yesterday=0,Today=0),CellAboveThisOne,CellAboveThisOne+1)
2. Use this column along with index match to take only the rows with non-0's to a new table
3. Point your chart at this new table.
Example:
I considered making a mask to feed the chart, I was hoping that there was a more direct route because there's about 20 of these damb tables. Still what the heck, it's on their clock. Thanks mate I'll give that a try. I was trying to work it out today on 0hrs sleep and getting nowhere.
Dude can literally do this wth and index function