Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Post
  • Reply
BBQ Dave
Jun 17, 2012

Well, that's easy for you to say. You have a bad imagination. It's stupid. I live in a fantasy world.



Hey all if anyone can help I'd really appreciate it. Pictured is a spreadsheet with no formulae. I spent some time looking on forums but couldn't make it work.

I want to automatically populate column E with the names in column B that I have switched on, and they need to be in numerical order. B is going to be much longer, like 600 entries.

Pretty much what I want to be able to do is activate and deactivate names in with column A to end up with a list of the names that are on, in order, in column E.

Adbot
ADBOT LOVES YOU

shame on an IGA
Apr 8, 2005

ugly hack, use column C to copy the values that are marked in A from B to C in each row and then do this to compile them all neatly in another column

https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range

Canine Blues Arooo
Jan 7, 2008

when you think about it...i'm the first girl you ever spent the night with

Grimey Drawer
Before working on Excel, you might want to first become familiar with that Print Screen button on your computer!

BBQ Dave
Jun 17, 2012

Well, that's easy for you to say. You have a bad imagination. It's stupid. I live in a fantasy world.

shame on an IGA posted:

ugly hack, use column C to copy the values that are marked in A from B to C in each row and then do this to compile them all neatly in another column

https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range

Thanks!

Canine Blues Arooo posted:

Before working on Excel, you might want to first become familiar with that Print Screen button on your computer!

Is that an app? Is it free?

Latin Hobbit
Jan 29, 2009
Assuming you have a reasonably up-to-date version of Excel you can do this easily with the FILTER function.

Put the following formula into cell E2 and it should spit out the list you want (and update in real time):

=FILTER(B:B,A:A="X")

This formula is filtering the contents of column B (B:B) based off rows where column A (A:A) equals the string "X", if you need to change the columns you are looking at or the value you are using to mark whether the row is switched on or off hopefully it is clear how. If this list gets very big to avoid human error causing you massive headaches I would also recommend putting Data Validation restrictions on column A to prevent non-intended values from being entered (have a look at https://support.microsoft.com/en-us...&rs=en-us&ad=us for a basic walkthrough).

LIGHTNING EDIT: Forgot to mention the sorting numerically component, you can do this by wrapping a SORT around the above function, EG:

=SORT(FILTER(B:B,A:A="X"))

Latin Hobbit fucked around with this message at 08:08 on Jan 24, 2021

SidneyIsTheKiller
Jul 16, 2019

I did fall asleep reading a particularly erotic chapter
in my grandmother's journal.

She wrote very detailed descriptions of her experiences...

Latin Hobbit posted:

Assuming you have a reasonably up-to-date version of Excel you can do this easily with the FILTER function.

It's only available in Office 365

BBQ Dave
Jun 17, 2012

Well, that's easy for you to say. You have a bad imagination. It's stupid. I live in a fantasy world.

SidneyIsTheKiller posted:

It's only available in Office 365


Latin Hobbit posted:

Assuming you have a reasonably up-to-date version of Excel you can do this easily with the FILTER function.

Put the following formula into cell E2 and it should spit out the list you want (and update in real time):

=FILTER(B:B,A:A="X")

This formula is filtering the contents of column B (B:B) based off rows where column A (A:A) equals the string "X", if you need to change the columns you are looking at or the value you are using to mark whether the row is switched on or off hopefully it is clear how. If this list gets very big to avoid human error causing you massive headaches I would also recommend putting Data Validation restrictions on column A to prevent non-intended values from being entered (have a look at https://support.microsoft.com/en-us...&rs=en-us&ad=us for a basic walkthrough).

LIGHTNING EDIT: Forgot to mention the sorting numerically component, you can do this by wrapping a SORT around the above function, EG:

=SORT(FILTER(B:B,A:A="X"))

THanks but it's gotta work on office 2013. It's for my coworkers to use... Still trying thanks for all the help.

Owlspiracy
Nov 4, 2020


Does office 2013 have the query function?

BBQ Dave
Jun 17, 2012

Well, that's easy for you to say. You have a bad imagination. It's stupid. I live in a fantasy world.

Aruan posted:

Does office 2013 have the query function?

Nope.

Ham Equity
Apr 16, 2013

i hosted a great goon meet and all i got was this lousy avatar
Grimey Drawer

BBQ Dave posted:

Is that an app? Is it free?
Big Boomer energy in this thread.

Probably easier for you to go to the start menu, and type in "Snipping Tool." Best thing to come out of Windows Vista.

BBQ Dave
Jun 17, 2012

Well, that's easy for you to say. You have a bad imagination. It's stupid. I live in a fantasy world.

Thanatosian posted:

Big Boomer energy in this thread.

Probably easier for you to go to the start menu, and type in "Snipping Tool." Best thing to come out of Windows Vista.

Sorry I was joking back. Love me the snipping tool.

Btw I figured it out. Thanks everyone

ninjewtsu
Oct 9, 2012

I wanna know what the solution was

BBQ Dave
Jun 17, 2012

Well, that's easy for you to say. You have a bad imagination. It's stupid. I live in a fantasy world.

Ive made a button that filtered out the zeros. Had to do it at the end, instead of populating a list in real time but it works. Not what I wanted but shame on an IGA put me on the right path. Everyone is very happy with the result even though it's janky and old. Hand typing 500 addresses a day gets old.

Cheers everyone!

SidneyIsTheKiller
Jul 16, 2019

I did fall asleep reading a particularly erotic chapter
in my grandmother's journal.

She wrote very detailed descriptions of her experiences...

ninjewtsu posted:

I wanna know what the solution was

The real solution is "get a database"

Aruan posted:

Does office 2013 have the query function?

It should still have MS QUERY which was its precursor, but that's probably overkill lol

SidneyIsTheKiller fucked around with this message at 06:56 on Jan 28, 2021

Slow Motion
Jul 19, 2004

My favorite things in life are sex, drugs, feeling like a baller, and being $30,000 in debt.
You make a couple columns with formulas that build up your ordered list into a single cell with pipe delimiters (reminder: comparative operators work on strings). Then you make a couple columns that deconstruct the ordered list into cells. It's more advanced than typical excel monkey work but anyone with "analyst" in their title should have no problem with it.

Adbot
ADBOT LOVES YOU

Slow Motion
Jul 19, 2004

My favorite things in life are sex, drugs, feeling like a baller, and being $30,000 in debt.
Here's what I came up with just doing the thing. It's been a while so I'm sure it could be more elegant.

Only registered members can see post attachments!

  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply